removing non-numeric characters from string

Night_Rain

Board Regular
Joined
Jan 1, 2005
Messages
181
Any suggestions on how to pull non-numeric values out of a variable length string, leaving only the numbers, order intact.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
There is a VBA equivalent of ISNUMBER, it's called IsNumeric.

Perhaps it would help if you could post sample data.
 
Upvote 0
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
 
Upvote 0
Hi Night_Rain:

Following is a formula based solution in which I have used the MCONCAT function from the MoreFunc Add-in ...
Book1
ABCD
1
2a123bcd123
3pkj3a2l5325
49asd52nk952
5abcdef25682568
6539pq1453914
7123456123456
8abcdefg 
9123abc123
10abc123123
Sheet1


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),"")),"")
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top