Page 1 of 2 12 LastLast
Results 1 to 10 of 11

remove all characters but the numbers

This is a discussion on remove all characters but the numbers within the Excel Questions forums, part of the Question Forums category; Please tell me there is a none VBA way to remove everything from a cell but the numbers. Example: A, ...

  1. #1
    Board Regular spanspace's Avatar
    Join Date
    Jan 2007
    Posts
    159

    Default remove all characters but the numbers

    Please tell me there is a none VBA way to remove everything from a cell but the numbers.

    Example: A, DAVID (002081) becomes 002081

    The cell the formula will go in is F4 and the cell its looking at is K4.

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,543

    Default Re: remove all characters but the numbers

    If the number is always inside parentheses try

    =MID(K4,FIND("(",K4)+1,FIND(")",K4)-FIND("(",K4)-1)
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,319

    Default Re: remove all characters but the numbers

    maybe this

    Code:
    Function onlynumbers(ByVal ref As String)
    Dim rx As Object
    
    Set rx = CreateObject("VBScript.RegExp")
    
    With rx
        .Pattern = "\D"
        .Global = True
        onlynumbers = .Replace(ref, "")
    End With
    End Function

    Sheet3
    AB
    7asdf4534asdf345343
    Excel 2003

    Worksheet Formulas
    CellFormula
    B7=onlynumbers(A7)

    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  4. #4
    Board Regular spanspace's Avatar
    Join Date
    Jan 2007
    Posts
    159

    Default Re: remove all characters but the numbers

    VoG that worked great to a point. It removed everything but the number but the when the cell to the left of it tries to use it as a reference it returns a #N/A. If I manually type the number into the cell then the to the left works fine.

    Quality
    BC
    5#N/A224290
    Excel 2007

    Worksheet Formulas
    CellFormula
    B5=VLOOKUP(C5,Agents!$J$2:$Q$640,MATCH("4DIGIT",Agents!$J$2:$Q$2,0),FALSE)
    C5=MID(H5,FIND("(",H5)+1,FIND(")",H5)-FIND("(",H5)-1)


  5. #5
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,319

    Default Re: remove all characters but the numbers

    a small change to Peters' formula would do
    Code:
    =MID(H5,FIND("(",H5)+1,FIND(")",H5)-FIND("(",H5)-1)+0
    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  6. #6
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,543

    Default Re: remove all characters but the numbers

    Maybe this (it will discard leading zeros)

    =MID(K4,FIND("(",K4)+1,FIND(")",K4)-FIND("(",K4)-1)+0
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    Board Regular spanspace's Avatar
    Join Date
    Jan 2007
    Posts
    159

    Default Re: remove all characters but the numbers

    Awesome man. I learned something today and I appreciate it.

  8. #8
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,157

    Default Re: remove all characters but the numbers

    =-replace(a1,1,find("(",a1)-1,"")
    Office 2007/2010

  9. #9
    Board Regular spanspace's Avatar
    Join Date
    Jan 2007
    Posts
    159

    Default Re: remove all characters but the numbers

    VoG's formula worked great. But I'd like to understand your's as well. Can you walk me through it a bit?

  10. #10
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,157

    Default Re: remove all characters but the numbers

    Sure, Excel treats numbers in parentheses as negative numbers.

    So all you have to do is replace from the 1st character to just before the first parentheses with nothing.

    By putting the - sign in front, you are effectively applying math to the text value, thus coercing it to a number, and negating the negative value which will return a positive value.

    -(002081) is equal to 2081
    Office 2007/2010

Page 1 of 2 12 LastLast

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