# 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!
###### Who is Participating?

x

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

Commented:
Try this formula...

=IF(LEFT(A1, 1)="1", RIGHT(A1, 10), A1)
0

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)
``````
0

Excel & VBA ExpertCommented:
@Rob
Absolutely perfect. :)
0

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.