trim numbers out of a string in excel vba

Posted on 2009-02-17
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
Question by:tchristie33
    LVL 65

    Assisted Solution

    Hi, you can use something like this.


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

    Open in new window


    Author Comment

    is there a character in excel that represents any number? such as * or ? works for any character?
    LVL 22

    Expert Comment

    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?

    Accepted Solution

    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
    LVL 65

    Expert Comment

    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.



    Author Comment

    i only want it to remove them at the end

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now