If I have a multiline single cell, with say 5 values, how can I split these out to 5 separate single cells? And can this be done to several multiline cells at once, without using text to columns?
example attached.
If I have a multiline single cell, with say 5 values, how can I split these out to 5 separate single cells? And can this be done to several multiline cells at once, without using text to columns?
example attached.
These are the formulas which extract the values.
Key to the formula is
FIND(CHAR(1),SUBSTITUTE($A1,CHAR(10),CHAR(1),COLUMN()-3)
This substitutes ther nth occurrence of CHAR(10) for Char(1) and returns the position of that character. CHAR(10) is the line feed character you used, COLUMN()-3 specifies n.
COLUMN() is a counter because it returns the number of the column it resides in, 4 for D, 5 for E, 6 for F etc. So, COLUMN()-3 equals 1 when in column D etc. I used it to count the nth line feed in A1.
I also used it to mark the locations in the formula which you may wish to change, for example, if you wish to deploy the formula in rows instead of columns, or in columns other than D:G. You can change "COLUMN()-3" for a hard number, but then you will have four (5) different formulas. My idea was that you would extract the values to columns and then transpose.