Combine contents in a range of cells into a single cell, with no VBA nor formula.

Excel Tips - Combine texts into one cell 1   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.  

Excel Tips - Combine texts into one cell 2 As a result, we will see following in the formula bar.Excel Tips - Combine texts into one cell 3 2.  Remove the head =(“ and tail “} manually Excel Tips - Combine texts into one cell 4 Now what we need to do is to replace all “;” in-between Excel Tips - Combine texts into one cell 5 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.Excel Tips - Combine texts into one cell 6 Now you are ready to press ENTER.  🙂   Excel Tips - Combine texts into one cell 7

B) Using Fill –> Justify

  1. Select the range (A1:A17 in this example)
  2. Adjust the column width so that it is wide enough to hold the resulting stringExcel Tips - Combine texts into one cell 8
  3. Home Tab –> Fill –> JustifyExcel Tips - Combine texts into one cell 9

There you go!Excel Tips - Combine texts into one cell 10 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?

Advertisements
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

6 Responses to Combine contents in a range of cells into a single cell, with no VBA nor formula.

  1. flickwpsnippets says:

    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.

    Like

  2. Flick says:

    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 🙂

    Like

  3. MF says:

    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.

    Like

  4. Lukas says:

    Creating email distribution lists comes to mind. Awesome technique I hadn’t known about before. Thanks for sharing!

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s