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

  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