How can I strip non-alpa and non-numeric characters from a variable? 3

[Apollo6]

I am writing a Word macro that moves a selection into a variable.

strTrial_ID = "GI34"

#1. How can I move the alpha characters into one variable and move the numeric characters into another variable. Neither the alpha or numeric lengths are constant.

Thanks in advance for suggestions.

 

[RobBroekhuis]

The code below assumes your string will always have alpha characters, followed by numeric characters:

dim s_alpha as string, s_num as string, p as integer
s_alpha=""
s_num=""
p=1
do while mid(strTrial_ID,p,1)>=&quot;A&quot; and mid(strTrial_ID,p,1)<=&quot;Z&quot;
p=p+1
loop
s_alpha=left(strTrial_ID,p-1)
s_num=mid(strTrial_ID,p)


Rob

 

[rmikesmith]

Apollo6,

The following procedure demonstrates how to do what you want. Just modify it accordingly.

Sub SeparateAlphaNumerics()
Dim Alpha As String
Dim Num As String
Dim InpStr As String
Dim i As Integer
Dim OneChar As String * 1

  InpStr = "ABC123"
  For i = 1 To Len(InpStr)
    OneChar = Mid$(InpStr, i, 1)
    If (OneChar Like "[A-Z]") Or (OneChar Like "[a-z]") Then
      Alpha = Alpha & OneChar
    ElseIf (OneChar Like "[0-9]") Then
      Num = Num & OneChar
    End If
  Next i
  MsgBox "Alpha = " & Alpha & vbCrLf & vbCrLf & "Numeric = " & Num
  
End Sub

Note: The &quot;numeric&quot; variable is still a String. If you need this to be manipulated as a number, use one of the converison functions, such as CSng of CDbl.

Regards,
Mike

 

[rmikesmith]

Rob,

Your fingers are more nimble than mine


Mike

 

[RobBroekhuis]

Our two solutions reflect our different assumptions given the opening posting. So now Apollo has two approaches to choose from. What luxury!
Rob

 

[Apollo6]

I tried with both and both work great!!!

However, after thinking about it, I can't confirm that alpha first will always be the case. That being said, I think I'll have to go with Mike's solution. I should have mentioned that inconstancy in my original post.

Stars for both of you!

 

[jdttek]

Apollo6 (& Mike / Rob - if you are still on this thread!!)
Pls see questions below.

1st of all, thanks much for pointing me to this thread. Like Apollo6, I found Mike's solution to work best, because could not always confirm alpha as start.

FYI: I needed to extract string from cell and place Alpha / Numeric in adjacent cells on worksheet, which I accomplished with following mods to Mike's code:

Sub SeparateAlphaNumerics()
Dim Alpha As String
Dim Num As String
Dim InpStr As String
Dim i As Integer
Dim OneChar As String * 1

InpStr = Range(&quot;C1&quot
For i = 1 To Len(InpStr)
OneChar = Mid$(InpStr, i, 1)
If (OneChar Like &quot;[A-Z]&quot Or (OneChar Like &quot;[a-z]&quot Then
Alpha = Alpha & OneChar
ElseIf (OneChar Like &quot;[0-9]&quot Then
Num = Num & OneChar
End If
Next i
Range(&quot;A1&quot.Value = Num
Range(&quot;B1&quot.Value = Alpha

End Sub

QUESTIONS:
1. I have a whole column of alpha/numerics that I need to execute this code on, placing the extracted number and text to the left of the Alpha/numeric string in each case, as per above. Any ideas how to go down the column (starting at 1st cell below header) and perform this, replacing InpStr = &quot;??&quot; and Alph / Num = &quot;??&quot; with appropriate cells in column?

2. I find that the numeric I need to extract is always last 4-5 characters in the string. Any way to just extract those and not other numbers if they appear earlier in the string.

Thanks for your suggestions on how to accomplish this!!

JDTTEK

 

[RobBroekhuis]

The modified procedure below addresses both of your questions, I think. Take a look and see if it works for you.

Sub SeparateAlphaNumerics()
Dim Alpha As String
Dim Num As String
Dim InpStr As String
Dim i As Integer
Dim OneChar As String * 1
dim cell as range
const StartCharNum=4

for each cell in range(&quot;C2&quot;,range(&quot;C2&quot.end(xldown))
InpStr = cell
alpha=left(inpstr,StartCharNum-1)
For i = StartCharNum To Len(InpStr)
OneChar = Mid$(InpStr, i, 1)
If (OneChar Like &quot;[A-Z]&quot Or (OneChar Like &quot;[a-z]&quot Then
Alpha = Alpha & OneChar
ElseIf (OneChar Like &quot;[0-9]&quot Then
Num = Num & OneChar
End If
Next i
cell.offset(0,-2) = Num
cell.offset(0,-1) = Alpha
next cell
End Sub
Rob

 

[jdttek]

Rob

Thanks !! Looked like it wld work, but what I got was:
A B C
4444 dddd dddd4444
44445555 123hhh 123 hhh 5555
444455556666 dddd dddd6666

Number on each row adds to previous row!! Any suggestions on how to fix?

Also, What is the logic to not have &quot;123&quot; appear in number col in 2nd row? It works, But I just can't figure out logic behind it!!

JDTTEK

 

[RobBroekhuis]

Ah yes. The pesky issue of resetting variables at the start of the loop. Try this:

Sub SeparateAlphaNumerics()
Dim Alpha As String
Dim Num As String
Dim InpStr As String
Dim i As Integer
Dim OneChar As String * 1
dim cell as range
const StartCharNum=4

for each cell in range(&quot;C2&quot;,range(&quot;C2&quot.end(xldown))
InpStr = cell
alpha=left(inpstr,StartCharNum-1)
num=&quot;&quot;
For i = StartCharNum To Len(InpStr)
OneChar = Mid$(InpStr, i, 1)
If ucase(OneChar) Like &quot;[A-Z]&quot; Then
Alpha = Alpha & OneChar
ElseIf (OneChar Like &quot;[0-9]&quot Then
Num = Num & OneChar
End If
Next i
cell.offset(0,-2) = Num
cell.offset(0,-1) = Alpha
next cell
End Sub

As for the 123 in the first few characters - note that the loop counter i starts at the value of StartCharNum (in this case, 4). So the first three characters in the string are simply skipped.

Rob

 

[jdttek]

Is there any way to just extract the last 5 numbers in the string and ignore any other numbers in the string.

 

[RobBroekhuis]

Be very specific - do you want

A) the last five numeric characters in the string, regardless of where they appear in the string, or
B) the numeric characters appearing in the last five characters of the string?

The answer, of course, is different depending on your problem definition.
Rob

 

[jdttek]

The last 5 numneric characters in the string.

abcd 123 efg 12345

Preference One: return abcd 123 efg in alpha field and 12345 in numeric field.


If that is not possible, I guess I could go thru two extractions: one for Alpha only &quot;=LEFT((cell),LEN(cell)-5))&quot;?? and then another for the numeric

Thanks

JDTTEK

 

[RobBroekhuis]

Your example does not clarify between the two cases. What do you want to have returned in the numeric field for:

abcd123efg456

456, or 23456?
Rob

 

[jdttek]

Sorry I was not clear.
Objective is to extract last characters in string only if they are numeric. Last would always be numeric.
Actually, as precaution, it may be best to extract all numbers at END of string, just in case of changes in future - i.e. - 4 or 6 numbers at end)
In your example: 456 and not 23456.
OR: abc222dfg1234 = 1234
Reason I can't just do a simple extract of last five characters (and assume they will always be numeric) is that these records are mixed in with records that do not have numbers at the end.

Actual situaltion is extract of text file that has name+ Acct# on one line and acct address on next line. I am trying to extract just the acct name and number with this procedure by moving the accopunt number to its own column and then filtering by number (Address will be blank since no number will be extacted. Fortunately there is no zip in address field!!)

Hope this helps explain better. Thanks much for your help.

JDTTEK

 

[RobBroekhuis]

The following function extracts the final numeric string from a string value:

Function EndNumbers(ByVal s As String) As String
Dim p As Integer
p = Len(s)
Do While IsNumeric(Mid(s, p, 1))
p = p - 1
Loop
If p < Len(s) Then EndNumbers = Mid(s, p + 1) Else EndNumbers = &quot;&quot;
End Function
Rob

 

[jdttek]

PERFECT !!!
Simpler solution but I learned alot from the earlier code as well. Was not that familiar with utility of Mid function.

Thankyou so much. You get a STAR!!

JDTTEK