Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Using =Value to return a number but gettting error

    ✭✭✭
    in Formulas and Functions

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

    image.png

    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.

    image.png

    So I thought that using =Value would fix that.

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

    image.png

    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:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭
      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), " ", "")))

      image.png
      · Share on FacebookShare on Twitter

    Answers

    • Community Champion

      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))

      Thanks,
      Sam
      —
      Want to chat about a Smartsheet problem you're facing?
      Grab time on my calendar here:
      https://calendly.com/sam-samharwart/30min

      · Share on FacebookShare on Twitter
    • ✭✭✭

      @Sam_Harwart ,

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

      · Share on FacebookShare on Twitter
    • Community Champion

      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

      · Share on FacebookShare on Twitter
    • Community Champion

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

      Thanks,
      Sam
      —
      Want to chat about a Smartsheet problem you're facing?
      Grab time on my calendar here:
      https://calendly.com/sam-samharwart/30min

      · Share on FacebookShare on Twitter
    • ✭✭✭
      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), " ", "")))

      image.png
      · Share on FacebookShare on Twitter
    • Community Champion
      Matthew 5

      Looks great!

      Thanks,
      Sam
      —
      Want to chat about a Smartsheet problem you're facing?
      Grab time on my calendar here:
      https://calendly.com/sam-samharwart/30min

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions