Splitting a multiline single cell into separate cells?

0

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.

Answer
Discuss

Discussion

In theory this could be done with the help of 5 formulas, each of which picks out one of the values. Whether or not that is possible in practice depends upon the constructions of the multi-value cell contents. Post a couple of example of collectively representative nature.
Variatus (rep: 1108) Sep 14, '17 at 8:42 pm
example attached.
Ktoast63 Sep 14, '17 at 9:16 pm
Add to Discussion

Answers

0

These are the formulas which extract the values.

  1. =LEFT($A1,FIND(CHAR(10),$A1)-1)
    Extracts the first value.
  2. =MID($A1,FIND(CHAR(1),SUBSTITUTE($A1,CHAR(10),CHAR(1),COLUMN()-3))+1,FIND(CHAR(1),SUBSTITUTE($A1,CHAR(10),CHAR(1),COLUMN()-2))-FIND(CHAR(1),SUBSTITUTE($A1,CHAR(10),CHAR(1),COLUMN()-3)))
    Paste in column D and copy to E:G

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.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login