Combining values with CONCATENATE is the best way, but with this function, 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 them in a single cell. Today in this post, I’d like to share with you 5 different ways to combine text from a range into a single cell.
[CONCATENATE + TRANSPOSE] to Combine Values
The best way to combine text from different cells into one cell is by using the transpose function with concatenating function.
Look at the below range of cells where you have a text, but every word is in a different cell and you want to get it all in one cell. Below are the steps you need to follow to combine values from this range of cells into one cell.
- In the B8 (edit the cell using F2), insert the following formula, and do not press enter.
- =CONCATENATE(TRANSPOSE(A1:A5)&” “)
- Now, select the entire inside portion of the concatenate function and press F9. It will convert it into an array.
- After that, remove the curly brackets from the start and the end of the array.
- In the end, hit enter.
How this formula works
In this formula, you have used TRANSPOSE and space in the CONCATENATE. When you convert that reference into hard values it returns an array. In this array, you have the text from each cell and a space between them and when you hit enter, it combines all of them.
Combine Text using the Fill Justify Option
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, that you need a single click to merge text. Have look at the below data and follow the steps.
- First of all, make sure to increase the width of the column where you have text.
- After that, select all the cells.
- In the end, go to Home Tab ➜ Editing ➜ Fill ➜ Justify.
This will merge text from all the cells into the first cell of the selection.
TEXTJOIN Function for CONCATENATE Values
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], …)
- delimiter a text string to use as a delimiter.
- ignore_empty true to ignore blank cell, false to not.
- text1 text to combine.
- [text2] text to combine optional.
how to use it
To combine the below list of values you can use the formula:
Here you have used space as a delimiter, TRUE to ignore blank cells and the entire range in a single argument. In the end, hit enter and you’ll get all the text in a single cell.
Combine Text with Power Query
Power Query is a fantastic tool and I love it. Make sure to check out this (Excel Power Query Tutorial). You can also use it to combine text from a list in a single cell. Below are the steps.
- Select the range of cells and click on “From table” in data tab.
- If will edit your data into Power Query editor.
- Now from here, select the column and go to “Transform Tab”.
- From “Transform” tab, go to Table and click on “Transpose”.
- For this, select all the columns (select first column, press and hold shift key, click on the last column) and press right click and then select “Merge”.
- After that, from Merge window, select space as a separator and name the column.
- In the end, click OK and click on “Close and Load”.
Now you have a new worksheet in 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 the old list with a new value you need to refresh your query and it will add that new value to the cell.
VBA Code to Combine Values
If you want to use a macro code to combine text from different cells then I have something for you. With this code, you can combine text in no time. All you need to do is, select the range of cells where you have the 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.
In the end,
There may be different situations for you where you need to concatenate a range of cells 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 tell me:
Which one is your favorite and worked for you?
Please share your views with me in the comment section. I’d love to hear from you, and please, don’t forget to share this post with your friends, I am sure they will appreciate it.
- Back to the List of Excel Formulas