Remove numbers from a string

  • Hi,

    Is it possible to remove the numbers from the end of string variable using a simple VB function?

    I tried:

    1. strString = Replace(strString, ISNUMBER, "")

    But it is clearly wrong, and even if it worked it would remove both 27's in the middle example below.

    i.e I want strings:
    "Number 23", "My 27 Isn't 27", "Test 44 5"

    To be converted to:
    "Number", "My 27 Isn't", "Test"

    My full code is:

    1. lngLastC = .Range("C65536").End(xlUp).Row
    2. Dim rn As Integer
    3. rn = 1
    4. ReDim strName(intNumRunners)
    5. For Each rngRunners In .Range("C3:C" & lngLastC)
    6. strName(rn) = rngRunners.Value
    7. [B]strName(rn) = Replace(strName, IsNumber, "")[/B]
    8. strName(rn) = RTrim(strName(rn))
    9. rn = rn + 1
    10. Next rngRunners

    (where the bold line is where I need to trim the string)

    If this isn't possible, could somebody please show me how I would loop backwards through a string removing any numbers until I reached a digit that wasn't a space or a number?



  • Re: Remove numbers from a string

    Try using this VBA function instead of the replace code

  • Re: Remove numbers from a string

    If I understand correctly, you only want numbers deleted from the end of the string, not from the middle. I think Will's function deletes all numbers. This version of Will's macro works from the end of the string, works its way backwards, and stops deleting when a non-number is encountered.