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

K

Kelley

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?
 
M

Max

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,"-",""),"
",""),")",""),"(","")
 
C

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)
 
D

Dave Peterson

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

Replace (
with (leaveblank)

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

Kelley

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

Max

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

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

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,"-","")," ",""),")",""),"(","")
 
R

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?
 
D

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

JR Hester

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