Take 1 out of the phone number

I have a column of phone numbers that are 10 or 11 digits. Currently they are formatted as text. Some phone numbers have a 1 in the front, some do not.

How can I take the 1 out of the phone numbers that have it? That way they are are consistently 10 digits.

Thanks!
cansevinAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Don't need the IF statement, just:

=RIGHT(A1,10)

will suffice.

If the value is 11 characters then the first will be dropped, if it is 10 characters then you get it all.

Thanks
Rob
1
 
Wayne Taylor (webtubbs)Commented:
Try this formula...

  =IF(LEFT(A1, 1)="1", RIGHT(A1, 10), A1)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Another way is you can check the length of the string like this.....
Assuming your phone number string is in A2, then try
=IF(LEN(A2)>10,RIGHT(A2,10),A2)

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Rob
Absolutely perfect. :)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Though Rob offered the shortest formula, all the proposed solutions will resolve the question, so point should be split between all the experts.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.