Does this sound familiar? You are searching for data that you know you’ve entered, but a query for Joe Smith simply doesn’t return any results. Or, how about – you are trying to add up two columns of numbers, but you keep getting errors.
Often, these issues are caused by extra spaces hiding before, after or between the text and numbers in your data. Especially when information has been copy/pasted into a spreadsheet, extra spaces can come along for the ride and lurk unnoticed until something goes wrong.
Excel offers several ways to help you remove spaces and clean up your data, but you will want to choose the right technique for the type of data you are working with. The following tips will describe two methods using functions and formulas to remove unwanted spaces from your data.
These steps will apply to Excel 2007-2013. Images were taken using Excel 2013 on the Windows 7 OS.
Use the Trim Function
The TRIM function removes all spaces from text except a single space between words. Use this function when you have text data that contains hidden spaces in the cell. In our example, you’ll see that there are multiple “Joe Smiths” in our data. At a glance, several of them even look the same. However, the # of Characters column reveals that there are hidden spaces buried in the text.
To use the TRIM function, you will need to create a temporary, or helper, column for the corrected data at the end of your spreadsheet. For the purposes of this demonstration, we’ve called the column “Trim.”
To follow using our example above, download Excel Remove Spaces Formula Template 1.xlsx
Next, click in the first cell of the Trim column. On the Formulas tab, click the Text dropdown menu in the Functions group and select TRIM.
After the dialog box appears, click on the cell that contains the text you would like to remove spaces from to complete the function. Click OK.
Copy the function to the remaining cells in the column to apply the function to the rest of the Name cells. You can see that the TRIM function cleaned up the extra spaces and the character counts are now all the same except for the “JoeSmith” that did not have any spaces between the first and last name. Note that TRIM will not ADD spaces.
Now you just need to replace your original data with your clean data. To do this, select all of the cells in the Trim column, then hit CTRL+C to copy the cells to the clipboard.
Then, select the first cell in the Name column and right-click. Select Paste Values under the Paste Options heading. Now you can Delete the Trim helper column and your Name column is clean.
Use a Formula to Remove All Spaces
If you need to prepare your data for import into a database (such as MS Access) or have a combination of number and text data that simply needs ALL spaces removed, you can use a formula instead of the TRIM function to remove ALL blanks in a cell.
Create a temporary, or helper, column for the corrected data at the end of your spreadsheet like you did for the TRIM function. In this example the column is called No Spaces.
Next, click in the first cell of the Trim column. On the Formulas tab, click the Text dropdown menu in the Functions group and select SUBSTITUTE.
When the dialog box appears:
- Click on the first cell in the column that you need changed
- In the “Old_text” field, enter the character you want replaced separated by quotes. To remove spaces this would then be ” “.
- In the “New_text” field, enter the character you want to appear instead. Since we want NO characters to replace the space, type “”.
Click OK to apply the formula to the cell.
Copy the formula to the remaining cells in the helper column. Then copy the data to the data column as you did before using CTRL-C and Paste Options.
Bonus Hint!
You can also remove spaces using the Find/Replace feature in Excel. Click CTRL+F to open the Find dialog box, then click theReplace tab. Enter one space ” ” in the Find what: field and leave the Replace with: field empty to remove all spaces.
Warning: This technique will search for and replace ALL spaces in the entire sheet or selection area. Use carefully so that you don’t remove spaces you still want or need!
With these tips, you can clean up data entered from multiple sources and ensure that it is consistent and less prone to unintended duplication. Use these tips to remove spaces when you want to:
- Find unintended duplicates in your data
- Prepare someone else’s data before you import it into your own spreadsheets
- Prepare your data for export into a database
When do you find the need to find and remove unwanted spaces?
