How to Add Blank Space in Excel Formula

Sometimes, you may want to add some blank space in your Excel formula to make it more readable or to align it with other cells. There are different ways to do this, depending on what kind of space you need and where you want to insert it.

In this article, we will show you four methods to add blank space in Excel formula, and also give you an example from our website Blank Space Copy and Paste.

How to Add Blank Space in Excel Formula

Method 1: Use Double Quotation Marks

One of the simplest ways to add blank space in Excel formula is to use double quotation marks (” “). This will create a text string that contains one or more spaces, which you can then concatenate with other text or values using the ampersand (&) operator. For example, if you want to add a space between two words in cell A1, you can use this formula:

=A1&" "&B1

This will return the result “Hello World” in the cell where you enter the formula. You can adjust the number of spaces by adding more quotation marks, such as:

=A1&" "&" "&B1

This will return the result “Hello World” with two spaces between the words.

Method 2: Use the REPT Function

Another way to add blank space in Excel formula is to use the REPT function, which repeats a text string a specified number of times. You can use this function to create a text string that contains multiple spaces, and then concatenate it with other text or values. For example, if you want to add 10 spaces before the word “Hello” in cell A1, you can use this formula:

=REPT(" ",10)&A1

This will return the result ” Hello” in the cell where you enter the formula. You can change the number of spaces by changing the second argument of the REPT function, such as:

=REPT(" ",5)&A1

This will return the result ” Hello” with five spaces before the word.

Method 3: Use the CHAR Function

A third way to add blank space in Excel formula is to use the CHAR function, which returns a character based on a numeric code. You can use this function to create a text string that contains different types of spaces, such as non-breaking space, thin space, or zero-width space, and then concatenate it with other text or values. For example, if you want to add a non-breaking space between two words in cell A1, you can use this formula:

=A1&CHAR(160)&B1

This will return the result “Hello World” in the cell where you enter the formula. The non-breaking space has a numeric code of 160, and it prevents the text from wrapping to the next line. You can use other numeric codes to create different types of spaces, such as:

  • CHAR(32) for normal space
  • CHAR(8201) for thin space
  • CHAR(8203) for zero-width space

Method 4: Use the TEXTJOIN Function

A fourth way to add blank space in Excel formula is to use the TEXTJOIN function, which joins multiple text strings with a specified delimiter. You can use this function to create a text string that contains spaces between multiple values, without using the ampersand (&) operator. For example, if you want to join the values in cells A1, B1, and C1 with a space between them, you can use this formula:

=TEXTJOIN(" ",TRUE,A1,B1,C1)

This will return the result “Hello World Excel” in the cell where you enter the formula. The first argument of the TEXTJOIN function is the delimiter, which is a space in this case. The second argument is a logical value that indicates whether to ignore empty cells or not. The third argument is the range of cells that you want to join. You can use other delimiters, such as commas, dashes, or slashes, to create different effects, such as:

=TEXTJOIN(",",TRUE,A1,B1,C1)

This will return the result “Hello,World,Excel” with commas between the values.

Example from Our Website

To give you an example of how to use these methods to add blank space in Excel formula, we will use the website BlankSpaceCopyAndPaste, which provides various types of blank spaces that you can copy and paste into your Excel worksheet. For instance, if you want to use the en space, which is a space that is half the width of a normal space, you can copy it from the website and paste it into your formula, such as:

=A1&" "&B1

This will return the result “Hello World” with an Ideographic Space between the words. You can use other types of spaces from the website, such as:

  • em space, which is a space that is the same width as a normal space
  • four-per-em space, which is a space that is one-fourth the width of a normal space
  • hair space, which is a very thin space
  • zero-width space, which is a space that has no width at all

Conclusion

In this article, we have shown you four methods to add blank space in Excel formula, and also given you an example from the website Blank Space Copy and Paste. You can use these methods to create different effects and improve the readability of your formulas. We hope you have found this article helpful and informative. Thank you for reading. 😊