Index Match, Inconsistent 0 before number 3 digit number.
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
-
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!
-
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
-
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!
-
Thank you! I will try that.
-
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!
-
I love this! Thanks so much! I can use this in some other issues I was having as well.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!