How do I strip out all non-numeric characters?

# Thread: How do I strip out all non-numeric characters?

1. ## How do I strip out all non-numeric characters?

How do I strip out all non-numeric characters?

I have 10,000 phone numbers, of which people entered in different formats, such as
212.123.1234
212 123 1234
212-123-1234
(212)123-1234

How do I strip out ALL the data/characters that are NOT numbers so the result is

2121231234

I want all my phone numbers to be consistent. I can individually FIND and REPLACE the .()- and spaces with "" nothing but sometimes there remain hidden characters after the 10 numbers. I know this from doing a =LEN(A1) and it reports 11, even though I see only 10 numbers. If I highlight the field and hit delete at the end of the 10 numbers, it deletes SOMETHING which is invisible and takes the length to 10 numbers as it should be.

So, how do I strip out ALL the data/characters that are NOT numbers so the result is only the 10 numbers?

2. ## Re: How do I strip out all non-numeric characters?

Use PGC01's OnlyDigits function from here:

3. ## Re: How do I strip out all non-numeric characters?

Another way if you have Morefunc:

example
AB
1212.123.12342121231234
2212 123 12342121231234
3212-123-12342121231234
4(212)123-12342121231234
Excel 2003

Array Formulas
CellFormula
B1=--MCONCAT(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

4. ## Re: How do I strip out all non-numeric characters?

I'm rather keen on this formula courtesy of Ron Coderre:

Sheet1
AB
24212.123.12342121231234
Excel 2002

Worksheet Formulas
CellFormula
B24=SUMPRODUCT(MID(0&A24,LARGE(INDEX(ISNUMBER(--MID(A24,ROW(\$1:\$25),1))*
ROW(\$1:\$25),0
),ROW(\$1:\$25)
)+1,1
)*10^ROW(\$1:\$25)/10
)

5. ## Re: How do I strip out all non-numeric characters?

Originally Posted by Richard Schollar
I'm rather keen on this formula courtesy of Ron Coderre:

Sheet1
AB
24212.123.12342121231234
Excel 2002

Worksheet Formulas
CellFormula
B24=SUMPRODUCT(MID(0&A24,LARGE(INDEX(ISNUMBER(--MID(A24,ROW(\$1:\$25),1))*
ROW(\$1:\$25),0
),ROW(\$1:\$25)
)+1,1
)*10^ROW(\$1:\$25)/10
)
Wow
One for the favourites list.

6. ## Re: How do I strip out all non-numeric characters?

regexp seems to be one of the best option to remove all type non-numeric characters.

7. ## Re: How do I strip out all non-numeric characters?

This works perfectly except for when the cell is blank/empty, it returns a value of zero/0.

THanks to all for the superfast replies. My skill level is beginner.

Originally Posted by Richard Schollar
I'm rather keen on this formula courtesy of Ron Coderre:

Sheet1
AB
24212.123.12342121231234
Excel 2002

Worksheet Formulas
CellFormula
B24=SUMPRODUCT(MID(0&A24,LARGE(INDEX(ISNUMBER(--MID(A24,ROW(\$1:\$25),1))*
ROW(\$1:\$25),0
),ROW(\$1:\$25)
)+1,1
)*10^ROW(\$1:\$25)/10
)

8. ## Re: How do I strip out all non-numeric characters?

You could test for that in an IF statement:

=IF(A1="","",SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(\$1:\$25),1))*
ROW(\$1:\$25),0
),ROW(\$1:\$25)
)+1,1
)*10^ROW(\$1:\$25)/10
))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•