Solved

Take 1 out of the phone number

Posted on 2016-09-01
7
42 Views
Last Modified: 2016-09-29
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
Comment
Question by:cansevin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
7 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41780895
Try this formula...

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

Expert Comment

by:Subodh Tiwari (Neeraj)
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)

Open in new window

0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 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 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41781172
@Rob
Absolutely perfect. :)
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question