HOW TO CHANGE A NUMBER 787-543-1251 TO A NUMBER 7875431251 THROUGH FIND&SELECT AND THEN REPLACE

%3CLINGO-SUB%20id%3D%22lingo-sub-1566712%22%20slang%3D%22en-US%22%3EHOW%20TO%20CHANGE%20A%20NUMBER%20787-543-1251%20TO%20A%20NUMBER%207875431251%20THROUGH%20FIND%26amp%3BSELECT%20AND%20THEN%20REPLACE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566712%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20learn%20how%20to%20change%20a%20telephone%20number%20that%20is%20formated%20787-543-1251%20to%20be%20formated%207875431251%20in%20the%20excel%20program%20using%20Find%26amp%3BSelect%20and%20then%20Replace.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1566712%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566748%22%20slang%3D%22de-DE%22%3ESubject%3A%20HOW%20TO%20CHANGE%20A%20NUMBER%20787-543-1251%20TO%20A%20NUMBER%207875431251%20THROUGH%20FIND%26amp%3BSELECT%20AND%20THEN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566748%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749870%22%20target%3D%22_blank%22%3E%40MIGUEL1969%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EIn%20excel%20there%20are%20different%20options%20for%20different%20users.%3C%2FSPAN%3E%20%3CSPAN%3EHere%20is%20a%20quick%20example%20of%20how%20to%20do%20it.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3E%3DSUBSTITUTE(A1%2C%22-%22%2C%22%22)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20find%20this%20helpful%2C%20please%20mark%20it%20as%20%22Best%20Answer%22%20and%20as%20Like%20(click%20thumbs%20up)%2C%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566762%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%20CHANGE%20A%20NUMBER%20787-543-1251%20TO%20A%20NUMBER%207875431251%20THROUGH%20FIND%26amp%3BSELECT%20AND%20THEN%20REPL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566762%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749870%22%20target%3D%22_blank%22%3E%40MIGUEL1969%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends.%20If%20you%20already%20have%20numbers%20formatted%20as%20phone%20numbers%2C%20when%20change%20format%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20430px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210357iA900AE96C56F8760%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20that's%20the%20text%2C%20Ctrl%2BH%20and%20Replace%20All%20dashes%20on%20nothing%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20478px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210358iD27581678DE864A5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566782%22%20slang%3D%22de-DE%22%3ERe%3A%20HOW%20TO%20CHANGE%20A%20NUMBER%20787-543-1251%20TO%20A%20NUMBER%207875431251%20THROUGH%20FIND%26amp%3BSELECT%20AND%20THEN%20REPL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566782%22%20slang%3D%22de-DE%22%3EDifferent%20solutions%20for%20different%20notes%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%20...%20that's%20Excel%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nicolino%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566788%22%20slang%3D%22de-DE%22%3ERe%3A%20HOW%20TO%20CHANGE%20A%20NUMBER%20787-543-1251%20TO%20A%20NUMBER%207875431251%20THROUGH%20FIND%26amp%3BSELECT%20AND%20THEN%20REPL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566788%22%20slang%3D%22de-DE%22%3EDifferent%20solutions%20for%20different%20needs%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%20...%20that's%20Excel%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566851%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%20CHANGE%20A%20NUMBER%20787-543-1251%20TO%20A%20NUMBER%207875431251%20THROUGH%20FIND%26amp%3BSELECT%20AND%20THEN%20REPL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566851%22%20slang%3D%22en-US%22%3E%3CP%3EOh%20my%20I%20am%20really%20rusty%20at%20Excel!!!%20Just%20spent%2015%20minutes%20coming%20up%20with%20a%20solution%20to%20this%20problem%20only%20to%20read%20the%20answers%20and%20discovering%20a%20simple%20find%20and%20replace%20would%20have%20done%20it!%20I%20should%20have%20known%20that!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20my%20over%20complicated%20solution...%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there%20is%20probably%20an%20easier%20way%20to%20do%20it%20than%20this%20but%20this%20is%20what%20I%20have%20come%20up%20with%3A%3CBR%20%2F%3EI%20do%20it%20in%20two%20steps%3A%3CBR%20%2F%3Eif%20your%20number%20is%20in%20A1%2C%20then%20put%20this%20into%20B1%20(%3DLEFT(A1%2C7))%20and%20this%20into%20C1%20(%3DLEFT(A1%2C3)%26amp%3BRIGHT(B1%2C3)%26amp%3BRIGHT(A1%2C4))%3CBR%20%2F%3EB1%20will%20display%20your%20number%20in%20the%20correct%20format.%20Hope%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566870%22%20slang%3D%22de-DE%22%3ERe%3A%20HOW%20TO%20CHANGE%20A%20NUMBER%20787-543-1251%20TO%20A%20NUMBER%207875431251%20THROUGH%20FIND%26amp%3BSELECT%20AND%20THEN%20REPL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566870%22%20slang%3D%22de-DE%22%3Ethe%20task%20was%20not%20what%20you%20had%20in%20mind%20...%20but%20if%20your%20problem%20has%20a%20solution%2C%20%3CBR%20%2F%3E%20I%20am%20happy%20for%20you%20and%20wish%20you%20a%20pleasant%20day.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nicolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566883%22%20slang%3D%22de-DE%22%3ERe%3A%20HOW%20TO%20CHANGE%20A%20NUMBER%20787-543-1251%20TO%20A%20NUMBER%207875431251%20THROUGH%20FIND%26amp%3BSELECT%20AND%20THEN%20REPL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566883%22%20slang%3D%22de-DE%22%3EWith%20%22what%20you%20had%20in%20mind%22%20I%20meant%20the%20task.%20Not%20that%20I%20will%20be%20misunderstood.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Enjoy%20the%20Day%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I need to learn how to change a telephone number that is formated 787-543-1251 to be formated 7875431251 in the excel program using Find&Select and then Replace.

 

7 Replies

@MIGUEL1969 

 

In excel there are different options for different users. Here is a quick example of how to do it.

 

=SUBSTITUTE(A1,"-","")

 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

@MIGUEL1969 

It depends. If you already have numbers formatted as phone numbers, when change format

image.png

If that's the text, Ctrl+H and Replace All dashes on nothing

image.png

Different solutions for different notes ... that's Excel

Nicolino
Different solutions for different needs ... that's Excel

Oh my I am really rusty at Excel!!! Just spent 15 minutes coming up with a solution to this problem only to read the answers and discovering a simple find and replace would have done it! I should have known that! 

 

here is my over complicated solution...

 

Hi there is probably an easier way to do it than this but this is what I have come up with:
I do it in two steps:
if your number is in A1, then put this into B1 (=LEFT(A1,7)) and this into C1 (=LEFT(A1,3)&RIGHT(B1,3)&RIGHT(A1,4))
B1 will display your number in the correct format. Hope this helps.

the task was not what you had in mind ... but if your problem has a solution,
I am happy for you and wish you a pleasant day.



Nicolino
I know I don't know anything (Socrates)
With "what you had in mind" I meant the task. not that I will be misunderstood.

Enjoy the Day