Jul 12 2020 06:29 AM
I want to change a phone number from (555) 555-5555 to 15555555555 how can you do this in excel? I have about 1,000 numbers that I need to change. I have tried data numbers but won't change?
Jul 12 2020 06:41 AM
Jul 12 2020 09:17 AM
@NikolinoDE I tried that and still won't change?
Jul 12 2020 09:24 AM - edited Jul 12 2020 09:27 AM
@johnathancarlson Suppose your phone numbers are in column A, starting at A1, try this:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",1),") ",""),"-","")
and copy the formula down as far as needed.
Jul 12 2020 09:38 AM
Jul 12 2020 09:40 AM
Jul 12 2020 11:46 AM
As variant and assuming
- you have texts, not formatted numbers as phone numbers;
- all texts have exactly the same patters;
- your Excel support dynamic arrays
formula could be
=1&CONCAT(MID(A1,{2,7,11},{3,3,4}))
if return text, and
=(1&CONCAT(MID(A1,{2,7,11},{3,3,4})))*1
if return number