In Excel, use =TRIM(A2) to remove leading and trailing spaces and reduce repeated spaces between words to one. If you want to delete every regular space, including spaces inside words, use =SUBSTITUTE(A2," ","") instead.
Choose the formula for the kind of space removal you need
“Remove spaces” can mean two different things: tidy the spacing around text while keeping words readable, or delete every space character. The right formula depends on which result you want. For example, cleaning a person’s name usually means keeping one space between first and last names; cleaning an ID code may mean removing all spaces so the code is contiguous.
- Trim extra spacing but keep word breaks: use
TRIM. - Delete all ordinary spaces: use
SUBSTITUTE. - Handle nonbreaking spaces or other unwanted characters: combine functions, as described below.
These formulas return cleaned text in a separate cell. They do not directly change the source cell, which makes it possible to compare the original and the result before replacing anything.
How TRIM works in Excel
With the text to clean in A2, enter =TRIM(A2) in another cell. TRIM removes spaces at the start and end of the text and converts multiple ordinary spaces between words into a single space. If A2 contains three spaces before “Maya,” several spaces between “Maya” and “Chen,” and two spaces after “Chen,” the result is Maya Chen.
This behavior makes TRIM useful for names, addresses, descriptions, and other text where word boundaries matter. It does not remove every space from the text: one space between words remains. It also does not remove every kind of whitespace character. Excel’s TRIM function is designed around the ordinary space character, code 32, so text copied from a website or another system may need additional cleaning.
For a column of values, put the formula beside the first source value, then fill it down. In Microsoft 365 for Windows, select the formula cell and drag its fill handle down the column, or double-click the fill handle to copy the formula alongside adjacent data. The reference A2 adjusts to A3, A4, and so on. Check several results, especially rows with unusual or pasted text, before relying on the cleaned column.
Delete every ordinary space with SUBSTITUTE
With the text to clean in A2, use =SUBSTITUTE(A2," ","") to replace each ordinary space with nothing. For example, if A2 contains AB 123 CD, the result is AB123CD. The formula removes both spaces, including the one between the letters and digits. This is often suitable for compact codes, but it would turn “Maya Chen” into “MayaChen,” so it is not the right choice when readability depends on word breaks.
SUBSTITUTE searches for the exact text you specify and replaces every matching occurrence by default. In this formula, the first quoted argument is one space, and the second is an empty string. If you need to replace only a particular occurrence, SUBSTITUTE can also take an optional instance number, but that is rarely necessary when cleaning all spaces.
Use SUBSTITUTE when deleting spaces is genuinely part of the desired data format, not simply because a cell looks untidy. Removing spaces from product codes, account references, or imported identifiers can make matching easier, but it can also change the value’s meaning. Keep the original column until you have confirmed the expected format.
Remove nonbreaking spaces from copied text
A frequent reason TRIM appears not to work is that the text contains a nonbreaking space rather than a normal space. Nonbreaking spaces are common in text copied from web pages, PDFs, and some exported reports. They may look exactly like ordinary spaces, but TRIM does not treat character 160 as the ordinary space character it is built to trim.
With the copied text in A2, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to convert nonbreaking spaces to ordinary spaces and then trim the result. If A2 contains “North”, a nonbreaking space, and “Road,” the result is North Road. The formula also removes leading and trailing ordinary spaces and reduces repeated ordinary spaces to one.
CHAR(160) represents the character commonly used for a nonbreaking space in this situation. Replacing it with a normal space before applying TRIM is important: replacing it with an empty string could join two words that were meant to remain separate. If you are unsure what kind of space is present, try this combined formula on a copy of the data and compare the output with the original.
Handle tabs and other nonprinting characters
Some imported text contains tabs, line breaks, or other control characters in addition to spaces. CLEAN removes many nonprinting ASCII control characters, including common tab and line-break characters. It does not remove nonbreaking spaces, so combining CLEAN with SUBSTITUTE and TRIM can address several frequent problems in one formula.
With the source text in A2, use =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) to replace nonbreaking spaces, remove characters CLEAN recognizes, and then trim ordinary spaces. For instance, this can turn a value containing a nonbreaking space between “North” and “Road,” plus extra spaces at either end, into North Road.
There is an important limitation: CLEAN removes recognized control characters; it does not necessarily turn every removed character into a space. If a tab separates two words, removing the tab can join them. To preserve a word boundary when the unwanted character is a tab, replace the tab with a normal space first.
With a tab-separated value in A2, use =TRIM(SUBSTITUTE(A2,CHAR(9)," ")). CHAR(9) identifies a tab. For example, if A2 contains “North,” then a tab, then “Road,” the result is North Road. For text that may contain both tabs and nonbreaking spaces, use =TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(9)," ")). This replaces both characters with ordinary spaces before TRIM standardizes the spacing.
Apply the formula to a full column
For a list with source text in A2:A100, put the chosen formula in B2, then copy it down through B100. The formulas use relative references, so the row reference changes as the formula is filled. If the range is an Excel table, entering the formula in the first row of a new column typically fills the calculated column automatically.
- Add a new column beside the original data and give it a clear heading, such as “Cleaned text.”
- Enter the formula in the first data row, using the matching source cell.
- Fill the formula down, then inspect representative rows and any rows that look unusual.
- Use the cleaned column for comparisons, lookups, sorting, or reporting as needed.
Formulas recalculate when their source cells change, so the cleaned results remain linked to the original data. This is helpful when a source list is updated regularly. If you need a fixed result that no longer depends on the source, copy the cleaned cells and paste values into the intended destination. In Excel for Microsoft 365 on Windows, use Home > Paste > Values from the Paste menu. On Mac, use the Paste Values option in the Home tab’s Paste menu; menu presentation can vary slightly by Excel release.
Formula results are text, not a direct cleanup of the source
A formula that returns cleaned text does not overwrite the cell it refers to. If A2 contains the original and B2 contains =TRIM(A2), A2 stays unchanged and B2 displays the result. This is safer than editing a source list in place, but it means you may need to paste values if another system requires the cleaned result as fixed data.
Also, removing spaces can affect more than appearance. If values are used as identifiers, deleting a space might cause two distinct entries to become identical. Conversely, inconsistent spacing can make text comparisons or lookups fail even when entries look alike. Decide whether the space is formatting noise or meaningful content before standardizing it.
Common problems and checks
- TRIM leaves a gap that will not go away: the character may be a nonbreaking space, tab, or another special character. Try the relevant CHAR-based replacement formula above.
- Words run together after cleaning: a formula may have deleted or removed a tab or other separator. Replace that character with a regular space before applying TRIM.
- Numbers look different after cleanup: text functions return text. A value that looks numeric may no longer behave like a number in calculations until it is converted appropriately.
- The formula shows as text instead of calculating: check that the cell is not formatted as Text, and confirm the entry begins with an equals sign. If needed, change the format to General and re-enter the formula.
- The source data changed unexpectedly: a formula alone should not alter its referenced cell. Check whether you pasted values over the original column or edited the source directly.
Regional settings can affect formula argument separators. The examples here use commas, as specified for Excel’s standard English-language formula syntax; if Excel rejects a formula, your installation may expect semicolons as separators. Do not change the quotation marks around the space or empty string: those identify the text being replaced.
Frequently Asked Questions
What is the simplest Excel formula to remove extra spaces?
With the text in A2, use =TRIM(A2). It removes leading and trailing ordinary spaces and reduces multiple spaces between words to one.
How do I remove all spaces from a cell in Excel?
With the text in A2, use =SUBSTITUTE(A2," ",""). It deletes every ordinary space, including spaces between words.
Why does TRIM not remove spaces copied from a website?
The copied character may be a nonbreaking space, often represented by CHAR(160), rather than a regular space. With the text in A2, try =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Can I remove spaces without a formula?
For a one-time edit, Find and Replace can replace a normal space with nothing, but that removes spaces inside words as well as at the ends. Use it only when deleting every ordinary space is intended; a formula is easier to review and preserves the source until you choose to replace it.
How do I keep the cleaned result but remove the formula?
Copy the formula results and paste values into the destination. In Excel for Microsoft 365 on Windows, choose Home > Paste > Values. Keep a copy of the original data if you may need to verify or recover it later.
Related
- table() in RStudio: How It Counts and Displays Data
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel VAT Formula at 20%: Add or Remove VATIf A2 contains a price before VAT, calculate VAT at 20% with =A2*20%; add that VAT to the…
- Python pandas Excel Functions: Read and Write FilesPython pandas handles Excel files mainly with read_excel() to load workbook data into a DataFrame and DataFrame.to_excel() to…
- Conditional Formatting in Excel: If-Then Color RulesYes. In Excel, select the cells you want to format, then choose Home > Conditional Formatting and create…
- Count Unique Values Across Excel Sheets with One FormulaTo count distinct nonblank values across worksheets in Excel for Microsoft 365, stack the ranges and count the…
- Excel Formula for High and Low Values: MIN and MAXIn Excel, use =MIN(B2:B10) to find the lowest number in a range and =MAX(B2:B10) to find the highest.…
- Extract Middle Text in Excel: Formulas and ExamplesTo extract text from the middle of a cell in Excel, use MID: with the source text in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.