Regex - Split string at fixed delimiter but keep "Groups" with same delimiter together

[bakerman2]

In Range("A1") I have a string like One,Two,"Three,Four",Five,Six,"Seven,Eight",Nine,Ten,"Eleven,Twelve"

The string has to be split at the comma but the Groups marked by the double quotes should stay intact.

C1 to K1 should look like One Two "Three,Four" Five Six "Seven,Eight" Nine Ten "Eleven,Twelve"

The double quotes should also be included in the final result.

I have written a function that works well but I'm just curious if it's possible to do the same thing using Regex.

Thanks

[Rick Rothstein]

I cannot answer your direct question because I do not program with Regular Expressions, and while I know you said you wrote your own function to do this, I thought you might like to see the function I wrote for this many, many, many years ago now.

Please Login or Register  to view this content.

The above function has four arguments... the first is the text that you want to split apart and is, of course, required; the second, the text you want to use as the delimiter is optional and is defaulted to a comma, but which can be any text string composed of one or more characters; the third is a Boolean which allows you to specify how consecutive delimiters are to be treated, where the default is False meaning the each delimited field will be returned even if that delimited field is the empty string ("") and where True will make the function treat consecutive delimiters as if they were a single delimiter; and the fourth is an optional built-in data type (VbCompareMethod - actually, a restricted Long underneath it all) which, if the delimiter has alphabetic characters in it, allows you to make the split case sensitive or not... the default is vbBinaryCompare (0) meaning the text must match the case of the delimiter you pass in exactly, set it to vbTextCompare (1) if you want the split to be performed with regard for the letter casing of the delimiter. Oh, and I guess I should mention... this function, like the Split function which it is modeled after, returns a zero-based one-dimensional String array.

EDIT NOTE
===============================
To use the above function with data on your worksheet, you should create a second function which allows you to specify the element to return. The following function does this...

Please Login or Register  to view this content.

To implement these two functions on your work sheet, put this normally entered formula into cell C1 and copy it across for as many cells as you think you will ever have output for, then copy all of those down to the bottom of your data (or beyond if you want to prepare for additional data being added at a later time)...

Please Login or Register  to view this content.

An interesting side note and something I was not aware of before... when you let Excel create an array for use in another function, whether it's another Excel function or a UDF (user defined function), that array appears to always be a one-based array. Even though my QuotedString function returns a zero-based array, the array Excel passes into the QS function is always one-based. So, if you use the QuotedString function in a formula, the first element's index value will always be one... when you call the QuotedSplit function directly from another VBA function or subroutine, the index value for the first element will always be zero.

[Haluk]

The below UDF when entered as an array function, it works as needed;

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Code for UDF

Please Login or Register  to view this content.

[Bo_Ry]

Please try

Please Login or Register  to view this content.

C1
=SpitQ($A1,COLUMNS($C1:C1))

[alansidman]

If you are looking for an alternative, there is Power Query

Please Login or Register  to view this content.

[bakerman2]

To all, I sincerely thank you for your contributions.

@ Rick

This one definitely goes into my 'Things to keep' folder.

@ Haluk & Bo_Ry

Great job, spot on. Both functions work as expected.

This answers my initial question.

@ kris

We think a bit alike because this was the way I went with the function I wrote myself. Thanks

@ Alan

If I ever find the time to dive into Power Query I really should because this seems to be an awesome tool.

[kvsrinivasamurthy]

Thanks for feed back and rep.