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

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.

Joe Goetschel | Smartsheet Director

Save My Contact



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!