Removing Leading Zeroes Formula

Sara H

I can't seem to find this anywhere, but how would you create a formula to remove leading zeroes in a helper column?

Frank B.

=IF(LEFT([Original Column]@row, 1) = "0", RIGHT([Original Column]@row, LEN([Original Column]@row) - 1), [Original Column]@row)

In this formula, replace "Original Column" with the name of the column that contains the data with the leading zeros.

The formula checks if the first character in the original column is a zero. If it is, the formula uses the RIGHT function to return all characters in the original column except for the first one. If the first character is not a zero, the formula returns the original value from the original column.

HTH!

Sara H

@Frank B. Hi Frank - Thank you so much for helping! The only challenge that I ran into is that it doesn't remove the leading zeroes from numbers with MORE THAN ONE leading zero. Do you know a way I might modify this for a varied number of leading zeros? The longest number (with leading zeros) would be up to nine digits.

Frank B.

Hi @Sarah H here you go:

=IF(LEFT([Original Column]@row, 1) = "0", 

  IF(LEFT([Original Column]@row, 2) = "00",

    IF(LEFT([Original Column]@row, 3) = "000",

      IF(LEFT([Original Column]@row, 4) = "0000",

        IF(LEFT([Original Column]@row, 5) = "00000",

          IF(LEFT([Original Column]@row, 6) = "000000",

            IF(LEFT([Original Column]@row, 7) = "0000000",

              IF(LEFT([Original Column]@row, 8) = "00000000",

                IF(LEFT([Original Column]@row, 9) = "000000000",

                  [Original Column]@row,

                  RIGHT([Original Column]@row, LEN([Original Column]@row) - 9)),

                RIGHT([Original Column]@row, LEN([Original Column]@row) - 8)),

              RIGHT([Original Column]@row, LEN([Original Column]@row) - 7)),

            RIGHT([Original Column]@row, LEN([Original Column]@row) - 6)),

          RIGHT([Original Column]@row, LEN([Original Column]@row) - 5)),

        RIGHT([Original Column]@row, LEN([Original Column]@row) - 4)),

      RIGHT([Original Column]@row, LEN([Original Column]@row) - 3)),

    RIGHT([Original Column]@row, LEN([Original Column]@row) - 2)),

  [Original Column]@row)

This formula checks for up to 9 leading zeroes by nesting 9 IF statements. If the value in the "Original Column" starts with 1-9 leading zeroes, it removes them using the RIGHT and LEN functions. If there are no leading zeroes, it returns the original value.

HTH!

DanielLevelscape

@Sara H The previous solution here was overly complex, and I wanted to leave a correct response here for anyone else who finds this question.

You only need to use a single formula to remove all leading zeros from a text or autonumber field.

=VALUE( [Original Column]@row )

In this way, the VALUE function translates the autonumber or text into a number, which automatically removes all leading zeros from the column.