Combine text in Excel

If you need to combine text in Excel which are distributed over many cells, there are a couple of ways you can do it.

Fill Justify to combine text in many cells into one cell

In another blog post we show how to break up a cell with lots of text into smaller pieces in many cells (instead of within the cell) using the FILL JUSTIFY tool. It is useful to note that this can also work the other way.

So if we have something like below and we want the text in rows 4,5,6 and 7 to be combined into one cells we could:

  • Do it manually 🙁
  • Use Fill Justify

To use FILL Justify you would:

combine text in excel
  1. The first thing you need to do is make the column width wide enough to fit in the whole sentence.
  2. Then highlight the cells you want to combine
  3. Click on the HOME tab and FILL then Justify
Use a button to join text

The end result will be

combine text in excel 3

The key is to make sure the column is wide enough. Once you have it in this format you can change the column size again.

CONCATENATE function to combine text in many cells into one cell (Excel 2013 and earlier)

Using Fill Justify is fine for once off situations as a tool. If you want Excel to do it every time then using a formula is best. In Excel 2013 and before, the function to use is CONCATENATE.

combine text in excel 4

The most difficult thing about this function is how to spell it. As shown above you need to specify each cell that must be joined in sequence and to make it readable we have included spaces between the text cells we want to join.

To see a video clip on how to do it watch the CONCATENATE video tutorial

The problem with CONCATENATE is that you need to specify exactly which cells should be joined. What can you do if you have a variable number of cells that need to be joined?

TEXTJOIN  function to combine text in many cells into one cell (Excel 2016 and on)

In Excel 2016 and above, Microsoft have addressed this issue. There is a new function called TEXTJOIN that does what is says.

Use a formula to join text in excel

As shown above, the TEXTJOIN function requires that you specify

  1. The delimiter(what should Excel use to separate the cells- in the CONCATENATE example we manually needed to type in a space)
  2. How to treat empty cells (important considering the next step as the area you highlight may not always contain text)
  3. The cells that contain the text, not entered one by one but rather by highlighting a range.

For more about dealing with transforming your data to what you want to see look at our Data Cleanup Course.

Write paragraphs in Excel

Most used functions in MS Excel

How to resize all columns to fit text in Excel at once

Limit text length in Excel for ID and passport numbers