Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Need to copy phone number from a cell stripping non number chars 3

Status
Not open for further replies.

mjstout

IS-IT--Management
Apr 19, 2006
10
US
Hi,

I need to move contacts from a large number of BlackBerrys to Motorola i530 phones.

I'm doing all of my conversion in Excel.

Phone numbers from the BBs can look like this 916-357-7531.
I530 phones don't accept non number-characters.

Is there a function that will copy a number 916-357-7531 from cell (A1) and place it into cell (A2) as 9163577531 ?

Thank you,

Mike
 
format the A2 cells with the numbering you require(custom). Copy and Paste Special as Text (you must selecet this button) to use the A2's formatting. It'll work...
 
Unfortunately copy and paste special will not work in this case because the import/export data from one phone is in a completely different format then that of the other phone.

Copy and pasting I would have to do one phone number at a time and there are hundreds of contacts per phone.

I need to pull the data from a cell into another and remove the non-number characters in the process.
 
If your phone numbers are in column A, then type this into a different column:
[tab][COLOR=blue white]=SUBSTITUTE(A1,"-","")[/color]

Fill down as needed.

That will remove any dashes that might be present and will not change numbers without the dashes.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
Thanks John that's a good start.
Some of the numbers also contain (,),/, and x.
Is there a function that can get rid of all non-number character? or a way I can create something like that?

Thanks again!

Mike
 
Well, this ain't pretty, but based on the same logic as my previous answer you can come up with this nested function:

[COLOR=blue white]=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1, "-", ""), "(", ""), ")", ""), "X", ""), "/", ""), "\", ""), " ", "")[/color]

That will remove the following characters:
(
)
/
\
x


along with all spaces left behind.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
Lol.. I was trying to do something like that too. Thanks a lot John Ill run with that!

Mike
 
Another option...

=LEFT(A1,3)&MID(A1,5,3)&MID(A1,9,4)

This will eliminate ANY characters, but unlike John's Substitute function, this requires that the characters to be stripped are in the SAME position.

You've probably got John's formula working, but in case not, here's a (slightly) modified version.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),",",""),")", ""),"x",""),"/",""),"\","")," ","")

Regards, Dale Watson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top