Table of Contents
You can use the following methods to add blank spaces in an Excel formula:
Method 1: Concatenate Two Cells with a Blank Space
=CONCAT(A2, " ", B2)
This particular formula concatenates the text in cell A2 and B2 with a single space between them.
Method 2: Concatenate Two Cells with Multiple Blank Spaces
=CONCAT(A2, REPT(" ", 5), B2)
This particular formula concatenates the text in cell A2 and B2 with five blank spaces between them.
Method 3: Add a Blank Space Within a Cell
=LEFT(C2,2)& " "&RIGHT(C2,4)
This particular formula concatenates the first two characters on the left in cell C2 with the last four characters on the right in cell C2 with a blank space between them.
The following examples show how to use each method in practice with the following dataset in Excel that contains information about employees at some company:
Let’s jump in!
Example 1: Concatenate Two Cells with a Blank Space
We can type the following formula into cell D2 to concatenate the values in the first and last name columns with a space in between the names:
=CONCAT(A2, " ", B2)
We can then click and drag this formula down to each remaining cell in column D:
Column D now contains the first and last names concatenated together with a single space between them.
Example 2: Concatenate Two Cells with Multiple Blank Spaces
We can type the following formula into cell D2 to concatenate the values in the first and last name columns with five spaces in between the names:
=CONCAT(A2, REPT(" ", 5), B2)
We can then click and drag this formula down to each remaining cell in column D:
Column D now contains the first and last names concatenated together with five blank spaces between them.
Example 3: Add a Blank Space Within a Cell
We can type the following formula into cell D2 to add a blank space between the first two values on the left of the Employee ID column and the last four values on the right:
=LEFT(C2,2)& " "&RIGHT(C2,4)
We can then click and drag this formula down to each remaining cell in column D:
Column D now contains the Employee ID values with a space between the first two values on the left and the last four values on the right.