convert phone number

[andrewm]

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

 

[Fred Smith]

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

Regards,
Fred

 

[Bernard Liengme]

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

 

[andrewm]

Thank you Fred !

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

Regards,
Fred

 

[Fred Smith]

Glad to help.
Fred

Thank you Fred !