Results 1 to 8 of 8

How do I strip out all non-numeric characters?

This is a discussion on How do I strip out all non-numeric characters? within the Excel Questions forums, part of the Question Forums category; How do I strip out all non-numeric characters? I have 10,000 phone numbers, of which people entered in different formats, ...

  1. #1
    New Member
    Join Date
    Apr 2010
    Posts
    30

    Default 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. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,579

    Default Re: How do I strip out all non-numeric characters?

    Use PGC01's OnlyDigits function from here:

    Extract only the numbers
    Microsoft MVP - Excel

  3. #3
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    10,037

    Default 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. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default 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
    )

    Richard Schollar

    Using xl2013

  5. #5
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    10,037

    Default Re: How do I strip out all non-numeric characters?

    Quote Originally Posted by Richard Schollar View Post
    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. #6
    Board Regular northwolves's Avatar
    Join Date
    Jun 2006
    Location
    Taiyuan,China
    Posts
    1,115

    Default 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. #7
    New Member
    Join Date
    Apr 2010
    Posts
    30

    Default 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.

    Quote Originally Posted by Richard Schollar View Post
    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. #8
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default 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
    ))
    Richard Schollar

    Using xl2013

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com