If you need to combine text in Excel which are distributed over many cells, there are a couple of ways you can do it.
Table of contents
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:
- The first thing you need to do is make the column width wide enough to fit in the whole sentence.
- Then highlight the cells you want to combine
- Click on the HOME tab and FILL then Justify
The end result will be
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.
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.
As shown above, the TEXTJOIN function requires that you specify
- The delimiter(what should Excel use to separate the cells- in the CONCATENATE example we manually needed to type in a space)
- How to treat empty cells (important considering the next step as the area you highlight may not always contain text)
- 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.
Related
Most used functions in MS Excel