How to concatenate in Excel with space

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. The operation of combining text is known as concatenation. Excel has 3 ways in which values can be concatenated. When combining data from multiple columns 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 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

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:

= Title & “ “ & First name & “ “ & Last name

• Press Enter

Note: The “  “ introduces a space 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 columns are combined with a space between the values

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(Title, “ “, First name,“ “, Last name)

• Press Enter

Note: The “  “ introduces a space 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 values from the columns are combined with a space in between

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,Title,First name,Last name)

• The space 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 columns are combined with a space separating the values