split - on "text","text,text","numeric"

peterdarazs used Ask the Experts™
Hi experts

I have some data that's difficult to comma split because field 1 sometimes also contains a comma and sometimes not. I tried Split (LINEDATA, "Chr(34)," )  but it just delivers the whole line

Is there a recognised vb6 split statement for data in this format? (below)

"21/07/2006","Bigg, Anne","1162","Private","18/06/1949","F","377998","GK","MI50",
"22/07/2006","Script Fee","23965","Private","11/01/1911","","377382","JC","MI42","5.45",

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try splitting with ",", for example....

    Split(LINEDATA, """,""")

Kamaraj SubramanianApplication Support Analyst
As my suggestion is

First replace the "." (double quote comma double quote ) with | (pipe symbol) then again just trim the first and last character double slash("), then your line looks like

After that you can use the split function to take the values
21/07/2006|Bigg, Anne|1162|Private|18/06/1949|F|377998|GK|MI50|
Private Sub Command1_Click()
Dim i As Long
Dim sString As String
Dim arrString() As String
sString = 21/07/2006|Bigg, Anne|1162|Private|18/06/1949|F|377998|GK|MI50|
arrString = Split(sString, "|")
For i = 0 To UBound(arrString)
    MsgBox arrString(i)
End Sub

Open in new window


Thanks folks, for both these useful suggestions.  I've assigned more or less equal points if that's ok with you. (one's for being easy, and the may be more accurate) Many thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial