Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45
  • Last Modified:

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!
0
cansevin
Asked:
cansevin
  • 3
1 Solution
 
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
 
Rob HensonIT & Database AssistantCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now