capturing phone numbers help

M

Mark

I can't figure out how to use @Text properly.

Lets say A1 has text of (318) 123-4567

and I want to put a formula in A2 to capture only the numbers and store them
as 3181234567 in A2.

A1 is not formatted, its just text.

This can't be very hard, how to do??
Thx mucho !!

Mark
 
M

mariab

Try this formula:
=text (concatenate(mid(A1,2,3),mid(A1,6,3),right(A1,4)),"0000000000")
 
D

Don Guillett

or
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"(",""),")",""),"
",""),"-","")
 
R

Ron Rosenfeld

I can't figure out how to use @Text properly.

Lets say A1 has text of (318) 123-4567

and I want to put a formula in A2 to capture only the numbers and store them
as 3181234567 in A2.

A1 is not formatted, its just text.

This can't be very hard, how to do??
Thx mucho !!

Mark
Click to expand...

If you happen to have Longre's free morefunc.xll add-in, available at
http://xcell05.free.fr/

you could use the simple formula:

=REGEX.SUBSTITUTE(A1,"\D")

This will remove every character that is not a digit.




--ron
 
Top