Well, the first thing in your mind maybe using ampersand & or CONCATENATE to combine them.
- =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.
As a result, we will see following in the formula bar.
2. Remove the head =(“ and tail “} manually 
Now what we need to do is to replace all “;” in-between 
3. CTRL H to open the Find and Replace
- 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?
wmfexcel 
The Fill-Justify trick is excellent, thank you! Will have to learn more about this. Was thinking I’d have to use VBA or creating a custom function (as suggested here http://www.excelcampus.com/keyboard-shortcuts/concatenate-range-of-cells/) to do multiple rows. But the space delimiter is more than adequate for building a string to use in the Criteria field when querying an Access database.
LikeLike
The Fill-Justify trick is amazing! Going to have to learn more about this now. Was previously looking at solutions suggested here: http://www.excelcampus.com/keyboard-shortcuts/concatenate-range-of-cells/ which involved VBA or creating a custom function. Thanks 🙂
LikeLike
Glad you like it! 🙂
Cheers,
LikeLike
Edit to the post:
The limitation for this (2nd) 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.
LikeLike
Creating email distribution lists comes to mind. Awesome technique I hadn’t known about before. Thanks for sharing!
LikeLike
Hi Lukas,
Glad you like it. Thanks for sharing your thought.
Cheers,
LikeLike