Converting 10 digit phone number with dashes to 11 digits with no dashes

%3CLINGO-SUB%20id%3D%22lingo-sub-2308961%22%20slang%3D%22en-US%22%3EConverting%2010%20digit%20phone%20number%20with%20dashes%20to%2011%20digits%20with%20no%20dashes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308961%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20list%20of%201200%20clients%20phone%20numbers%20in%20Excel%20and%20I%20need%20to%20convert%20them%20as%20follows.%3C%2FP%3E%3CP%3E602-457-0377%26nbsp%3B%20%26nbsp%3B%20Current%20value.%3C%2FP%3E%3CP%3E16024570377%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDesired%20new%20value%20needed%20with%20no%20formula%20in%20the%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2308961%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-2309304%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%2010%20digit%20phone%20number%20with%20dashes%20to%2011%20digits%20with%20no%20dashes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2309304%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041284%22%20target%3D%22_blank%22%3E%40Prestige_Realty%3C%2FA%3E%26nbsp%3BSay%20all%20your%20numbers%20with%20the%20hyphens%20are%20in%20column%20A.%20Enter%20how%20you%20want%20to%20see%20the%20first%20number%20directly%20next%20to%20it%20in%20column%20B.%20From%20you%20description%20it%20would%20be%20like%20a%20number%201%20plus%20all%20numbers%20from%20A%2C%20skipping%20the%20hyphens.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20entire%20range%20of%20cells%20where%20you%20want%20to%20repeat%20the%20pattern.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-04-30%20at%2007.06.29.png%22%20style%3D%22width%3A%20290px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F276978iC78FE51CD699EFDB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-04-30%20at%2007.06.29.png%22%20alt%3D%22Screenshot%202021-04-30%20at%2007.06.29.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENow%20press%20Ctrl-E%20(Flash%20fill)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-04-30%20at%2007.11.00.png%22%20style%3D%22width%3A%20272px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F276979i6FAAA4B9AB07C634%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-04-30%20at%2007.11.00.png%22%20alt%3D%22Screenshot%202021-04-30%20at%2007.11.00.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a large list of 1200 clients phone numbers in Excel and I need to convert them as follows.

602-457-0377    Current value.

16024570377     Desired new value needed with no formula in the value.

 

Thank you in advance!

3 Replies

@Prestige_Realty Say all your numbers with the hyphens are in column A. Enter how you want to see the first number directly next to it in column B. From your description it would be like a number 1 plus all numbers from A, skipping the hyphens.

 

Select the entire range of cells where you want to repeat the pattern.

Screenshot 2021-04-30 at 07.06.29.png

Now press Ctrl-E (Flash fill):

Screenshot 2021-04-30 at 07.11.00.png

@Riny_van_Eekelen Brilliant!  This is exactly what I needed.  Thank you so much!  I was spending hours Googling it and watching youtube videos and now I can do my whole worksheet within a minute!