Hall wrote...
One cell has the content
abc;def;ghi;...
I want a formula contruct to break the parts up as separated by the
semi-colon to their own cells. So
abc
def
ghi
...
...
vertically or horizontally.
Why formulas? You could use Data > Text to Columns, Delimited using
semicolon as the delimiter to parst the fields into separate cells
(columns) in the same row. That's clearly the easiest approach.
If you really must use formulas, if the string were in cell A1 and the
first field, abc, were to appear in cell C1, you could use the
following array formula in C1.
C1:
=MID($A$1,SMALL(IF(MID(";"&$A$1,seq,1)=";",seq),ROWS(C$1:C1)),
SMALL(IF(MID($A$1&";",seq,1)=";",seq),ROWS(C$1:C1))
-SMALL(IF(MID(";"&$A$1,seq,1)=";",seq),ROWS(C$1:C1)))
Fill C1 down as far as needed. Or, somewhat more efficient, but using a
different formula in the topmost result cell,
C1:
=LEFT(A1,FIND(";",A1)-1)
C2:
=REPLACE(LEFT($A$1,FIND(";",$A$1&";",
SUMPRODUCT(LEN(C$1:C1)+1)+1)-1),1,SUMPRODUCT(LEN(C$1:C1)+1),"")
Fill C2 down as needed. Note that these need not be entered as array
formulas.
