Forum Discussion
Convert txt to numbers in excel then change format
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
6 Replies
Another variant is
with
=REPLACE(REPLACE(REPLACE(A1,1,1,""),4,2,""),7,1,"")+0if only they are initially not numbers with applied phone number format
Hello rffEn,
This formula was found courtesy of https://www.extendoffice.com/documents/excel/1622-excel-extract-number-from-string.html
=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.
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!
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:
- Select the cell with phone numbers that you would like to extract
- In the Data tab, select "Text to Columns"
- Select Delimited > Next, Comma > Next, General > Finish.
- Select all of the numbers and format as Special > Phone Number
For future reference, please ask new questions in a new thread.
