Split text avoiding field separator within double quotes

[cgkmal]

I have a text containing strings separated by comma. But some strings have inside commas, then to differentiate, these
strings are surrounded by double quotes like below:

commas in red are those that separate each strings.

Please Login or Register  to view this content.

I'm trying with code below, but is not working since is splitting MyText in 9 strings, when I want to get only 7.

How can I split string by commas without consider the commas inside the double quotes?

Please Login or Register  to view this content.

The output desired should be

Please Login or Register  to view this content.

Thanks in advance for any help

[LJMetzger]

I occassionally use the following technique which requires additional work:
a. Replace the commas inside the double quotes with a character or sequence of characters that is known not to exist in your string (e.g. '|', or '@', or 'QQQ').
b. Run the split command
c. Use the 'replace()' function on each array member to restore the original commas.

Lewis

[p24leclerc]

Here is a macro that looks awful and all brute force but it works.

Please Login or Register  to view this content.

[Joakim N]

I see p24leclerc has given you a code that works. I will attach mine also.

Result is stored in asResult().

Please Login or Register  to view this content.

[Leith Ross]

Hello cgkmal,

This macro uses a more advanced method to parse the text . It uses Regular Expressions.

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

[Severduf]

Woow!

Hello all of you great contributors, I didn't expect such number of suggestions and solutions for my question.

I've tested all of them and work just fine. I'll learn from every solution shared

Many thanks for your help people.

Regards

[BS Singh]

Hi Jindon,

Your solution is very short and really impressive. Could you please elaborate the pattern you have used in provided solution.

Regards,

[p24leclerc]

JINDON's pattern = [^"",]+|""[^""]+""

the first part looks for the ordinary coma separated characters
[^"",] character class looking for anything being a comma or not a double quote ("")
+ means one or more times.

| an OR statement

The second part looks for the QUOTE enclosed characters
"" It has to start with a double quote
[^""]+ it can contains any characters but a double quote one or more times (+)
"" It ends with a double quote

^ is the exclusion symbol in RegEx
""[^""]+""

Leith Ross' pattern = ""[^""]+""|\w+(?=\,)

He starts looking for the Quote enclosed characters
""[^""]+"" It starts with a double quote, contains any characters but a double quote, one or more times and ends with a double quote.
| the OR symbol
\w+ looks for any characters one or more times
(?=\,) looks ahead for a comma to end those characters.

That is why Leith has to concatenate a comma at the end of the search string (in order to get the last word)

Hoping this helps you understand those patterns
If I'm wrong, I'm sure jindon or Leith Ross will correct me.
Regards

[BS Singh]

Thanks for your detailed explanation...

[cgkmal]

Hello guys again,

Thank you for the great solutions provided. I have an issue when string has empty values. I mean, where there are 2 or more commas consecutives.

for example if the string is like this:

Please Login or Register  to view this content.

When is splitted should be nine elements, but with the current code only appears 7. I've tried to add toyour regex "|\,\," but it seems is not correct.

May you help me again to fix this?

Thanks in advance

[jindon]

Please Login or Register  to view this content.

[cgkmal]

Hello jindon,

Thanks for your answer.

Is not working. The following string, str = a,"f,2",4,,5,,,,"k,4",,g has 11 fields but the regex is seeing only 6.

The current VBA code looks like this.

Please Login or Register  to view this content.

Output now
w(0) -> a
w(1) -> "f,2"
w(2) -> 4
w(3) -> 5
w(4) -> "k,4"
w(5) -> g

Desired output
w(0) -> a
w(1) -> "f,2"
w(2) -> 4
w(3) ->
w(4) -> 5
w(5) ->
w(6) ->
w(7) ->
w(8) -> "k,4"
w(9) ->
w(10) -> g

Thanks in advance for any help.

[jindon]

Please Login or Register  to view this content.

[cgkmal]

Hello jindon,

It almost work for all cases. Is not working if MyText = "f oui, 2",a,"f,2",4,,5,,,,"k,4",,g

Regarding that, I don't understand some things, maybe you have some time to explain me please.

What it is ".test()"? is a property of "CreateObject("VBScript.RegExp")"?
I understand that .Execute() is when you say the Pattern to work on. Work on (MyText). But what does it mean the (0)?

How it is that the pattern contains the Chr(2) and you execute the regex before to replace the commas by Chr(2) in MyText?

May you explain how the Regex you used work.

Thanks again

[jindon]

MyText = "f oui, 2",a,"f,2",4,,5,,,,"k,4",,g

Doesn't even compile.

I will leave this to someone else as I don't understand what you are trying to do.

[cgkmal]

Hello jindon,

Only try to split separated by commas, but without confusing the commas that are inside the fieds surrounded by double quotes.

The text doesn't compile since I showed as it is, to compile I need to write it inside the code like this:

Please Login or Register  to view this content.

The first field is "f oui, 2" but is being splitted in 2 fields in the output.

Thanks for all the help.

[jindon]

Please Login or Register  to view this content.

[cgkmal]

Thank you jindon,

With that regex works correctly now!

Regards