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

[mjstout]

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

 

[NoCalAdmin]

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...

 

[mjstout]

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.

 

[AnotherHiggins]

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.

 

[mjstout]

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

 

[AnotherHiggins]

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.

 

[mjstout]

Lol.. I was trying to do something like that too. Thanks a lot John Ill run with that!

Mike

 

[DaleWatson123321]

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