Excel Formula for removing leading 1 on phone number if present

Question

I am needing to create a formula that removes the leading 1 from a 11 digit phone number as shown below.

I would also like the formula to exclude any 10 numbers that doesn't have a leading 1.

I am trying to have my list all the same with just 10 digits.

Solution 1

Try this if you are sure the value are clean and no spaces and dash

=IF(LEN(A1)=11, RIGHT(A1,10),A1)

Solution 2

Use this simple formula:

=--Right(E2,10)

The Right() Function turns it into text so the -- turns it back to a number.

Solution 3

Try,

=IFERROR(--TEXT(--RIGHT(E2, 10), "[>1999999999]0000000000;;;"), "")

Comments

[Admin]

The RIGHT function returns the ten-right-most digits regardless of what the 11th digit is or even if there isn't an 11th digit. The "[>1999999999]0000000000;;;" format mask only produces a ten-digit number if the first digit starts with at least a 2; nothing if it starts with a 1 or is a nine-or-less digit number.

[Scott Craner]

And we look for the 2 because US Area codes start with 201 and go up. I see, It is a way to check if the data is valid.

[joel]

You rock! The below formula worked perfectly. The 11 digit number had no dashes or spaces. :) =IF(LEN(A1)=11, RIGHT(A1,10),A1)