I need to add the US prefix to a list of cellphone numbers and delete the
parentheses . How do i do that in batch ?

ex : i need to go from

(xxx)xxx-xxxx to 1xxx-xxxx

You do something like this:
="1"&substitute(substitute(a1,"(",""),")","")

Regards,
Fred

"andrewm" wrote in message
...
I need to add the US prefix to a list of cellphone numbers and delete the
parentheses . How do i do that in batch ?

ex : i need to go from

(xxx)xxx-xxxx to 1xxx-xxxx

Is the original data text or number (look in the formula bar to see it the
() are present in the stored value)

If the original data is in text form (not a formatted number):
1) if your actually want 1xxx-xxx-xxxx (that is the general form for North
America phone numbers) then, use Edit/Find&Replace to change "(" to "1" and
again to change ) to "-"
2) if you really want 1xxx-xxxx (without the area code); let's assume the
first one is in A1, so in B1 use =1&RIGHT(A1,8) and copy down the column.
Then use Copy followed by Paste Special/Values to change the formulas to
values and hence allow you to delete column A

If the original data is a formatted number use ==A1+10000000000 and reformat
to get 1xxx-xxx-xxxx
or =MOD(A1,10000000)+10000000 to get 1xxx-xxxx and reformat. In either case
use followed by Paste Special/Values to change the formulas to values and
hence allow you to delete column A

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"andrewm" wrote in message
...
I need to add the US prefix to a list of cellphone numbers and delete the
parentheses . How do i do that in batch ?

ex : i need to go from

(xxx)xxx-xxxx to 1xxx-xxxx

Thank you Fred !

"Fred Smith" wrote:

You do something like this:
="1"&substitute(substitute(a1,"(",""),")","")

Regards,
Fred

"andrewm" wrote in message
...
I need to add the US prefix to a list of cellphone numbers and delete the
parentheses . How do i do that in batch ?

ex : i need to go from

(xxx)xxx-xxxx to 1xxx-xxxx

Glad to help.
Fred

"andrewm" wrote in message
...
Thank you Fred !

"Fred Smith" wrote:

You do something like this:
="1"&substitute(substitute(a1,"(",""),")","")

Regards,
Fred

"andrewm" wrote in message
...
I need to add the US prefix to a list of cellphone numbers and delete
the
parentheses . How do i do that in batch ?

ex : i need to go from

(xxx)xxx-xxxx to 1xxx-xxxx