Need to remove leading zeros

PeggyLang

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?

Danielle Arteaga

FWIW, one less messy solution (especially if you really need to preserve the full four-digit 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 column-level 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å

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!

PeggyLang

@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'.

Andrée Starå

@PeggyLang

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?

Danielle Arteaga

There is probably a much more elegant solution here, but I came up with a way to do this that should preserve any non-leading 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 non-leading positions.

In the Position of First Zero column, I placed this column-level 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 column-level 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.

Danielle Arteaga

FWIW, one less messy solution (especially if you really need to preserve the full four-digit 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 column-level 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.)

PeggyLang

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

PeggyLang

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

Texmati

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