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

peterdarazs
peterdarazs used Ask the Experts™
on
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",

Thanks
Comment
Watch Question

Do more with

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

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

Wayne
Kamaraj SubramanianApplication Support Analyst
Commented:
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)
Next
End Sub

Open in new window

Author

Commented:
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
P

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