In this Excel tutorial, you will learn how to add space using different Excel functions like REPT, REPLACE, TEXT, or TEXTJOIN. You will also learn to remove spaces with the TRIM function, Find and Replace feature, or the SUBSTITUTE function.
Furthermore, through this article, you will get an idea of how to align your data perfectly. Or, how you can put spaces between rows.
We have used Microsoft 365 to prepare this article. However, you can do the same for other versions as well. However, in the case of using the TEXTJOIN function, keep in mind that it has been available only since the Excel 2019 version.
We must use the spaces to maintain a proper format and create user-friendly data. So that, someone can easily read and understand your data. On the other hand, you can’t use extra spaces in your dataset. Extra spaces will convert the data into a clumsy one. So, you must know when you should have a space, when to remove it, or where to replace it.
You can see in the image above, I have shown different formulas for removing and adding spaces.
⏷Add Space in Excel
⏵Add Space in a Certain Position
⏵Use REPT Function to Add Multiple Spaces
⏵Apply REPLACE Function to Add Space Between Text
⏵Use TEXT Function to Add Space Between Number
⏵Add Multiple Spaces to Combine Multiple Cell Values
⏷Remove Space in Excel
⏵Remove Leading/Trailing/Repeated Space With TRIM Function
⏵Use Find and Replace Feature to Remove All Spaces
⏵Remove All Spaces with SUBSTITUTE Function
⏷Change Line Spacing with Format Cells Feature
⏷Put Space Between Rows
⏷Frequently Asked Questions
⏷Space in Excel: Knowledge Hub
How to Add Space in Excel?
When you have text data but no space between the words, then it will be a difficult task to understand it. So, you need to add space between text in this case, right? Another case is if you are dealing with large numbers, then it is a convenient way to use space between these digits. To add a space in a cell, we can use:
- Different functions like REPT, REPLACE, TEXT, etc.
- The ampersand operator to join spaces as text between values
1. Adding Space at a Certain Position with Ampersand, LEFT, and RIGHT Functions
The ampersand operator (&) joins two or more texts (according to the formula). When we provide space as one of those texts, it will add it.
If all the cells have equal characters, then use this method. Here, I have added a space between the alphabet and numeric terms.
- In cell C5, write the following formula and press ENTER to get the result.
- Use the Fill Handle icon to copy the same formula for the rest of the cells of this column.
=LEFT(B5,3)&" "&RIGHT(B5,5)
Here, the LEFT function will take 3 leftmost characters from the B5 cell. The RIGHT function will take 5 rightmost characters from the cell. Then the ampersand operator (&) will join them with a space.
2. Use REPT Function to Add Multiple Spaces in Excel
The REPT function repeats a text. Below, you can see the basic information of this function.
So, if we provide a space as its text argument, it will repeat it a specific number of times.
Here, I will preserve the employee’s name and department in one cell. Thus, I will use multiple spaces between them, 4 to be exact.
- Use the following formula in the target cell >> press ENTER >> then use the Fill Handle icon to copy the same formula for others.
=B5&REPT(" ",4)&C5Here, the REPT function will consider a blank space 4 times. Then ampersand will join these spaces with B5 and C5 cell values.
3. Applying REPLACE Function to Add Space Between Text in Excel
The REPLACE function replaces a portion of a text with another. I have attached an overview of the REPLACE function here:
So, when we use spaces as new text to replace, it can add them.
- Now to get a single space between text, use the corresponding formula in the D5 cell.
=REPLACE(C5,6,0," ")- Press ENTER to get the target output in C5 cell.
The above formula will add a space after the 6th character. But in other cells, our target positions are different. Therefore, you have to manually copy the formula and then change the start_num argument according to necessity.
4. Add Space Between Number Using TEXT Function
The TEXT function converts a value to certain formats of text. And we can use this format feature to have space in those values. See below, you will get a proper idea related to the TEXT function.
- To create space after certain digits, use the following formula in Excel.
=TEXT(D5,"## ### ####")The TEXT function will include 1st space after 4 digits from the right, and then 2nd space will come after the next 3 digits from the right side.
- Press ENTER to get the output >> use the Fill Handle icon for the rest of the cells.
5. Adding Multiple Spaces to Combine Multiple Cell Values with TEXTJOIN Function
Here, we want to join some cell values keeping multiple spaces between them. We have some IDs, employee names, and their departments. We have to join these values along with multiple spaces to make them understandable. To do so, we’re going to use the TEXTJOIN function. Below, you can see an overview of this function.
- Here, we’ve merged B8:G8 cells to store the output.
- In B8 cell, use the following formula to connect multiple cell values.
=TEXTJOIN(" ",TRUE,C$4:C$6)In this formula, multiple spaces act as the link delimiter. The “TRUE” value of the ignore_empty argument will ignore all the empty values. Then this TEXTJOIN function will join C4 to C6 cell values using the given delimiter.
How to Remove Space in Excel?
We can remove space in Excel using the TRIM and SUBSTITUTE functions and the Find and Replace feature.
Removing spaces in Excel is important because they can mess up your data. Which causes mistakes. When you want to organize, search, or do calculations, these spaces can cause problems. So, let’s see how to remove these spaces. Here, we will explain four different ways.
1. Use of TRIM Function to Remove Blank Space (Leading/Trailing Spaces) in Excel
By default, the TRIM function removes extra spaces in any value. Here is how you can use them:
- So, when you need to remove extra spaces then you can enter the following formula.
=TRIM(C5)This TRIM function will remove all extra spaces, and only keep a single space between words.
2. Using Find and Replace Feature to Remove All Spaces
We can use the Find and Replace feature to change certain values to a new one or completely remove them. This, in turn, is very helpful to remove spaces from a particular spreadsheet or range.
If you need to remove all the spaces, then you can use this Find and Replace feature of Excel.
- Select the cells >> from the Home tab >> go to Find & Select >> choose Replace.
At this time, you will see a dialog box named Find and Replace.
- From this dialog box give one Space at Find what >> keep the Replace with box blank >> press Replace All.
- Then you will get information from Microsoft Excel. Press OK on it.
- After that, press OK to Find and Replace dialog box >> get the result.
3. Use of SUBSTITUTE Function for Removing All Spaces
The SUBSTITUTE function works similar to the REPLACE function. Just like how we can add spaces in values through these functions, we can also remove the spaces using them. Here, for your better understanding, I am attaching an overview of the SUBSTITUTE function.
- To remove any space from a cell, enter the following formula.
=SUBSTITUTE(B5," ","")Here, the SUBSTITUTE function will convert all spaces into null values.
- You can use the Fill Handle icon for the rest of the cells.
How to Change Line Spacing with Format Cells Feature in Excel?
The main idea is to use the Alignment options of the Format Cells feature to our advantage. If you use spaces to line up numbers and text in columns, keeping things neat then it will be easier to make the data understandable to others. See the following image. One employee has the largest name. So, to make that cell visible, the column width is increased, which is not a visually friendly dataset.
- To change the line spacing select the whole dataset >> press CTRL+1.
- So, you will get the Format Cells dialog box >> go to Alignment section >> choose Justify under the Vertical option >> press OK.
As a result, you will get the wrap text format for that cell.
How Do I Put a Space Between Rows in Excel?
We are going to need a helper column for that. Having them twice and setting them in order will automatically put in extra spaces between rows. After that, we can remove the helper column. Here are the details of the process:
- Create a helper column >> Write 1, 2, and so on in the cells.
- Afterward, copy the numbers >> paste them just below the same column >> from the Home tab >> go to Sort & Filter >> choose Custom Sort.
- Then, a dialog box named Sort appears >> Select Helper in the Column Sort by >> Select Smallest to Largest in the Order box >> Press OK.
- You will get the output below. If you want, you can delete the Helper column now.
Frequently Asked Questions
1. What is cell spacing?
In Excel, cell spacing denotes the space between cells of a worksheet. We know the text values remain on the left side of the cell, and date/number values remain on the rightmost side of the cell. Thus, it looks like they are attached to the cell wall. So, you can change the Indent from Format Cells option to increase cell spacing.
To change the Indent angle from Format Cells >> Alignment >> Horizontal >> Left/Right (Indent) >> increase the Indent angle manually.
2. How do I add a space between rows in a table in Word?
In Microsoft Word, select the table >> go to Layout ribbon >> from Table Properties >> go to Table menu >> click on Options >> then from Table Options dialog box >> change the Default cell margins.
3. How do I count spaces as characters in Excel?
To count all the characters of a cell including spaces, you should use the LEN function. Go to a cell >> write =LEN(cell_reference) >> press ENTER.
4. How to remove spaces, line breaks, and nonprinting characters in Excel?
For removing line breaks and nonprinting characters, you might combine the CLEAN function along with the TRIM function. For instance, you have a text having a line break and a nonprinting character in cell A1. Now, go to an empty cell (B1) >> write =TRIM(CLEAN(A1)) >> press ENTER.
Practice Session
Follow these methods and practice by yourself.
Download Practice Workbook
Conclusion
Here, we discussed how to add/remove space in Excel. Also, through this article, you got to know how to deal with spaces, how you can change the alignment, and how you can add spaces between rows. You should focus on your data on when to add spaces or when to remove spaces. Then according to your requirement, apply the explained method. We hope this will help you to make/preserve data in Excel. However, if you have any queries or recommendations about this article, please leave a comment below. Thank you.
