Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

trim numbers out of a string in excel vba

Posted on 2009-02-17
6
Medium Priority
?
846 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
0
Comment
Question by:tchristie33
  • 3
  • 2
6 Comments
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 2000 total points
ID: 23663693
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
 

Author Comment

by:tchristie33
ID: 23663732
is there a character in excel that represents any number? such as * or ? works for any character?
0
 
LVL 22

Expert Comment

by:spattewar
ID: 23663759
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
tchristie33 earned 0 total points
ID: 23663803
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 23664126
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
 

Author Comment

by:tchristie33
ID: 23664329
i only want it to remove them at the end
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

580 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