Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    regex---replace numbers (excel97+)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: regex---replace numbers (excel97+)

    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

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: regex---replace numbers (excel97+)

    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

  4. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: regex---replace numbers (excel97+)

    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

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: regex---replace numbers (excel97+)

    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:

    <pre>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
    </pre>

    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: regex---replace numbers (excel97+)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: regex---replace numbers (excel97+)

    Hi Jim,

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

  8. #8
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: regex---replace numbers (excel97+)

    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

  9. #9
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: regex---replace numbers (excel97+)

    Thanx to all--really learnt a lot!!
    Smbs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •