Parse string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I would like to parse a single string no longer than 10 characters into
separate text and numeric groups. There is no delimiter and data in the
field might look like this:

Input=>Output
"PM1J1S4" => "PM" "1" "J" "1" "S" "4"
"1MM12J3" => "1" "MM" "12" "J" "3"
"2WM1" => "2" "WM" "1"
"3NJ145S123" = "3" "NJ" "145" "S" "123"

Does anybody know a way to parse this, grouping by letters or numbers?

AK
 
You will need to write a VBA fuction that loops through the characters of
the string.

Test if the Asc() of the character is between vbkey0 and vbKey9. If so, it's
a digit.

Set a boolean variable to indicate if the last character was a digit. If
this character is the same result as the last one, concatenate the character
into your output string. When there is a change of state (digit verses
non-digit), output the string you built so far, and clear the string for the
next one.

You will need some VBA experience to achieve that.
 
I just happen to have some code that does this:


here is a sample:

Public Sub test8()

Dim c As Collection
Dim i As Integer

Set c = cTokens("PM1J1S4")

For i = 1 To c.Count
Debug.Print c(i)
Next i

End Sub

The above gives the output of
PM
1
J
1
S
4

So, lets change it for your needs:

Public Sub test9()

Dim c As Collection
Dim i As Integer
Dim s As String

Set c = cTokens("PM1J1S4")

For i = 1 To c.Count
If s <> "" Then s = s & " "
s = s & """" & c(i) & """"
Next i

Debug.Print s

End Sub

output is:

"PM" "1" "J" "1" "S" "4"


The parse routine follows my sig:

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)


Public Function cTokens(s As Variant) As Collection

Dim intPtr As Integer
Dim str As String
Dim c As String

Dim bolNum As Boolean
Dim bolLastNum As Boolean


Set cTokens = New Collection

If IsNull(s) Then Exit Function

c = Mid(s, 1, 1)
bolLastNum = c Like "[0-9]"

For intPtr = 1 To Len(s)
c = Mid(s, intPtr, 1)
bolNum = c Like "[0-9]"

If bolNum = bolLastNum Then
str = str & c
Else
cTokens.Add str
bolLastNum = bolNum
str = c
End If
Next intPtr
If str <> "" Then
cTokens.Add str
End If

End Function
 
Back
Top