Removing space in Excel is a simple task but can be hard to do. Perhaps you have the data needed to complete your search, but blank spaces have made your function queries inaccurate. It is also possible that leftover spaces are mistakes waiting to be fixed.
Many possibilities could leave you with extra space to trim. Fortunately, there are multiple ways to remove space in Excel.
I’ve listed a few methods down below.
How To Remove Blank Spaces In Excel
Delete
The most manual method (and not recommended) – you can simply go into each cell and delete the spaces that aren’t required.
The delete button is a universal way of getting rid of any unwanted data, whether the sheet is filled or empty. Drag the cursor over the desired area, then press the delete button. You may delete as many times as needed to remove excess.
Personally, I would only use this as a last resort.
Interesting read – What Is Data Validation In Excel?
Trim Function
The trim function is a formula to remove parts of a cell. The user must navigate to the formula box, insert “TRIM,” and include the cells they want to trim in parentheses. The TRIM function results in the removal of the extra space.
Ctrl + H Shortcut
The Ctrl + H shortcut is self-explanatory. After pressing the two buttons in tandem, a dialog box titled “Replace” appears. The user can then select what they would like to replace in a single space, which can help remove unwanted space. In the example below, type a single space in Find What and leave the Replace with blank.
Find and Select
The non-shortcut version of Replace. The Find and Select feature is in the top right portion of the Excel sheet. By clicking the Find and Select option, the user can choose to Replace and select the unwanted space with desired data.
CLEAN Function
The CLEAN function works with the TRIM function, which can remove unwanted line breaks and nonprinting characters. The user will insert “=TRIM(CLEAN(Cell Number))” into the formula box. The cell number can be a single cell or many.
SUBSTITUTE Function
You can go one step further by including the SUBSTITUTE function. This helps to remove non-breaking spaces which break the non-breaking spaces into regular spaces.
This feature works with the TRIM and CLEAN functions in the formula box. The SUBSTITUTE feature would appear as “=TRIM(CLEAN(SUBSTITUTE(Desired Cells))).”
Interesting read – How To Auto Resize Image In Excel Cell
Improve Your Excel Sheet
Removing spaces is crucial for editing a spreadsheet. Blank spaces create issues for financial modelers, data analysts, or anyone that uses spreadsheets. They’re not easy to spot and it’s quite annoying when your formula randomly spits out errors like #N/A.
I hope you have found this helpful and that it has saved you some time!
You may also like to read Top 5 Excel Formulas For Finance Analysts.
