How sepeparate n number of strings from a m cells and writhe the result in a single column

[tahi.laci]

The data I work with is strings like this in each cell in a column:
A1="apple, pear"
A2="lemon"
A3="kiwi, melon, apple"

I want to get:
D1="apple"
D2="pear"
D3="lemon"
D4="kiwi"
D5="melon"
D6="apple"

There's always a uniform separator between each record in a cell (Here it's comma, but it could be anything else). There is at least one entry in each cell.

P.S.: You can use this function to count the number of commas in a cell: =LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

[ragulduy]

Can't think how to get this with formulas but it would be fairly simple with a macro:

Please Login or Register  to view this content.

[Leon V (AW)]

you will need a macro

[XOR LX]

Or, if for whatever reason you insist on a formula-based solution, this array** formula, in D1 and copied down until you start to get blanks for the results, should work:

=IFERROR(TRIM(MID(SUBSTITUTE(INDEX($A$1:$A$3,MATCH(TRUE,MMULT(--(ROW($A$1:$A$3)>=TRANSPOSE(ROW($A$1:$A$3))),1+LEN($A$1:$A$3)-LEN(SUBSTITUTE($A$1:$A$3,",","")))>=ROWS($1:1),0)),",",REPT(" ",189)),189*IFERROR(ROWS($1:1)-(1+LOOKUP(ROWS($1:1)-10^-3,MMULT(--(ROW($A$1:$A$3)>=TRANSPOSE(ROW($A$1:$A$3))),1+LEN($A$1:$A$3)-LEN(SUBSTITUTE($A$1:$A$3,",",""))))),ROWS($1:1)-1)+1,189)),"")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[tahi.laci]

To XORLX: Thanks a lot. It did work, although I find it a bit hard to understand such long functions, (I usually use many helping columns with small functions) Could you give me an idea why it works?

[tahi.laci]

To yudlugar: It does work. Just so that I learn a few things could you clarify a few things?

How does the Split() statement work in the fourth row? What does it do?
What does the Ubound() statement do?
Why is the penultimate row necessary? (The one which starts with "If Range("D1") =""...)

[XOR LX]

An 'idea' of why such a formula works is not something easy to give. Going through it step-by-step using the in-built Evaluate Formula feature may help you understand what is happening. A few general comments on top of that:

This part of the formula:

INDEX($A$1:$A$3,MATCH(TRUE,MMULT(--(ROW($A$1:$A$3)>=TRANSPOSE(ROW($A$1:$A$3))),1+LEN($A$1:$A$3)-LEN(SUBSTITUTE($A$1:$A$3,",","")))>=ROWS($1:1),0))

is determining which of the three cells in question (A1, A2 or A3) is to form the basis for the extraction in each case. As the formula is copied down, this part:

ROWS($1:1)

becomes, successively, ROWS($1:2) (=2), ROWS($1:3) (=3), ROWS($1:4) (=4), etc., i.e. increments by 1 each time. This part:

1+LEN($A$1:$A$3)-LEN(SUBSTITUTE($A$1:$A$3,",",""))

when array-entered, returns an array of the number of words in each of the strings in A1, A2 and A3, in your example:

{2;1;3}

The MMULT construction then produces a cumulative version of this array, i.e.:

{2;3;6}

and finally the MATCH formula asks for the first occurrence in this group which is greater than or equal to the current relative row position of the cell containing the formula, e.g. in B4 this part of the formula would equate to:

MATCH(TRUE,{2;3;6}>=4,0)

which is equal to 3, and so, when passed to the INDEX function, in this case (i.e. for B4) gives:

INDEX($A$1:$A$3,3)

and so we know we will be dealing with the string in A3 in this particular case.

The rest of the formula is not so straightforward to explain (although contains many of the elements just outlined above), but boils down to a rather complicated way of then extracting the required string from the relevant cell in each case.

Hope that helps.

Regards

[ragulduy]

Split will take a string and split it into an array of elements delimited by the second arguement. In this case it will return each word using "," as a delimiter.

Ubound returns the number of elements in the array, so it allows us to loop through each element in an array of unknown length.

The last line is because Range("D" & rows.count).end(xlup).offset(1,0) will pick the first empty cell in column D but will skip D1 and start in D2, so it is to move everything up one.