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

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?

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

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(A 1,"-",""),"
",""),")",""),"(","")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kelley" wrote:
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?

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

If the phone number is in A1, use

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

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)




"Kelley" wrote in message
...
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?

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

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

Replace (
with (leaveblank)

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

Kelley wrote:

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?

--

Dave Peterson

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

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.

"Kelley" wrote:

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?

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

Another one:

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

Biff

"Kelley" wrote in message
...
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?

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

Think we still need to append the leading "1" to the result after all
of the Edit | Replaces <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Dave Peterson wrote:
One way is to do a bunch of :

Replace (
with (leaveblank)

Same with ), then - and with the space character

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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Kelley wrote:
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.

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

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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?


Kelley wrote:
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?

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

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


Max wrote:

Think we still need to append the leading "1" to the result after all
of the Edit | Replaces <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Dave Peterson wrote:
One way is to do a bunch of :

Replace (
with (leaveblank)

Same with ), then - and with the space character

--

Dave Peterson

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

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

"rhhince" wrote:

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?


Kelley wrote:
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?