Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
Worth $20, Absolutely Free
CONCATENATE is a handy function to concatenate text from different cells. You need to refer to the cells which you want to combine. It’s simple and easy to apply.
But here is a bad news. In concatenate, it’s not possible to refer to an entire range.
You need to select all the cells of a range one by one. And, if you try to refer to an entire range, it will return the text from the first cell.
In this situation, you do need a method where you can refer to an entire range of cells to combine it in a single cell.
So today, in this post, you will learn 5 different methods to combine a range into a single cell.
Download this raw data file from here to follow along.
The best way to combine text from different cells into one cell is using transpose function with concatenate function.
Look at the below range of cell where you have a text but its every word is in a different cell and you want to get is all in one cell.
Here are the steps:
In this formula, you have used transpose function and a space in the concatenate function. When you convert that reference into hard values it returns an array.
In this array, you have text from each cell and a space between them. And, when you hit enter, it combines all of them.
Fill justify is one of the unused but most powerful tools in Excel. And, whenever you need to combine text from different cells you can use it.
The best thing is, you need a single click to merge text. Have look at below data and follow the steps.
This will merge text from all the cells into the first cell of the selection.
If you are using Excel 2016 (Office 365), there is a function called “TextJoin”. It can make it easy for you to combine text from different cells into a single cell.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
To combine below list of values you can use the formula:
Here you have used space as a delimiter, TRUE to ignore blank cells and entire range in a single argument. In the end, hit enter and you’ll get all the text in a single cell.
Power Query is a fantastic tool and I love it. You can also use it to combine text from a list in a single cell. Below are the steps.
Now, you have a new worksheet your workbook with all the text in a single cell.
The best thing about using Power Query is you don’t need to do this setup again and again. When you update old list with a new value you need to refresh your query and it will add that new value in the cell.
If you want to use a macro code to combine text from different cell then I have something for you. With this code, you can combine text in no time.
All you need to do, select the range of cells where you have text and run this code.
Dim rng As Range
Dim i As String
For Each rng In Selection
i = i & rng & " "
Range("B1").Value = Trim(i)
Make sure to specify your desired location in the code where you want to combine the text.
There may be the different situations for you where you need to concatenate a range of cell into a single cell. And, that’s why we have these different methods.
All methods are easy and quick, you need to select the right method as per your need. I must say that give a try to all the methods once.
And, once you try these methods I want you to tell me which one is your favorite and worked for you. Share your views with me in the comment section, I would love to hear from you.
Please, don’t forget to share these methods with your friends.