How to Remove Extra Spaces and Unwanted Characters in Excel 365
Question details
The user wants to know which functions in Excel 365 are most effective for removing leading, trailing, duplicate, non-printable, and non-breaking spaces from text.
- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Cleaning up messy text data, often imported or copied from web pages, containing unwanted spacing or hidden HTML characters.
- Observed behavior
- Text contains irregular extra spaces, non-printable characters, or non-breaking spaces that interfere with data analysis, formulas, and formatting.
Add a helper column next to your messy text data so you can apply the cleanup formulas without immediately overwriting your original source data.
Remove Regular Extra Spaces Using TRIM
Use this method to quickly strip leading, trailing, and duplicate regular spaces between words.
The standard TRIM function is designed to remove the 7-bit ASCII space character (value 32) from text. It is the most common tool for basic text cleanup.
Click an empty cell adjacent to the cell containing your messy text (e.g., cell B1 if your text is in A1).
Type =TRIM(A1) into the formula bar and press Enter.
Click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of your data.
Eliminate Non-Printable and Non-Breaking Spaces
Ideal for cleaning text copied from web pages or imported from external databases that contain hidden characters.
Combine Functions for Comprehensive Cleanup
The most robust approach to automatically clean messy data with multiple spacing and character issues in a single step.
Clean Text Data Effortlessly in WPS Spreadsheet
WPS Spreadsheet provides full support for advanced text-cleaning functions like TRIM, CLEAN, and SUBSTITUTE. You can process messy, imported data quickly without paying for expensive software subscriptions.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the messy text data.
- 2. Select an empty cell: Click on a blank cell adjacent to the data you need to clean.
- 3. Input the cleanup formula: Type =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))) into the cell and hit Enter.
- 4. Apply to all rows: Drag the small square at the bottom right of the cell downwards to apply the formula to the rest of the list.
- 5. Paste as values: Copy the results, right-click your original column, and select 'Paste as Values' to finalize your clean data.
Frequently Asked Questions
Why doesn't the TRIM function remove all spaces in Excel?
The standard TRIM function only recognizes and removes the standard space character (ASCII 32). It cannot detect non-breaking spaces (ASCII 160) which frequently appear when copying text from websites. You must use the SUBSTITUTE function to convert those to regular spaces first.
What is a non-breaking space and how do I reference it in formulas?
A non-breaking space is a special character used in web design (HTML ) to prevent an automatic line break at its position. In Excel formulas, it is referenced using the code CHAR(160).
Can I remove unwanted spaces without using formulas?
Yes, you can use the Find and Replace tool (Ctrl+H). To remove regular double spaces, type two spaces in the 'Find what' box and one space in the 'Replace with' box. For non-breaking spaces, copy the hidden space directly from the cell text and paste it into the 'Find what' box, then replace it with a regular space.
