Convert txt to numbers in excel then change format

[rffEn]

Hi Everyone,

I have phone numbers in excel that are txt and want to change to numbers.  Also need to change format from (000) 000-0000 to 000-000-000.  I have several worksheets with these two formats and need to compare , ID duplicates, etc.

Many thanks,  Bob

[#2]

Hello @rffEn,

This formula was found courtesy of Extendoffice.com:

=SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)

After applying this formula, Format your cells as Special and then by Phone Number.

[#3]

@rffEn 

Another variant is

with

=REPLACE(REPLACE(REPLACE(A1,1,1,""),4,2,""),7,1,"")+0

if only they are initially not numbers with applied phone number format

[#4]

@PReagan Hey, I used your formula and it works perfectly! However, it only extracts 2 numbers. The third number is all zeros. 

Example: "I'm trying to extract all of these numbers 02060874,02270190,02345347 in this sentence." 

What I get in return is 2060874022701900000000. 

How do I get that third number to appear instead of the zeros using this formula?

Thanks in advance!

[#5]

Hello @bobby24,

The formula above is not appropriate for what you are looking to accomplish. My suggestion would be to use the "Text to Columns" feature. To do this:

1. Select the cell with phone numbers that you would like to extract
2. In the Data tab, select "Text to Columns"
3. Select Delimited > Next, Comma > Next, General > Finish.
4. Select all of the numbers and format as Special > Phone Number

For future reference, please ask new questions in a new thread.

[#6]

@PReagan 

Or look here

https://techcommunity.microsoft.com/t5/excel/extracting-numbers-in-middle-of-paragraph/m-p/1111692

[#7]

I did not see this thread. Thank you @Riny_van_Eekelen