Separate out comma delimited text string via formula

[spydey]

I have a text string that I need to separate out via formula. I know how to do it via the built in Delimiter, but I have to do this via formula. VBA is an option too ....
The sub-strings are delimited by a comma and a space, e.g. ", "
The individual sub-strings can range from 2 characters to 3 characters.
There is a total possible count of 6 different sub-strings and thus 5 comma/spaces.
However, the full text string can range from one substring (with no comma/space) up to the max 6 sub-strings (with a comma/space separating each one).
The order of the sub-strings varies, so any given substring could be either 3 characters or 2 characters. It is dynamic.
The primary text string populates into a single cell on sheet 2.

I need to take that text string, and break it out into 6 different cells, each one representing one of the sub-strings.
The first cell would contain the substring before the first comma/space.
The 2nd cell would contain the substring after the 1st comma/space and before the 2nd comma/space.
The 3rd cell would contain the substring after the 2nd comma/space and before the 3rd comma/space
.....
The 6th cell would contain the substring after the 5th comma/space
The sub-string cells will be horizontal & on sheet 1.

I can get the first substring with one formula, and the last substring with a different formula.
But due to the dynamic nature of the primary text string, I cannot figure out a single formula that will allow me to separate the substrings from the primary text string, based upon the comma/space delimiter, regardless of how many comma/space delimiters there are.

Do I need to have a different formula in each of the 6 substring cells, with each one slightly adjusted to only grab a certain section of the primary text string????

Any ideas? Thoughts? Suggestions?

Your assistance is much appreciated!!

-Spydey

 

[kweaver]

Try this (although I used sub-strings of longer lengths, it shouldn't matter).

Book2 (version 1).xlsb
	A	B	C	D	E	F	G
1	Strings	1	2	3	4	5	6
2	No delim	No delim
3	This one, has two strings	This one	has two strings
4	This example, has, three strings	This example	has	three strings
5	This, string, has, all, six, items	This	string	has	all	six	items
Skoot

Cell Formulas
Range	Formula
B2:G5	B2	=TRIM(MID(SUBSTITUTE($A2,", ",REPT(" ",LEN($A2))),(B$1-1)*LEN($A2)+1,LEN($A2)))

 

[spydey]

@kweaver

That appears to have worked!! Thank you so much for your assistance. I did change the (B$1-1) to the iteration number of the actual delimiter, i.e. 0, 1, 2 ... 5.

Thanks a million!

-Spydey