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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Splitting a multiline single cell into separate cells?
0
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: 4889) Sep 14, '17 at 8:42 pm
example attached.
Ktoast63 Sep 14, '17 at 9:16 pm
Answers
0
These are the formulas which extract the values.
- =LEFT($A1,FIND(CHAR(10),$A1)-1)
Extracts the first value. - =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)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.