We help IT Professionals succeed at work.

trim numbers out of a string in excel vba

Medium Priority
863 Views
Last Modified: 2012-06-21
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
is there a character in excel that represents any number? such as * or ? works for any character?
CERTIFIED EXPERT

Commented:
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?
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
i only want it to remove them at the end
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.