How do I strip out all non-numeric characters?

exceltm

New Member
Joined
Apr 5, 2010
Messages
32
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?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
Another way if you have Morefunc:

Excel Workbook
AB
1212.123.12342121231234
2212 123 12342121231234
3212-123-12342121231234
4(212)123-12342121231234
example
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
I'm rather keen on this formula courtesy of Ron Coderre:
Excel Workbook
AB
24212.123.12342121231234
Sheet1
Excel 2002
Cell Formulas
RangeFormula
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)
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Excel Workbook
AB
24212.123.12342121231234
Sheet1
Excel 2002
Cell Formulas
RangeFormula
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 :eek:
One for the favourites list. (y)
 

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,122
regexp seems to be one of the best option to remove all type non-numeric characters.
 

exceltm

New Member
Joined
Apr 5, 2010
Messages
32
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.

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

Excel Workbook
AB
24212.123.12342121231234
Sheet1
Excel 2002
Cell Formulas
RangeFormula
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)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,406
Messages
5,528,591
Members
409,827
Latest member
Tmcgrew05

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top