See my post here http://www.mrexcel.com/board2/viewtopic.php?t=109308
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....
Any suggestions on how to pull non-numeric values out of a variable length string, leaving only the numbers, order intact.
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
Thank you very much, HOTPEPPER.
Since I was only interested in numbers, and I would be embedding this within an existing event procedure, I re-wrote as follows:
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.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
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.
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.
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:
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, "")
Set Reg = Nothing
Application.ScreenUpdating = True
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 a123bcd 123 3 pkj3a2l5 325 4 9asd52nk 952 5 abcdef2568 2568 6 539pq14 53914 7 123456 123456 8 abcdefg
9 123abc 123 10 abc123 123
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
array formula in cell C2 is ...
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
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