Extract numeric data from a string 1

[scriggs]

I have a list of references in the form

AA001
AA594
CWD002
CWD593

I need to seperate the starting alpha and remaining numeric elements so that I can use these elements for ordering, so that I get

AA001 AA 001
AA594 AA 594
CWD002 CWD 002
CWD593 CWD 593

Any suggestions please....

 

[PaulF]

if the value always starts Alpha then Numeric try

Dim x As integer
Dim strAlpha As string, strNumeric As string
For x = 1 to Len(YourField)
If IsNumeric(Mid(YourField,x,1)) Then
strAlpha = Left(YourField,x-1)
strNumeric = Mid(YourField,x)
Exit For
End If
Next x

PaulF

 

[hkaing79]

Will the Val() function work?

 

[fstrahberger]

Use the RegExp-object from WSH

    Dim strSource As String
    strSource = "CWD002"
    '-----------------------------------------------------
    ' Insert Microsoft VB Script Regular Expression 5.5
    ' using Extras -> AddOns into the project
    '-----------------------------------------------------
    Dim oReg As New RegExp
    oReg.Global = True
    oReg.IgnoreCase = True
    ' Search for any numer of leading alpha elements as Submatch(0)
    ' Search for any number of trailing numers as Submatch(1)
    oReg.Pattern = "(^[a-zA-Z]+)([0-9]+$)"
    
    Dim oMatch As Object
    For Each oMatch In oReg.Execute(strSource)
        Debug.Print "Alpha: " & oMatch.Submatches(0)
        Debug.Print "Numeric: " & oMatch.Submatches(1)
    Next

 

[tonyflavell]

Hi there, the following will separate your reference into "alpha and numeric" (both as strings - easy to convert and manipulate) into "Temp1" and "Temp2"


Sub Separate(MyNo As String)
Dim Temp1, Temp2, B
MyRef = Trim(MyNo):Temp1 = Temp2 = ""

For I = 1 To Len(MyRef)
B = Mid$(MyRef, I, 1)
Select Case B
Case "A" To "Z"
Temp1 = Temp1 & B
Case "1" To "9"
Temp2 = Temp2 & B
Case Else
MsgBox "Not a Valid Reference", , "Reference Error"
Exit Sub
End Select
Next I

End Sub

 

[scriggs]

Thanks all, I liked tonyflavells's code best and have given him a star!

I just added some variable def's and expanded the numeric selection to include 0 to 9:

Sub Separate(MyNo As String)

Dim Temp1, Temp2, B, i, MyRef

MyRef = Trim(MyNo): Temp1 = "": Temp2 = ""

For i = 1 To Len(MyRef)
B = Mid$(MyRef, i, 1)
Select Case B
Case "A" To "Z"
Temp1 = Temp1 & B
Case "0" To "9"
Temp2 = Temp2 & B
Case Else
MsgBox "Not a Valid Reference", , "Reference Error"
Exit Sub
End Select
Next i

End Sub

 

[tonyflavell]

Glad to have helped.

Your're right about Case 0 to 9 - stupid typo on my part.

 

[PHV]

So, you want XY12Z3T as a valid reference for XYZT and 123 ?

 

[scriggs]

Hi PHV

My desire was really to split the reference between first alpha section and second numeric section. The reference should only be AAAA00001 not a mix like you have suggested - I understand the limitation of the coding i currently have.

Once I have extracted the numeric section I will use the new fields AAAAA and 000001 to allow sorting in different orders, etc. which are just not available with the whole ref.