Adding Space (Single and Multiple)
When you need to combine two values in a single cell, it is often necessary to insert a space between those values. Sometimes, you may even need to insert multiple spaces. This tutorial will guide you on how to write a formula to add a space or multiple spaces in Excel.
Adding a Single Space:
- Start by entering an equals sign (=) in a cell.
- Refer to the cell containing the first value and type an ampersand (&).
- Type (” “) to indicate the space you want to add.
- Next, refer to the cell containing the second value and type an ampersand (&).
- Finally, press Enter to get the result.
Once you press Enter, you will see both values combined in a single cell with a space between them.
Example Formula: =A1&” “&B1
Adding Multiple Spaces:
If you want to add more than one space between two values, you can use the REPT function.
In this formula, instead of using a single space, we utilize the REPT function. Within the REPT function, we specify the space and the desired number of repetitions.
Example Formula: =A1&REPT(” “,20)&B1
Adding Multiple Spaces for Multiple Values:
If you have multiple values and wish to add a space between each of them while combining, you can use the TEXTJOIN function.
Within the TEXTJOIN function, you need to specify the delimiter (which, in this case, is a space) to be inserted between each value. Additionally, include TRUE to ignore blank cells, and provide the range containing the values.
Example Formula: =TEXTJOIN(” “,TRUE,A1:A5)