Using =Value to return a number but gettting error

I have the following cells that I need to use in an Index(match

I cannot control the format, and ideally would like to resist using a helper column. The numbers vary in length and the blank spaces, "-" and leading zeros become an issue with Index(match.

The formula I am working with to adjust and want to use for the Match portion of the Index(match

=SUBSTITUTE(MID(Location@row, FIND("fz", Location@row) + 3, 10), "-", "")

Will give me these results which are not numbers but text, which is what I expected as this function does not return the results as a number.

So I thought that using =Value would fix that.

=Value(SUBSTITUTE(MID(Location@row, FIND("fz", Location@row) + 3, 10), "-", ""))

Which gets me these results where all but one cell fails with the #INVALID VALUE error.

So, am I just using VALUE incorrectly here or am I missing something else.

Tags:

Best Answer

  • Matthew 5
    Matthew 5 ✭✭
    Answer ✓

    @Sam_Harwart ,

    You got me on the right track thinking of this as a negative number…I had in my head that the "-" was reading as text (which it probably is) but it also sees it as the negative number if the number is there too, I was just not seeing it in my head.

    So, I shortened it up a bit, using the ABS function which will give me not only the number, but eliminate the leading zeros. So far I have not found a value in the data that breaks this. I can now use this in my Index(Match and it works out just right.

    =ABS(VALUE(SUBSTITUTE(MID(Location@row, FIND("fz", Location@row) + 3, 10), " ", "")))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!