How do I take out a dash in a number like 827-6541 in a XL list

[Guest]

I have a "list of fax #s in xl that I am tyring to send as a broadcast fax.
The service I have requires that the fax number be in a format with no
characters other than the numbers. For example, (912) 678-8344 must be
changed to 19126788344. Not characters other than numbers and no spaces.
Can anyone tell me a way to do this without reinventing the computer?

 

[Guest]

Assuming data as posted is representative and is running in A1 down,
perhaps this might suffice:

In B1, copied down:
="1"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"
",""),")",""),"(","")

 

[Chip Pearson]

If the phone number is in A1, use

="1"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-",""),"
","")

Watch out for line wrapping. The formula above should be in one line.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Dave Peterson]

One way is to do a bunch of Edit|Replaces:

Replace (
with (leaveblank)

Same with ), then - and with the space character.

 

[Guest]

Thanx All, but all this information is like a Linda Rhonstad song, "Blue
Bijou". It all blew right by me. Too..oooooo technical for me. But thanx
anyway.

 

[T. Valko]

Another one:

="1"&MID(A1,2,3)&MID(A1,7,3)&RIGHT(A1,4)

Biff

 

[Max]

Think we still need to append the leading "1" to the result after all
of the Edit | Replaces <g>

 

[Max]

Kelley,
The implementation of the many options given is simpler than trying to
sing/sound like Linda Ronstadt <g>. Do give all of the options a go or
2. You may be pleasantly surprised ..

 

[Max]

The earlier suggestion as posted has an unfortunate line-break which
could trip things up.

Here's a re-post with an intentional line-break to smoothen
implementation
(Just copy direct from the post, then paste into the formula bar for
B1)

In B1, copied down:
="1"&SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(A1,"-","")," ",""),")",""),"(","")


---

 

[rhhince]

Would it not be as easy as highlighting the whole column of numbers and
using Ctrl + H to replace the characters you wish to delete in one
swoop?

 

[Dave Peterson]

Yep, so maybe using a formula in an adjacent column--after the edit|replaces
would help.
=A1+10000000000

(or a custom format or adding 10000000000 to each cell...)

 

[Guest]

Now that has to be the most straight forward solution ever. Even though there
are three separate characters to delete, (,) and -, This works!