In Microsoft Excel 2003, I use the following formula [in cell A2 in a sheet named (Allocation)] to refer to several cells from a sheet named (02Mis)
='02Mis'!$A$4&"/"&'02Mis'!$A$11&"/"&'02Mis'!$A$10&"/"&'02Mis'!$A$9&"/"&'02Mis'!$A$13
as shown in the following images.
My question is: how to make each entry appears in a separate line as shown in cell C2 in the top image instead of separated by / as in cell A2.
I mean how to make the separator a new line instead of /
This can be achieved in quite a few clicks if you follow these steps.
You have a worksheet named "02Mis" just like this sample:
And another worksheet in the same workbook named "Allocation" where you extract the required values at A2 using this:
='02Mis'!$A$4&"/"&'02Mis'!$A$11&"/"&'02Mis'!$A$10&"/"&'02Mis'!$A$9&"/"&'02Mis'!$A$13
which results in a long string of text with / in between.
To get rid of this and to get a new line for each of the / in C2, paste this in C2 and hit 'enter':
='02Mis'!$A$4&CHAR(10)&'02Mis'!$A$11&CHAR(10)&'02Mis'!$A$10&CHAR(10)&'02Mis'!$A$9&CHAR(10)&'02Mis'!$A$13
which will fetch you something like this it is not yet the result you desired for:
Now, after entering the formula in C2, select it giving a left click, click on wrap text and there you will have it:
I hope this helps :)
The trick is to use char(10) for line return, in combination with clicking on Wrap Text.
Note that you could simplify the formula with TEXTJOIN, like this formula:
=TEXTJOIN(CHAR(10),TRUE,M4:M10)
This is what I got:
If your cells are not sequential, you can separate sequences of cells with ,:
