How to Remove Space in Excel (Including Types of Spaces)
By Indeed Editorial Team
Published 27 April 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
Spaces on your Excel document can cause faulty results when you apply a formula. The program considers similar texts with different spacing as different values. Professionals in a variety of industries use Excel to organise and track data, and understanding how to extract spaces from your document can help you apply formulas successfully. In this article, we discuss how to remove space in Excel and highlight why this is important.
How to remove space in excel
The following steps can help you learn how to remove space in Excel:
1. Use the trim function
The trim function extracts space characters from texts in a cell except for the single space between words. You can write a trim function at the formula bar to start this command. The trim function can perform different tasks, as shown below:
Removing spaces from a single cell
The general syntax for this function is TRIM (Text), where text represents the cell you wish to extract unwanted spaces. Using this technique, you can remove leading, trailing and extra spaces between words and enhance your writing experience. For example, you can use remove a space from the third cell of the first column by typing '=TRIM(C1)'.
Trim spaces in an entire column
You can insert the trim formula into a whole column to extract spaces. The procedure involves copying the function to the entire column and replacing the original text with your edited version. The following steps can help you execute this operation:
Insert the trim formula to the first cell after the new column header.
Position your cursor at the bottom right of this cell.
Double-click it when the function cell as soon as the plus sign appears to copy the formula to the entire column.
Highlight the cells under the trim column header.
Copy them by using 'CTRL' + 'C'.
Select the original column.
Press 'CTRL' + 'ALT' + 'V' to paste the edited texts into the first column.
Press the ‘Enter' key to apply changes.
Remove spaces in a numeric column
The previous procedure creates text strings and may not eliminate spaces for numeric columns. You can fix this issue by multiplying the trimmed values by the number one. For example, the following formula can turn the data in your B2 cell and B-column into valid numbers that you can use for operations:
Use a helper column
You can choose to use a helper column with the trim function as an alternative to removing unnecessary spaces from single cells. This technique can be efficient for a document with few details. The following procedure can help you accomplish this task:
Add an extra column next to your text's column.
Name it trim.
Type '=TRIM(A1)' to the B1 cell under the trim column.
Press 'Enter' to apply the formula.
Copy the operation to the following cells.
Replace the original column with the one that has the data you've corrected.
Highlight all cells in the helper column and press 'CTRL' + 'C'.
Select the first cell in the initial column.
Choose 'Shift' + 'F10' and press 'V' to apply changes and remove spaces from other cells.
Delete the helper column.
Extract line breaks
Pasting data from an external source to your Excel document can cause several non-printing errors, such as carriage return, form feed, horizontal and vertical tabs. The trim function can eliminate the unnecessary spaces from your imported data but not the non-printing inaccuracies. You can use the following example to remove line breaks from the third cell on your text:
Remove non-breaking space
Pasting text to your Excel document can also cause non-breaking errors, which you cannot remove with the previous steps. To remove these undesirable areas, you may convert them to standard spaces, then use both trim and substitute functions. For example, you can delete a non-breaking space that has a value of 160 by using two steps, as shown below:
=TRIM(SUBSTITUTE(C1, CHAR(160), “ ”))
Eliminate non-breaking spaces and line breaks
If your document has non-breaking spaces, line breaks and unwanted symbols, you can use the formula below to eliminate them. This technique incorporates trim, clean, substitute and character functions. For example:
=TRIM(CLEAN((SUBSTITUTE(C1, CHAR(160), “ ”))))
Extract non-breaking spaces and non-printing characters
If your data comprises non-printing characters and non-breaking spaces, you can use two or more substitute operations to eliminate these undesired character codes. You may also type the values for non-breaking regions and the delete function alongside these substitute commands. Non-breaking symbols have a value of 160 in the 7-bit ASCII system, while the delete character has a value of 127. For example:
=TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(127), “ ”), CHAR(160), “ ”)))
2. Remove leading spaces
Efficient Excel skills can help you eliminate leading spaces while maintaining the gaps between words. The formula for this action combines the find, mid and trim functions. You can also use the LEN operation to calculate string length from the position of the first character. For example,
=MID(B2, FIND(MID(TRIM(A2), 1, 1), A2), LEN (A2))
3. Eliminate all spaces
You can eliminate all white regions in a cell, including spaces between words and numbers, by using a single command. For instance, gaps that separate numerical columns for easy reading can affect the operation of your formulas during execution. You can remove all spaces from your document by using the substitute function and a character string of value 32, as shown below:
=SUBSITUTE(A2, CHAR(32), “ ”)
4. Use the find and replace dialogue box
You can apply basic computer skills and use this method to remove extra spaces between words. This technique also trims leading and trailing spaces to a single gap. The following procedure can help you execute this operation:
Select the cells and columns that you wish to remove spaces from.
Hold the 'CTRL' key and press 'H' to open the find and replace dialogue box.
Go to the 'Replace' tab.
Press the spacebar twice in the first section and press it once in the replace what section.
Select 'Replace all' at the bottom left of the display box.
Repeat this process until a box stating that there's nothing to replace appears.
5. Remove spaces from blank rows or columns
Empty rows can provide inaccurate results when applying formulas or compiling your work. These blank regions can appear after receiving an Excel document from a colleague, exporting data from an organisation's database and removing undesirable cells manually. The following steps can help you eliminate these spaces without altering your document's format:
Select empty cells
This technique can eliminate entire rows and columns with empty spaces. You can apply it to simple and short tables to avoid altering the file's format. The following steps can act as a guideline for you:
Highlight the texts you wish to remove spaces from.
Press the 'F5' function key to open the ‘Go to dialogue' box.
Select the 'Special' tab from the display.
Choose the ‘Blanks' button from the special dialogue box and press 'OK'.
Right-click on any highlighted text and select ‘Delete'.
Select ‘Entire row' or ‘Entire column' from the delete display box.
Use a key column
A key column is a combination of vertical cells that you can use to identify rows in a table uniquely. This technique can be an alternative to removing empty rows from your document. You can follow the following procedure to apply this operation:
Highlight the entire table by holding the 'CTRL function' and pressing 'A'.
Select the data tab from the toolbar.
Press the 'Filter' button that has a funnel icon.
Select the arrow that gives a drop menu from the column header of the first column.
Uncheck the ‘Select All' ticked checkbox.
Scroll down and select the ‘Blanks' checkbox and click ‘OK'.
Highlight the filtered rows by pressing 'CTRL' + 'A'.
Right-click and select ‘Delete row' from the context menu.
Select ‘Ok' at the dialogue box that appears.
Go to the Data tab and select the ‘Clear' button to remove the applied filter.
Add a helper column
Adding a helper column can assist you in removing spaces in Excel that are scattered cells. If your document doesn't have a key column, you can use this technique. You can use the following steps to achieve this task:
Add an extra column next to your last column.
Name it ‘Blanks' since it's a function that returns ‘TRUE' when a row is empty.
Type the formula '=COUNTBLANK(A2:E2)', where A2 and E2 represent the range.
Copy this formula to the entire column.
Apply the steps from the preceding sections since you've created a key column.
Delete the helper column.
Types of spaces
There are various types of spaces in Excel, depending on their location. Understanding them can help you better understand the preceding procedures. They include:
Leading spaces: These gaps occur before the text string in a cell.
Trailing spaces: These are empty regions that exist at the end of your text.
Added in-between spaces: The extra gaps that occur between words.
Line breaks: These discontinuities start a new line in a single cell.
Non-breaking spaces: This function prevents the automatic formation of line breaks.
Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.
Explore more articles
- What Is Social Learning Theory? (With Benefits and Methods)
- What Is the Incident Management Process? (Including Steps)
- What Is Organisational Leadership? (Benefits and Components)
- What Does a Mentor Do? (Plus Benefits of Mentorship)
- How to Create a Theoretical Framework (With Definition and Steps)
- Team Leadership Skills: Definition and Workplace Examples
- 7 Brand Strategy Examples: Types, Definition and Importance
- How to Write a Retirement Speech in 6 Steps (Plus Tips)
- 12 Important Future Skills to Develop for the Workplace
- What Is Cloud Architecture? (With Definition and Benefits)
- Personal Selling: Definition, Process, Strategies and Pros
- What Is a Psychometric Test? Benefits, Tips and Example