How to merge data in two cells in Excel

You can watch a video tutorial here.

Excel is popular as a spreadsheet application but also has several functions that can be used to manipulate text. When working with data you may need to merge the data in two cells into one. When merging data in two cells, it is usually a good idea to include a delimiter such as a space so that the separation of the data is evident.

To combine cells, 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……)
     1. 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…….)
     1. delimiter: the delimiter to be used between the pieces of text
     2. ignore_empty: if set to TRUE, this ignores empty pieces of text
     3. text1, text 2…..: the pieces of text to be joined. At least one piece is mandatory

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:

= No. & “ “ & Name

• Press Enter
• The values from the cells are combined into a single cell

Note: The “  “ introduces a space between the fields

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(No., “ “, Name)

• Press Enter
• The values from the cells are combined into a single cell

Note: The “  “ introduces a space between the fields

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,No.,Name)

• The space is the delimiter
• Press Enter
• The values from the cells are combined into a single cell