Index Match, Inconsistent 0 before number 3 digit number.

Options

I have two different 3rd party associates that use Smartsheet's. We have a number to represent stores. One party uses a 4 digit number so when the number is 3 digits, they add a 0 before it. The other does not and only uses the 3 digits. I have formulas such as Index match to pull data from their sheets but have now run into the problem of how pulling the data by the store number from both.

Is there an addition to my formula I can use that will ignore the 0 and still pull the data from a 0123 and a 123 the same?

Thank you for any help!

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    There are probably a few ways to do this. Without seeing your formulas/sheets it's hard to tell which way would be best.

    I'm thinking you need a nested IF statement in there somewhere. The logic would be: If the left most digit in the store number is a zero ( LEFT({Store Number}, 1) = 0 ), then only read the three righthand numbers in the store number ( RIGHT({Store Number}, 3) ), otherwise, use the whole store number.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Happy I could help!

    I thought of one other thing that would work, and it's a more simple solution: The VALUE function.

    Wrap the VALUE function around your INDEX/MATCH, and if a result comes back that starts with a leading 0 such as 0123, the VALUE function changes it to its true numeric value of 123. Alternatively, reference the cell containing your INDEX/MATCH results, and find its value:

    Using the VALUE function also eliminates issues with some cells possibly being text while others are numeric.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!