Using =Value to return a number but gettting error

Matthew 5

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.

Matthew 5

@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), " ", "")))

Sam_Harwart

Hi Matthew,

Will the text always have one of those formats? If yes, you could use RIGHT() to grab the last 4 characters in an easier way:
=VALUE(RIGHT(Location@row, 4))

Matthew 5

@Sam_Harwart ,

Unfortunately not, they can be anywhere from 4 to 10 digits long.

DKazatsky2

Hi @Matthew 5,

If you create another column and use the VALUE function on the column with the original SUBSTITUE function, you will get a numeric value.

=VALUE([column with the original formula result]@row)

See if you can then use that value in your match.

Hope this helps,

Dave

Sam_Harwart

If we have to handle more digits in addition to the varying data string input structures, we can substitute out the spaces and then treat the dash as a negative or positive value.

=IF(VALUE(SUBSTITUTE(MID(Location@row, FIND("FZ", Location@row) + 2, 20), " ", "")) > 0, VALUE(SUBSTITUTE(MID(Location@row, FIND("FZ", Location@row) + 2, 20), " ", "")), -1 * VALUE(SUBSTITUTE(MID(Location@row, FIND("FZ", Location@row) + 2, 20), " ", "")))

Matthew 5

@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), " ", "")))

Sam_Harwart

https://community.smartsheet.com/discussion/comment/469203#Comment_469203

Looks great!