Phone Number in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1516999%22%20slang%3D%22en-US%22%3EPhone%20Number%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516999%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20change%20a%20phone%20number%20from%20(555)%20555-5555%20to%2015555555555%20how%20can%20you%20do%20this%20in%20excel%3F%20I%20have%20about%201%2C000%20numbers%20that%20I%20need%20to%20change.%20I%20have%20tried%20data%20numbers%20but%20won't%20change%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1516999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517012%22%20slang%3D%22de-DE%22%3ESubject%3A%20Phone%20Number%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517012%22%20slang%3D%22de-DE%22%3ERight%20mouse%20click%20-%20Format%20cells%20-%20Special%20format%20-%20Select%20country%20and%20depending%20on%20the%20country%20the%20phone%20format%20also%20appears.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20That%20would%20be%20the%20easiest%20and%20fastest%20solution%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%3CBR%20%2F%3E%20Have%20fun%20with%20Excel%20...%20like%20me.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Beginners%20Nikolino%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517124%22%20slang%3D%22en-US%22%3EBetreff%3A%20Phone%20Number%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3BI%20tried%20that%20and%20still%20won't%20change%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517137%22%20slang%3D%22en-US%22%3ERe%3A%20Phone%20Number%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F726172%22%20target%3D%22_blank%22%3E%40johnathancarlson%3C%2FA%3E%26nbsp%3BSuppose%20your%20phone%20numbers%20are%20in%20column%20A%2C%20starting%20at%20A1%2C%20try%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1%2C%22(%22%2C1)%2C%22)%20%22%2C%22%22)%2C%22-%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20copy%20the%20formula%20down%20as%20far%20as%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517163%22%20slang%3D%22de-DE%22%3ESubject%3A%20Phone%20Number%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517163%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F726172%22%20target%3D%22_blank%22%3E%40johnathancarlson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fde-de%2Foffice%2Fanzeigen-von-zahlen-als-telefonnummern-7feabe49-15b3-411c-a030-781b4f2c87dd%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fde-de%2Foffice%2Fanzeigen-von-zahlen-als-telefonnummern-7feabe49-15b3-411c-a030-781b4f2c87dd%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emaybe%20this%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517167%22%20slang%3D%22de-DE%22%3ESubject%3A%20Phone%20Number%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517167%22%20slang%3D%22de-DE%22%3EWe%20assume%20that%20cell%20A4%20contains%20the%20phone%20number%20%220815%20%2F%204711-55%22.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Solution%2C%20Part%201%20%3CBR%20%2F%3E%20Remove%20the%20hyphen%20with%20this%20formula%3A%20%3CBR%20%2F%3E%20%3DCHANGE(A4%3B%22%20-%22%3B%22%20%22)%20%3CBR%20%2F%3E%20The%20second%20argument%20of%20the%20function%20indicates%20the%20character%20to%20be%20replaced%2C%20the%20third%20indicates%20the%20surrogate%20character%20(in%20our%20case%2C%20a%20space).%20%3CBR%20%2F%3E%20Solution%2C%20Part%202%20%3CBR%20%2F%3E%20Nestay%20a%20second%20CHANGE%20function%20to%20also%20remove%20the%20slash%3A%20%3CBR%20%2F%3E%20%3DCHANGE(A4%3B%22%20-%22%3B%22%20%22)%3B%22%20%2F%22%3B%22%20%22)%20%3CBR%20%2F%3E%20Solution%2C%20Part%203%20%3CBR%20%2F%3E%20With%20SMOOTH%2C%20finally%20delete%20all%20superfluous%20spaces%3A%20%3CBR%20%2F%3E%20%3DSMOOTH(A4%3B%22%20-%22%3B%22%20%22)%3B%22%20%2F%22%3B%22%20%22)).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517343%22%20slang%3D%22en-US%22%3ERe%3A%20Phone%20Number%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F726172%22%20target%3D%22_blank%22%3E%40johnathancarlson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20and%20assuming%3C%2FP%3E%0A%3CP%3E-%20you%20have%20texts%2C%20not%20formatted%20numbers%20as%20phone%20numbers%3B%3C%2FP%3E%0A%3CP%3E-%20all%20texts%20have%20exactly%20the%20same%20patters%3B%3C%2FP%3E%0A%3CP%3E-%20your%20Excel%20support%20dynamic%20arrays%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D1%26amp%3BCONCAT(MID(A1%2C%7B2%2C7%2C11%7D%2C%7B3%2C3%2C4%7D))%0Aif%20return%20text%2C%20and%0A%0A%3D(1%26amp%3BCONCAT(MID(A1%2C%7B2%2C7%2C11%7D%2C%7B3%2C3%2C4%7D)))*1%0Aif%20return%20number%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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? 

6 Replies
Highlighted
Right mouse click - Format cells - Special format - Select country and depending on the country the phone format also appears.

That would be the easiest and fastest solution
Have fun with Excel ... like me.

Beginners Nikolino
Highlighted

@Nikolino I tried that and still won't change? 

Highlighted

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

Highlighted
Wir nehmen an, dass die Zelle A4 die Telefonnummer "0815 / 4711-55" enthält.

Lösung, Teil 1
Entfernen Sie den Bindestrich mit dieser Formel:
=WECHSELN(A4;"-";" ")
Das zweite Argument der Funktion gibt das zu ersetzende Zeichen an, das dritte das Ersatzzeichen (in unserem Fall ein Leerzeichen).
Lösung, Teil 2
Verschachteln Sie eine zweite WECHSELN-Funktion, um auch den Schrägstrich zu entfernen:
=WECHSELN(WECHSELN(A4;"-";" ");"/";" ")
Lösung, Teil 3
Mit GLÄTTEN löschen Sie abschließend alle überflüssigen Leerzeichen:
=GLÄTTEN(WECHSELN(WECHSELN(A4;"-";" ");"/";" ")).
Highlighted

@johnathancarlson 

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