excel for mac combine strings from two cells

Now, you have a new worksheet your workbook with all the text in a single cell.

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..Id like share with you 5 different ways to combine text from a range..into a single cell.

Top 5 Ways to Combine Values from a Range of Cells Below are those ways which you can use to combine values from a range of cells.

Using a Formula Fill Justify Option TEXTJOIN Function Power Query VBA Code.so lets get started.

Using a Formula CONCATENATE TRANSPOSE to Combine Values 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 cells..where you have a text but its every word is in a different cell and you want to get is 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, insert formula ( CONCATENATE(TRANSPOSE(A1:A5) ) ) and do not press enter.

Now, just select the entire inside portion of concatenate function and press F9.

After that, remove the curly brackets from the start and the end of the array.

In the end, hit enter.

Thats all..how this formula works In this formula, you have used TRANSPOSE and a space in the CONCATENATE.

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.

Combine Text using 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.

First of all, make sure to increase the width of the column where you have text.

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.

Syntax: TEXTJOIN(delimiter, ignoreempty, text1, text2, ) delimiter a text string to use as a delimiter.

How to use it To combine below list of values you can use the formula: TEXTJOIN(,TRUE,A1:A5) Here you have used space as a delimiter, TRUE to ignore blank cells and entire range in a single argument.

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.

Select the range of cells and click on From table in data tab.

From Transform tab, go to Table and click on Transpose Next, you need to merge all the columns.

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.

Excel For Mac Combine Strings From Two Cells