regex—replace numbers (excel97+)

Topic

New Reply

Hi all!
I have a long column containg letters and numbers eg:
123stefd456 abc3467
hjjk4556 ghhk4gr57
I need a vba script which will filter out all the numbers and just leave letters in each cell.
“instr” does not take regex “[1-9]'” format as wildcard –how do I use “new regex” in vba as defined in vbscript ??? -or is there a direct way of doing with vba?
thanx
Smbs

Reply | Quote

Replies

The array formula wizards will probably come up with a method involving formulas only, no code. In the meantime, here is a procedure using standard Excel VBA that strips the digits 0 … 9 from the selected cells. It leaves everything else; if you want to leave only alphabetic characters, you can adapt the If … Then statement accordingly.

Sub RemoveDigits()
Dim oCell As Range
Dim strOldVal As String
Dim strNewVal As String
Dim strChar As String
Dim i As Integer

For Each oCell In Selection
‘ Get current value
strOldVal = oCell.Value
‘ New value starts out empty
strNewVal = “”
‘ Loop through characters
For i = 1 To Len(strOldVal)
‘ Get i-th character
strChar = Mid(strOldVal, i, 1)
‘ Test if not numeric
If Not IsNumeric(strChar) Then
‘ It’s not a digit, so append to new value
strNewVal = strNewVal & strChar
End If
Next i
‘ Set new value
oCell.Value = strNewVal
Next oCell
End Sub

Reply | Quote

Thanx looks like a great work around—will give it a try
Just on looking at it for the first time I am not sure what happens if there are spaces{blanks) between letters or numbers in same cell-my example in my original posting is the contents of 2 cells one below the other!
Thanx
Smbs

Reply | Quote

Here is a variant that leaves only regular upper and lower case letters and removes everything else. If you have letters with diacritics (accents) such as

Reply | Quote

Smbs,

The following code works If all the extras characters are lower case and consist only of a to z.
It can be modified to remove other characters as necessary…

Sub RemoveAlphasFromRange()
Dim ColRng As Range
Dim N As Long

Set ColRng = Range(“C1:C1000”) ‘ Do not us an entire column
For N = 97 To 122 ‘ Character codes for lower case a to z
ColRng.Value = Application.Substitute(ColRng, Chr$(N), vbNullString)
Next ‘N
Set ColRng = Nothing
End Sub

Regards,

Jim Cone
San Francisco, CA
jim.coneXXX@rcn.comXXX

Reply | Quote

Hi Jim,

Your code removes alphabetic characters, but smbs wanted to keep those, and remove everything else.

Reply | Quote

Hans,

You are right and you know the answer, but for others…

Replace…
For N = 97 To 122 ‘removes lower case a to z

with…
For N = 48 to 57’ removes 0 to 9

Also, adding this additional line, before the loop removes all spaces…
ColRng.Value = Application.Substitute(ColRng, Chr$(32, vbNullString)

Regards,
Jim Cone

Reply | Quote

Thanx to all–really learnt a lot!!
Smbs

Reply | Quote

There are no VBA functions that use regular expressions. Therefore, you would need to do something like the code below which will do what you asked for all string values in column A:

Public Sub RemoveDigits()
Dim oCell As Range
Dim lngLastRow As Long
Dim strOld As String, strChar As String, strNew As String
    lngLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    For Each oCell In Range(Worksheets("Sheet1").Range("A1"), Worksheets("Sheet1").Range("A1").Offset(lngLastRow))
        If (Not oCell.HasFormula) And (Not IsNumeric(oCell.Value)) And (Not oCell.Value = "") Then
            strOld = oCell.Value
            strNew = ""
            Do While Len(strOld) > 0
                strChar = Left(strOld, 1)
                strOld = Right(strOld, Len(strOld) - 1)
                If Not IsNumeric(strChar) Then
                    strNew = strNew & strChar
                End If
            Loop
            oCell.Value = strNew
        End If
    Next oCell
End Sub

Reply | Quote