Thanks all,

Bob, Yes, it is a csv. No I can't open the entire file in Exce as it is too
large, 507k lines of text.

Joel, I think I understand yuour approach, but wouldn't that also replace
the comma I don't want replaced?

Mike

"Bob Bridges" wrote:

This isn't wrong, but isn't it premature? Before I advise a workaround like
that, I want to make sure our guy isn't missing a more obvious solution.
This is obviously a file in CSV format, yet he didn't SAY "csv" and he
apparently isn't reading it into Excel as a CSV either, because if he were
the quotes would no longer be in the data. So mikebres, have you the option
of just opening this file in Excel as a csv? because if you do, you'll find
the data already broken down into cells with the quote marks removed for you
and the internal commas preserved.

--- "Joel" wrote:
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?