-
Solved: Stripping numbers from alphanumeric strings
Hi
I am attempting to write a small VBA script to cycle through a large number of rows in a column, identify those in boldand rename the cell to the value that it holds (i.e. cell A1 holds the value ABC, the cell name becomes ABC).
The issue I am having is that a few of the cells hold values beginning with numbers (i.e. 10ABC) which can obviously not be used as a valid cell name. My solution is to strip the numbers from the beginning and append the index number I am using to cycle through the rows to the end (to retain uniqueness)
I have trawled the internet but all the solutions I have found seem to rely on knowing the amount of numbers present etc. I am experimenting with using InStr and Replace functions but am struggling.
Is there a way of defining a class of characters to find in InStr or to identify in Replace similar to Perl, i.e. [0-9].
Thanks
-
Sorry, forgot to include the sort of thing Ive tried already, its along the lines of
[VBA]
If InStr(cellName, "-0-1-2-3-4-5-6-7-8-9-") Then
cellName = Replace(cellName, "-0-1-2-3-4-5-6-7-8-9-", "")
cellName = cellName & index
End If
[/VBA]
The -0-1-2-3-4-5-6-7-8-9- is from a page I found suggesting this is the way to define a set of values but it doesn't seem to be working
-
I have been able to solve the issue of getting into the first IF statement by using
[VBA]If InStr(cellName, ISNUM) Then[/VBA]
The main issue is now a way of getting the Replace function to replace any numbers.
-
try adapting this (from http://www.vbforums.com/showthread.php?t=540953):
[VBA]Sub blah()
For Each cll In Selection.Cells
MsgBox remleaddig(cll.Value)
Next cll
End Sub
Function remleaddig(str As String)
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "^\d+"
str = regEx.Replace(str, "")
remleaddig = Trim(str)
End Function
[/VBA]
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
Solved!
Thats great, thanks for your help. Bit of tweaking to include a regex.test to only append index if there are leading digits and it works perfectly!
[VBA]
Function remleaddig(str As String, ind As Long)
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "^\d+"
If regEx.test(str) Then
str = regEx.Replace(str, "")
str = str & ind
End If
remleaddig = Trim(str)
End Function
[/VBA]
Thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules