Title: Remove Extra Spaces from Excel Data | Pryor Learning Solutions
Headings: Remove Extra Spaces from Excel Data
Remove Extra Spaces from Excel Data
Use the Trim Function
Use a Formula to Remove All Spaces
Bonus Hint!
Tepring Crocker

Content: Option 2
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. Step 1
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. Step 2
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. Step 3
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 “”.