How To Add a Space in Excel? [Complete Guide]

The easiest and most common way to insert a space anywhere else is by hitting that space bar. But Excel is all about conditions and logic. You want the sheet to add space where it is supposed to.

There are quite a few popular ways to add a space between text or when concatenating. All these methods to smartly add spaces need a little understanding of the functions.

Let’s take a look at them one by one!

Adding Single Space in a New Cell

What you need are a couple of feeding cells where you can enter texts and a destination cell that executes our formula and sources texts from the feeding cells. So write two different things in two different cells (consecutively) and then access the third cell.

So if you want to add a space between the two sourced texts, then this is how your syntax should be:

=B4&” “&C4

Here are the steps you then need to follow to add a single space in Excel:

1. Double-click the cell where you want to add a space. Alternatively, hit F2 or go to the function bar.
2. Write down the formula =B4&” “&C4 (where B4 and C4 are the cells where the text lies).
   
3. Hit Enter to know the results.

The “&” ”&” adds a space between them since there is just a blank space between the inverted commas.

Adding Multiple Space in New Cell

If you want to add multiple spaces between two sourced texts from two cells, then instead of adding multiple spaces manually with the spacebar in the formula, you can try using the REPT function.

The REPT function repeats the target text formatting for the number of times you mention. Consider the same two cells that have the text that you want to source and add multiple spaces in between them in the destination cell.

Here is the syntax to use the REPT function:

=B4&REPT(“ “,10)&C4

Now follow these steps:

1. Double-click the cell where you want to add a space. Alternatively, hit F2 or go to the function bar.
2. Write down the formula =B4&REPT(“ “,10)&C4 (where B4 and C4 are the cells where the text lies).
   
3. Hit Enter to know the results.

The number 10 denotes the number of times the REPT function is supposed to execute the display of formatted text, which in our case, is a space between inverted commas. So the function will source texts from the cells B4 and C4 and add 10 spaces in between them.

Add Space Between Multiple Values

What if you have more than two cells to source text? What if you want to add texts from multiple cells into one but with proper spacing between them?

Well, the TEXTJOIN function helps you here. Let us assume the source cells are B2, B3, B4, B5, B6, B7, B8. Here is the syntax for the TEXTJOIN function:

=TEXTJOIN(“ “,TRUE,B2:B8)

Now follow these simple steps:

1. Double-click the cell where you want to add a space. Alternatively, hit F2 or go to the function bar.
2. Write down the formula =TEXTJOIN(“ “,TRUE,B2:B8) where B2 to B8 are the cells where the text lies.
   
3. Hit Enter to know the results.
   

This will source texts from cells B2 to B8, join them together, and add a space between each text.

Other Advanced Formulas for Adding Space

The thing with Excel is that you never know the kind of data you import. So you have to use different formulas and customize them to suit your needs. If you have textual data that is not properly spaced out, you have to use some custom formulas.

Here are some advanced formulas that help you format your text with proper spacing.

1. CONCATENATE Formula

If you have a string of text values to join along with some cells to source in a text that you want to display with proper spacing between them, then CONCATENATE is perhaps the simplest and the most effective function.

Let’s say you want to write “My name is [name], and I am [age] years old” and want to source age and name from two different cells then here is the syntax:

=CONCATENATE(“My name is “, A4, ” “, “and I am “, B4, ” “, “years old.”)

2. SUBSTITUTE Formula

You can use the SUBSTITUTE function to substitute the text with your custom-made text. You can also define the number of times you want to trigger the instance of substituting right at the end.

The only thing is this function involves writing a lot of text manually, which is not ideal. Let’s assume you have a source text “MynameisAdam” inside a cell, and you want to replace it.

Here is the syntax for it.

=SUBSTITUTE(B4,”MynameisAdam”,”My name is Adam”)

3. CHAR Function

The CHAR function allows you to add special characters in a cell. If you have text from two source cells and if you want to add a space between them using the CHAR function then you have to use the ampersand sign (&) to separate them.

Let’s assume the source texts are in the cells B4 and C4.

Here is the syntax to add a space between texts using the CHAR function:

=B4&CHAR(32)&C4

The number next to “CHAR” specifies the unique characters that you want to insert. You can enter numbers between 1 to 255. 32 inserts the space character.

4. RIGHT and LEFT Function

The RIGHT and LEFT functions allow you to separate text from either the right or the left side. You can separate the text referring to the number of characters you want to access from either the left or the right. But you can also use both functions together and add a space between characters.

Let’s assume your source text is “Adam52”, and it is in cell B4, and you want to add a space in between.

Here is the syntax to add a space between texts using the RIGHT and LEFT functions.

=LEFT(B4, 4)&” “&RIGHT(B4,2)

The LEFT function will access the four characters of the text in B4 from the right side, the &” “& will add a space, and the RIGHT function will access the 2 characters of the text in B4 from the left side.

Did you know? You can also separate date and time, remove dashes, subtract two columns, and flip axis in Excel.

Frequently Asked Questions

Wrap Up!

Putting spaces at the right place makes a world of difference. Proper formatting of text requires a little more than manually entering spaces and other characters.

This is where the dynamic functions like CONCATENATE, SUBSTITUTE, CHAR, and RIGHT/LEFT come in handy as you only have to write the formula and the source. The sheet then formats the text for you with proper spacing.