trim numbers out of a string in excel vba

I have any number of different strings that contain any length of characters. i want to go through and shorten them to 12 digits long (that is the easy part) however if after being shortend any of the items contain numbers on the end of them like "HALLMAKR 12'
I need it to remove the numbers off of the string and the extra space at the end. and make it  "HALLMAKR".
This is ok though: "MAGIC KING"  (spaces are fine unless they are before numbers at the end)

Thanks for the help
tchristie33Asked:
Who is Participating?
 
tchristie33Author Commented:
i also found this to be a little shorter

        If IsNumeric(Right(Item, 1)) = True Or Right(Item, 1) = " " Then
            Do: Item.Value Left(Item, Len(Item) - 1)
            Loop Until IsNumeric(Right(Item, 1)) = True And Right(Item, 1) = " "
        End If
0
 
RobSampsonCommented:
Hi, you can use something like this.

Regards,

Rob.
strString = "HALLMAKR 12"
strNewString = ""
For intChr = 1 To Len(strString)
   strChr = Mid(strString, intChr, 1)
   If IsNumeric(strChr) = False Then strNewString = strNewString & strChr
Next
strNewString = Trim(strNewString)
MsgBox strString & VbCrLf & "has become" & VbCrLf & strNewString

Open in new window

0
 
tchristie33Author Commented:
is there a character in excel that represents any number? such as * or ? works for any character?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
spattewarCommented:
what is the number are present in the middle of the string? Do you want to remove the numbers completely from the string without looking at their position?
0
 
RobSampsonCommented:
Hi, your above code won't get rid of numbers from the middle of a string, but if that's fine, that will work too.

Regards.

Rob.
0
 
tchristie33Author Commented:
i only want it to remove them at the end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.