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 ofStore ID. For example, create a column calledStoreIDAppended. In that, place this column-level formula: "A"+[Store ID]@row. Now, yourStoreIDAppendedvalues should look like "A0001, A1002, A0101," etc. When you sort using theStoreIDAppendedcolumn, 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 bymarking 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 theStore IDcolumn, I create a series of IDs with zeros in leading and non-leading positions.

In thePosition of First Zerocolumn, I placed this column-level formula: =FIND(0, [Store ID]@row). This identifies the position of the first character inStore IDif that character is a zero.

In theRemove First Zerocolumn is this column-level formula: =IF([Position of First Zero]@row = 1, RIGHT([Store ID]@row, 3), [Store ID]@row). IfStore IDhas 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 inStore 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 theRemove Third Zerocolumn into a newStore IDcolumn (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 ofStore ID. For example, create a column calledStoreIDAppended. In that, place this column-level formula: "A"+[Store ID]@row. Now, yourStoreIDAppendedvalues should look like "A0001, A1002, A0101," etc. When you sort using theStoreIDAppendedcolumn, the leading zeros will sort properly. (See below.)

PeggyLang

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

PeggyLang

@Danielle ArteagaWELL 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.

=如果(左(Number@row, 2) =“00”,右(Number@row,LEN(Number@row) - 2), IF(LEFT(Number@row, 1) = "0", RIGHT(Number@row, LEN(Number@row) - 1), Number@row))