remove all characters but the numbers

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: remove all characters but the numbers

  1. #1
    Board Regular spanspace's Avatar
    Join Date
    Jan 2007
    Posts
    159
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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,468
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,468
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    19,225
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: remove all characters but the numbers

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

  9. #9
    Board Regular spanspace's Avatar
    Join Date
    Jan 2007
    Posts
    159
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    19,225
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    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 2010/365

User Tag List

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