Excel Combine Columns Into One Cell

 admin

So earlier we had CONCAT, and CONCATENATE function to concatenate multiple cells. But if we wanted to supply a range for joining cells with a delimiter (say a comma) then it is really tricky with these functions. But now Excel has introduced a new function called TEXTJOIN Function that can be used to concatenate ranges with a lot more flexibility.

In this article, we will learn how to concatenate cell values of a range with comma using TEXTJOIN function. For users who don't have this function we will discuss other methods of concatenating range values with comma.

Generic Formula

=TEXTJOIN(',',TRUE,text_range1,[text_range2]...)

Comma (',') : This is the delimiter we want to use. Since in this article we are concentrating on concatenating cells with commas.

TRUE : For ignoring blank cells in the range.

Text_range1 : This is the range whose cells have values you want to concatenate.

[Text_range2] : The other ranges if you want to join in the text with commas.

  1. So if you want to combine the first two columns, you would have to write something like this: =CONCATENATE (A1, B1). Step 2 – Going back to the previous example, should A1 contain “First Name” and B1 contain “Last Name,” the Excel cell with the concatenate formula will then show up as “First NameLast Name.”.
  2. How to Use Combine cells in Excel. Combining cells can be done in any way; this can be done via the formula of concatenating or using the symbol of “&.” Method 1 st by using the function of concatenating. Step 1 st; First, select the cell where you want the combined data to be displayed.

Let’s see an example to make things clear.

Example: Join Cell Values of Ranges With Comma as Delimiter

Here, we have some values in range B2:E8. We need to join the texts of each cell in a row.

Let's implement the formula we have and drag it down.

In Excel, we can combine multiple rows, columns or cells into one cell with the CONCATENATE function, and the combined contents can be separated by comma, dash, semicolon and other characters. But, have you ever tried to combine the cells with line break as following screenshot shown?

=TEXTJOIN(',',TRUE,B3:E3)

You can see that we have a string which is a result of concatenation of texts with commas.

Let's say if you want to concatenate the range B3:E3 and B7:E7. So the formula will be:

=TEXTJOIN(',',TRUE,B3:E3,B7:E7)

It will concatenate all the texts ignoring the blank cells.

How does it work?

The formula is simple. The TEXTJOIN function requires the delimiter with which you want to join text with. The second variable is set to be true so that it ignores the blank cells.

Now if any cell has invisible values like space then you will see an extra comma in between the joined text.

To avoid spaces, use the TRIM function to strip them out.

=TEXTJOIN(',',TRUE,TRIM(B3:E3,B7:E7))

Concatenating Cells with Commas in Excel 2016 and Older

The problem is that the TEXTJOIN function is only available to Excel 2019 and 365. So if you want to concatenate the cells with commas, we'll need to use a trick.

So to concatenate cells in a row with commas do this.

In a cell, write '=' to start the formula and select the range as shown below.

Now press F2 and select the range in the formula bar or cell.

Press F9 key.

Now remove the equals and curly braces. You have the cells joined with commas.

But this way is not that effective for too many operations.

So do we have any other way to combine texts with a given delimiter in Excel? The other way is the VBA way. Let's create one UDF to do this.

Press CTRL+F11 to open the VB Editor. Right click on the workbook and insert a module. Copy the code above and paste in the module's code area.


Now use this formula to join text with any delimiter you want to.

This formula will work in any version of Excel. You can download the workbook below to use this formula immediately.

So yeah guys, this is how you can join text with comma delimiter in Excel. I hope it was helpful for you. If you have any questions regarding this topic or any other excel related topic, ask in the comments section below. Till then keep Excelling.

Related Articles:

Split Numbers and Text from String in Excel 2016 and Older: When we didn't have TEXTJOIN function we used LEFT and RIGHT functions with some other functions to split numeric and nonnumeric characters from a string.

Extract Text From A String In Excel Using Excel's LEFT And RIGHT Function: To remove text in excel from string we can use excel's LEFT and RIGHT function. These functions help us chop strings dynamically.

Remove leading and trailing spaces from text in Excel: Leading and trailing spaces are hard to recognize visually and can mess up your data. Stripping these characters from the string is a basic and most important task in data cleaning. Here's how you can do it easily in Excel.

Remove Characters From Right: To remove characters from the right of a string in Excel, we use the LEFT function. Yes, the LEFT function. The LEFT function retains the given number of characters from LEFT and removes everything from its right.

Remove unwanted characters in Excel: To remove unwanted characters from a string in Excel, we use the SUBSTITUTE function. The SUBSTITUTE function replaces the given characters with another given character and produces a new altered string.

How to Remove Text in Excel Starting From a Position in Excel: To remove text from a starting position in a string, we use the REPLACE function of Excel. This function help us determine the starting position and number of characters to strip.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use Excel VLOOKUP Function This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use theExcel COUNTIF Function Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel This is another dashboard essential function. This helps you sum up values on specific conditions.

How to transpose data from column into one single cell in Excel?

When you need to transpose a list of data into one single cell in a worksheet, normally, you can apply the Concatenate function to merge the list of cells into one cell, but, it will be complex if there are huge data need to be combined. This article, I will talk about some quick tricks for you to solve this task in Excel.

Transpose data from column into one single cell with Kutools for Excel

Except the Concatenate function to merge a list of cell values, you can apply the following VBA code to get the result as quickly as you can.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Transpose data from column into one single cell

3. Then save and close this code, go back the worksheet, and enter this formula: =transposerange(A1:A10) into a blank cell to put the result, and press Enter key, you will get all the cell values in a column have been located in a single cell, see screenshot:

Note: In the above code, A1:A10 is the list range you want to transpose into one single cell, and also, you can separate the merged content with other delimiters, such as comma, dash, space, etc. by just changing the comma in the script xStr = xStr & xCell.Value & ','.

If you have Kutools for Excel, with its powerful tool-Combine, you can combine data in a column, row or a range to one cell.

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

After installing Kutools for Excel, please do as follows:( Free Download Kutools for Excel Now! )

1. Select the data column that you want to combine into one cell.

2. Click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, see screenshot:

3. In the popped out dialog box, select Combine into single cell under the To combine selected cells according to following options, and then specify a separator to separate the merged content, see screenshot:

Transpose column into single cell

4. Then click Ok or Apply button, and you will get the following result as you need:

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Multiple Columns Into Single Column Excel

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

How To Combine Columns Into One Cell Excel

or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Excel Combine Columns To One Cell

  • To post as a guest, your comment is unpublished.
    Hi,
    Thanks a lot! I'm using your following function and it works very well. I would like to include an 'if' function in the transposerange. What should I add to the function in VBA? Basically, it would work like a countif. So we would read the formula =transposerangeif(range,criteria).
    Thanks in advance!
    Function transposeRange(Rg As Range)
    'updateby Extendoffice 20151207
    Dim xCell As Range
    Dim xStr As String
    For Each xCell In Rg
    If Not IsEmpty(xCell.Value) Then
    xStr = xStr & xCell.Value & ','
    End If
    Next
    transposeRange = Left(xStr, Len(xStr) - 1)
    End Function
    • To post as a guest, your comment is unpublished.
      Hello,Sophie,
      Could you give an example for your need, you can insert a screenshot here.
  • To post as a guest, your comment is unpublished.
    Thanks, this was very helpful.