Index Match(Or Collect) with IF, LEFT, and LEN

Joe Goetschel

I need help combining 2 formulas that currently work on their own but I need them to work together.

SHEET 1 - IMPORT SHEET (Data comes in as "100h")

I can do the following to get the results I want.

=if([Column1@row]="",0, LEFT([Column1@row], LEN([Column1@row] - 1))

I get a 0 if Blank or the "100" without the "h"

SHEET 2 (Bring hours over to another sheet)

=INDEX({Sheet 1 - Hours}, MATCH([Project ID]@row, {Sheet 1 - ID},0))

I can get the Data in as 100h no problem. But I want it to come over as 100.

I do not wish to have a helper column ( long story) also ALOT OF Helper Columns would be needed. (52+)

Any help would be great.

Paul Newcome

The easiest way to do this is to copy your INDEX/MATCH without the = in the beginning, then highlight [Column1]@row and paste.

=if(paste_here = "", 0, LEFT(paste_here, LEN(paste_here) - 1))

If it is ALWYS going to be the letter "h", you can also use a SUBSTITUTE function instead of the LEFT/LEN combo.

Joe Goetschel

In this case, it will always be "h" can you show me what that would look like?

Paul Newcome

=if(paste_here = "", 0, SUBSTITUTE(paste_here, "h", ""))

It isn't really that much different/shorter, but it is one less function for the sheet to have to process on the back-end.