Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Take 1 out of the phone number

Posted on 2016-09-01
Medium Priority
44 Views
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!
0
Question by:cansevin
• 3

LVL 48

Expert Comment

ID: 41780895
Try this formula...

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

LVL 33

Expert Comment

ID: 41781063
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

LVL 34

Accepted Solution

Rob Henson earned 2000 total points
ID: 41781171
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

LVL 33

Expert Comment

ID: 41781172
@Rob
Absolutely perfect. :)
0

LVL 33

Expert Comment

ID: 41821088
Though Rob offered the shortest formula, all the proposed solutions will resolve the question, so point should be split between all the experts.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month10 days, 22 hours left to enroll