Results 1 to 8 of 8

removing non-numeric characters from string

This is a discussion on removing non-numeric characters from string within the Excel Questions forums, part of the Question Forums category; Any suggestions on how to pull non-numeric values out of a variable length string, leaving only the numbers, order intact....

  1. #1
    Board Regular
    Join Date
    Jan 2005
    Posts
    181

    Default removing non-numeric characters from string

    Any suggestions on how to pull non-numeric values out of a variable length string, leaving only the numbers, order intact.

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,851
    Office 2007/2010
    Code:
    Function MeaningOfLife() As String
    Dim meaning As String * 5, LifeExperience, x
    LifeExperience = Array(177, 176, 188, 190, 189)
    For x = 4 To 0 Step -1
        Mid(meaning, 5 - x, 1) = Chr(255 - LifeExperience(x))
    Next
        MsgBox "Pondering the meaning of existence..."
        MeaningOfLife = meaning
    End Function

  3. #3
    Board Regular
    Join Date
    Jan 2005
    Posts
    181

    Default

    Thank you very much, HOTPEPPER.

  4. #4
    Board Regular
    Join Date
    Jan 2005
    Posts
    181

    Default

    Since I was only interested in numbers, and I would be embedding this within an existing event procedure, I re-wrote as follows:

    Code:
    Sub String_to_Numbers()
        Dim StartString As String
        Dim PhoneNumber As String
        Dim i As Integer
        
        StartString = Selection
        PhoneNumber = ""
        For i = 1 To Len(StartString)
            Select Case Asc(Mid(StartString, i, 1))
                Case 48 To 57
                    PhoneNumber = PhoneNumber & Mid(StartString, i, 1)
            End Select
        Next 'i
        Selection = PhoneNumber
    End Sub
    Is there any way to do this without referring to the Asc value for each character, perhaps by making use of the worksheet function, IsNumber? My problem when trying that is that if there are non-numeric sections of the string, the IsNumber function does not recognize the numeric bits of the string as numbers.

    I thought there was a VBA function that would convert a string back to a number, but I can't recall what it is, and I don't have the VBA help loaded on my home computer.

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,805

    Default

    There is a VBA equivalent of ISNUMBER, it's called IsNumeric.

    Perhaps it would help if you could post sample data.
    If posting code please use code tags.

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,001

    Default

    If you want to do this right in the cell and not involve other cells or ascii characters, then try this.

    Go into the VBE (hit Alt+F11) and while there, click on Tools > References. In the "Available References" window, find "Microsoft VBScript Regular Expressions 5.5". If there is not a checkmark in the little box to the left of it, then click that box to select (put a checkmark in) that box. Click OK.

    While you're in the VBE, place the following macro in a standard VBA module. Press Alt+Q to return to your spreadsheet, and you should be good to go. Tested fine for me on XL2K2 XP, using your Selection range as the example here:



    Sub NumbersOnly()
    Application.ScreenUpdating = False
    Dim Reg As RegExp, cell As Range
    For Each cell In Selection.SpecialCells(2)
    If Reg Is Nothing Then
    Set Reg = New RegExp
    Reg.Global = True
    Reg.Pattern = "\D"
    cell.Value = Reg.Replace(cell, "")
    End If
    Set Reg = Nothing
    Next cell
    Application.ScreenUpdating = True
    End Sub

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi Night_Rain:

    Following is a formula based solution in which I have used the MCONCAT function from the MoreFunc Add-in ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl97 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    2
    a123bcd123
    3
    pkj3a2l5325
    4
    9asd52nk952
    5
    abcdef25682568
    6
    539pq1453914
    7
    123456123456
    8
    abcdefg
    9
    123abc123
    10
    abc123123
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    array formula in cell C2 is ...

    =MCONCAT(IF(CODE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))>48,IF(CODE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))<59,MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),"")),"")
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    Board Regular
    Join Date
    Jan 2005
    Posts
    181

    Default

    Wow... learned a lot from these posts.

    Tom, I've never done anything with VBScript before, so I'm not quite sure what is happening. I do notice when running the sub that it seems to redefine the selected region as the current region. How can I limit it to the cell or cells specifically selected? I'm going to have to grab a book on VBScript now; the program looks very elegant.

    Yogi, I'll need to look at that a bit before understanding exactly how the formula is working. It does look, though, as if it is still using the characters asci values to determine whether a given part of the string should be retained, via the CODE function. A lot there to digest and learn. Some cool functions.

    Norie, thanks. The IsNumeric function worked as hoped where the IsNumber function failed. A version of the code I ended up using:

    Code:
    Sub Letters_Out()
        Dim i As Integer
        Dim Original As String
        Dim NumOnly As String
        
        Original = Selection
        NumOnly = ""
        For i = 1 To Len(Original)
            If IsNumeric(Mid(Original, i, 1)) Then
                NumOnly = NumOnly & Mid(Original, i, 1)
            End If
        Next 'i
        Selection = NumOnly
    End Sub

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