Need to remove leading zeros
I have a column that represents 'Store #'. Store #'s are 4 digits, i.e., 0001 or 0010 or 0100 or 1000
This becomes a bit of an issue when sorting the column.
I am creating a helper row for purposes of sorting and want to remove the LEADING zeros, however, am having trouble figuring out how to accomplish this.
Thoughts?
Best Answer

FWIW, one less messy solution (especially if you really need to preserve the full fourdigit Store IDs) might be to append an alphabetic character to the front of Store ID. For example, create a column called StoreIDAppended. In that, place this columnlevel formula: "A"+[Store ID]@row. Now, your StoreIDAppended values should look like "A0001, A1002, A0101," etc. When you sort using the StoreIDAppended column, the leading zeros will sort properly. (See below.)
Answers

Hi @PeggyLang
I hope you're well and safe!
Try something like this.
=VALUE(SUBSTITUTE(Number@row, 0, ""))
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

@Andrée Starå This works beautifully until text in the cell is '0010' or '0100'. In these instances I need to return '10' and '100'.

Ah, yes, I forgot about that. I have a meeting now, but I'll return to the post afterward.
How many leading zeros can there be as a max?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

There is probably a much more elegant solution here, but I came up with a way to do this that should preserve any nonleading zeros.
Here's the sample worksheet I created to test this:
In the Store ID column, I create a series of IDs with zeros in leading and nonleading positions.
In the Position of First Zero column, I placed this columnlevel formula: =FIND(0, [Store ID]@row). This identifies the position of the first character in Store ID if that character is a zero.
In the Remove First Zero column is this columnlevel formula: =IF([Position of First Zero]@row = 1, RIGHT([Store ID]@row, 3), [Store ID]@row). If Store ID has no zeros or a zero in a position other than 1, it returns all 4 digits. If the first character is zero, it returns only the right 3 digits.
Now, the later columns repeat the process until zero is not the first digit in Store ID. Formulas are as follows:
Position of Second Zero: =FIND(0, [Remove First Zero]@row)
Remove Second Zero: =IF([Position of Second Zero]@row = 1, RIGHT([Remove First Zero]@row, 2), [Remove First Zero]@row)
Position of Third Zero: =FIND(0, [Remove Second Zero]@row)
Remove Third Zero: =IF([Position of Third Zero]@row = 1, RIGHT([Remove Second Zero]@row, 1), [Remove Second Zero]@row)
You can "copy" the results of the Remove Third Zero column into a new Store ID column (named whatever suits you), then hide these "helper" columns on your sheet.

FWIW, one less messy solution (especially if you really need to preserve the full fourdigit Store IDs) might be to append an alphabetic character to the front of Store ID. For example, create a column called StoreIDAppended. In that, place this columnlevel formula: "A"+[Store ID]@row. Now, your StoreIDAppended values should look like "A0001, A1002, A0101," etc. When you sort using the StoreIDAppended column, the leading zeros will sort properly. (See below.)

@Andrée Starå Three leading zeros is the max

@Danielle Arteaga WELL that was an out of the box solution that actually works!!! Simple, easy, unconventional, but works. Thanks!!!

Late to the party, but this works for me where are either 1, 2 or no trailing zeros.
=IF(LEFT(Number@row, 2) = "00", RIGHT(Number@row, LEN(Number@row)  2), IF(LEFT(Number@row, 1) = "0", RIGHT(Number@row, LEN(Number@row)  1), Number@row))
Help Article Resources
Categories
Check out the Formula Handbook template!