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?

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

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.

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))