CODEWITHSUNDEEP
vbaexcelexcel-formulaformula
joel
joel

Reputation: 37

Excel Formula for removing leading 1 on phone number if present

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.

enter image description here

Upvotes: 2

Views: 42459

Answers (3)

Scott Craner
Scott Craner

Reputation: 152525

Use this simple formula:

=--Right(E2,10)

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

Upvotes: 2

user4039065
user4039065

Reputation:

Try,

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

Upvotes: 2

BobNoobGuy
BobNoobGuy

Reputation: 1645

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

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

Upvotes: 5

Related Questions