There is an old Proverb : If you can't bring Mohamed to the mountain, bring
the mountain to Mohamed. This is a perfect case of the Proverb. Ths simple
solution is to replace the CSV commas with some other character like a
semicolon (any character not in you data). then seperate the string using
Split but seperate the semicolons.

Sub test()

'Input data
a = "Lastname"",""FirstName"",""Sales,Marketing"",""mo re data"
'replace comma delimiters with semicolon
'leave other commas in place
'for examble "a","b,c","d" with "a";"b,c";"d"
b = Replace(a, """,""", """;""")
c = Split(b, ";")
'Remove the double quotes
For i = LBound(c) To UBound(c)
c(i) = Replace(c(i), Chr(34), "")
Next i

End Sub


"mikebres" wrote:

I have large text files that I need to import. I only needed some of the data
from them so I select those lines from the text file and import these. I've
got that part working, but I ran into an issue with some of the columns
having the comma, which I'm using as the delimeter, inside them. Here is an
example:

"Lastname","FirstName","Sales,Marketing","more data...

I had been using the substitute function to remove the quotes before I split
the line with the Split function. However, when I came across this I had to
rethink my solution. Well my thinking hasn't worked too well.

So how can I split these lines, remove the quotes, and keep the comma as
part of the result?

Thanks
Mike