How to put data from multiple columns into one column in Excel

You can watch a video tutorial here.

Excel is popular as a spreadsheet application but is also frequently used as a database and to create lists. When working with tables, you may need to combine text from multiple columns into a single column. Excel has several functions that can be used to manipulate text. 

To combine data, we will explore the following options:

1. Ampersand (&) operator: this joins pieces of text together
2. CONCAT() function: this joins pieces of text together
   • Syntax: CONCAT(text1, text2……)
     • text1, text 2…..: the pieces of text to be joined; at least one piece is mandatory
3. TEXTJOIN() function: this combines multiple pieces of text into a single string using the specified delimiter
   • Syntax: TEXTJOIN(delimiter, ignore_empty, text1, text 2…….)
     • delimiter: the delimiter to be used between the pieces of text
     • ignore_empty: if set to TRUE, this ignores empty pieces of text
     • text1, text 2…..: the pieces of text to be joined. At least one piece is mandatory

When combining data from multiple columns it is usually a good idea to include a delimiter such as a space or a hyphen so that the separation of the data is evident.

Option 1 – Use the ampersand (&) operator

Step 1 – Create the formula with &

• Select the cell in which the result is to appear
• Type the formula using cell references:

= Address & “- “ & Address2 & ”-“ & City & “-“ & Pincode

• Press Enter

Note: The “- “ introduces a hyphen between the fields

Step 2 – Copy the formula and check the result

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• The data is combined into a one column

Option 2 – Use the CONCAT() function

Step 1 – Create the formula with CONCAT()

• Select the cell in which the result is to appear
• Type the formula using cell references:

= CONCAT(Address,“- “,Address2,”-“,City,“-“,Pincode

• Press Enter

Note: The “ -“ introduces a hyphen between the fields

Step 2 – Copy the formula and check the result

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• The data from multiple columns has been put into one column

Option 3 – Use the TEXTJOIN() function

Step 1 – Create the formula with TEXTJOIN()

• Select the cell in which the result is to appear
• Type the formula using cell references:

= TEXTJOIN(“- “,TRUE,Address,Address2, City,Pincode)

• The hyphen is the delimiter
• Press Enter

Step 2 – Copy the formula and check the result

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

3. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
4. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• The data from multiple columns has been put into one column