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?

Tags:

Best Answer

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    edited 04/26/23 Answer ✓

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


Answers

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • PeggyLang
    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å
    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?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Danielle Arteaga
    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
    Danielle Arteaga ✭✭✭✭✭✭
    edited 04/26/23 Answer ✓

    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
    PeggyLang ✭✭✭✭✭

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

  • PeggyLang
    PeggyLang ✭✭✭✭✭

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!