Split text records by "*" in variable amount of fields

[Chromium]

Hi to All,

I’ve a problem.. I need to split a field records with the following format:

Aaaaaaaa*aaa bbb nnnn* aaaa bbbb 123 cc*bbbbb … etc etc

By “*”, to obtain as result:

Field1 Field2 Field3
Aaaaaaaa aaa bbb nnnn aaaa bbbb 123 cc

The problem are two:

1) I don’t know how to do that split;
2) I don’t know how many field will be necessary, (different records have different “*” separator).

I can also make “n” field in a query with a “research” function, that result a string between a “Like function with an identifier string” and the “*” next to the like result. But I don’t know how to make it too…



Someone can help?

 

[raskew]

Hi -

You should be able to adapt this:

Public Sub quickparse2(ByVal ptext As String, pDelim As String)
'*******************************************
'Purpose:   Extracts words/expressions from
'           a string based on a user-provided
'           separator
'Coded by:  raskew
'Input:     x = "Aaaaaaaa*aaa bbb nnnn* aaaa bbbb 123 cc*bbbbb"
'           Call quickparse(x, "*")
'Output     Aaaaaaaa
'           aaa bbb nnnn
'           aaaa bbbb 123 cc
'           bbbbb
'*******************************************

Dim strHold  As String
Dim texthold As String
Dim textsay  As String
Dim n        As Integer

    texthold = Trim(ptext)
    n = Len(ptext)
    Do While InStr(texthold, pDelim) > Len(pDelim)
       textsay = Left(texthold, InStr(texthold, pDelim) - 1)
       'add code to do something
       Debug.Print textsay
       strHold = strHold & textsay & vbCrLf
       texthold = Trim(Mid(texthold, InStr(texthold, pDelim) + Len(pDelim)))
       n = Len(texthold)
    Loop
End Sub

HTH - Bob

 

[Chromium]

Hi -

You should be able to adapt this:

Public Sub quickparse2(ByVal ptext As String, pDelim As String)
'*******************************************
'Purpose:   Extracts words/expressions from
'           a string based on a user-provided
'           separator
'Coded by:  raskew
'Input:     x = "Aaaaaaaa*aaa bbb nnnn* aaaa bbbb 123 cc*bbbbb"
'           Call quickparse(x, "*")
'Output     Aaaaaaaa
'           aaa bbb nnnn
'           aaaa bbbb 123 cc
'           bbbbb
'*******************************************
 
Dim strHold  As String
Dim texthold As String
Dim textsay  As String
Dim n        As Integer
 
    texthold = Trim(ptext)
    n = Len(ptext)
    Do While InStr(texthold, pDelim) > Len(pDelim)
       textsay = Left(texthold, InStr(texthold, pDelim) - 1)
       'add code to do something
       Debug.Print textsay
       strHold = strHold & textsay & vbCrLf
       texthold = Trim(Mid(texthold, InStr(texthold, pDelim) + Len(pDelim)))
       n = Len(texthold)
    Loop
End Sub

HTH - Bob

I'll try!

Since now, I found a way with a triple function. It works... but less cleaner.

 

[gemma-the-husky]

or just use split function
eg

dim myarray()
dim maxvalue as long

myarray = split(mystring,"*")
maxvalue = ubound(larray) 'note that its zero based
msgbox("last item: " & myarray(maxvalue))