- =A1&” “&A2&” “&A3&” “……
- =CONCATENATE(A1,” “,A2,” “……) which is more or less time consuming.
Two ways to achieve this quickly
- Using a combination of tricks (=Range, F9, Find and Replace)
- Using Fill –> Justify
Let’s see how!
A) Using a combination of tricks
1. In any blank cell, input =A1:A21 Press F9 (do not press enter)
- Note: F9 is the shortcut key of “calculate now”. When pressing F9 inside the formula bar, it returns the result of a formula.
- Find what: “;”
- Replace with: (space) ‘note: you may replace with other delimiter e.g. comma, dash. Now you are ready to press ENTER. 🙂
B) Using Fill –> Justify
- Select the range (A1:A17 in this example)
- Adjust the column width so that it is wide enough to hold the resulting string
- Home Tab –> Fill –> Justify
There you go! Isn’t it magical? The limitation for this approach is that we cannot select the delimiter we want. By default it is “space”. Moreover, each cell can hold up to 255 characters only. If the resulting text string contains more than 255 characters, the extras go to second line.
To both approach, the result is static. If the source changes, you need to repeat the trick again. The Fill –> Justify is just another mysterious function in Excel. You have may seen it many times but still have no idea what it does. Let’s discuss it in the next post. Stay tuned! 🙂
Practical case for combining contents into a cell
To me, it’s mainly for writing commentary in Excel report. E.g. “The top 10 performing shops are………”. Most of the case, the top 10 are listed in a table and I am too lazy to type them again into a commentary. So what’s your case for combining texts in range of cells and what technique you use?