Excel does not have one universal formula that removes every type of unwanted character. The best method depends on what your text contains.
For example, CLEAN is useful for removing non-printing characters, while TRIM handles extra standard spaces. If you want to remove a particular symbol, such as @, #, or -, SUBSTITUTE is usually the better choice.
Before cleaning your data, it is a good idea to make a copy of the original column. This way, you can compare the results and restore the original information if necessary.
Let’s learn everything in detail!
Remove Special/Unwanted Characters from Text in Excel
To remove special or unwanted characters from text in Excel, follow these steps:
- Select an empty cell next to the text you want to clean.
- Use the CLEAN function to remove non-printing characters from the text.
- Use the TRIM function to remove extra spaces between words and at the beginning or end of the text.
- Use SUBSTITUTE to remove specific unwanted characters, such as hyphens, commas, or symbols.
- Combine Excel functions when the text contains multiple types of unwanted characters.
- Copy the cleaned results and use Paste Values if you want to replace the original text.
1: Identify the Special or Unwanted Characters
The first step is to determine which characters you want to remove. Not every symbol is unwanted in every situation. For example, a hyphen may be unnecessary in a product code, but it may be important in a ZIP code or phone number. Similarly, removing spaces from a person’s full name could make the name harder to read.
Look closely at the text in your worksheet. Check whether the problem involves extra spaces, line breaks, punctuation, symbols, or invisible characters. You can also click a cell and look at the formula bar to see whether the text contains unexpected spacing or line breaks.
For example, suppose cell A2 contains the following text:
John@Smith#2026
If you want to remove the @ and # characters while keeping the letters and numbers, you need a formula that targets those specific symbols.
If the text contains unwanted spaces, the solution will be different. Identifying the problem first helps you choose the right Excel function.
2: Remove Non-Printing Characters Using CLEAN
The CLEAN function is useful when text contains non-printing characters. These characters are often introduced when you copy information from websites, emails, reports, or other applications.
Non-printing characters may include certain line breaks, tabs, and control characters that are not visible in a normal worksheet. They can interfere with text comparisons and other formulas.
To use CLEAN, select an empty cell next to the original text. If the text you want to clean is in cell A2, enter the following formula in B2:
=CLEAN(A2)
Press Enter. Excel will return the text without the non-printing characters that CLEAN can remove.
For example, if A2 contains text with an unwanted line break, the formula may return the same text on one line.
You can apply the formula to more rows by selecting the cell containing the formula and dragging the fill handle downward. Excel will adjust the cell references automatically.
Remember that CLEAN is not designed to remove every visible symbol. If your text contains characters such as @, $, %, or #, you will generally need another method.
3: Remove Extra Spaces Using TRIM
Extra spaces are among the most common text-cleaning problems in Excel. They can appear before a word, after a word, or between words. In some cases, these spaces cause two apparently identical text values to behave differently in comparisons.
The TRIM function removes leading and trailing standard spaces and reduces repeated spaces between words to a single space.
Suppose cell A2 contains:
John Smith
To clean the spacing, select an empty cell and enter:
=TRIM(A2)
Press Enter. Excel will return:
John Smith
This is especially useful when cleaning customer names, addresses, product descriptions, and imported lists.
However, TRIM does not remove every type of whitespace. For example, text copied from a website may contain a non-breaking space, which is different from a standard space. If TRIM does not solve the problem, you can combine it with SUBSTITUTE.
For example:
=TRIM(SUBSTITUTE(A2,CHAR(160),” “))
This formula replaces non-breaking spaces with standard spaces and then removes unnecessary spacing.
4: Remove Specific Symbols Using SUBSTITUTE
If you want to remove a particular character from your text, the SUBSTITUTE function is one of the simplest options.
SUBSTITUTE searches for a specific piece of text and replaces it with another value. If you replace the unwanted character with an empty string, Excel removes it.
For example, suppose A2 contains:
ABC-123-XYZ
To remove all hyphens, enter the following formula in another cell:
=SUBSTITUTE(A2,”-“,””)
The result will be:
ABC123XYZ
The two quotation marks with nothing between them represent an empty string.
You can use the same method to remove other characters. For example, to remove an at symbol from a text value, use:
=SUBSTITUTE(A2,”@”,””)
To remove a dollar sign, use:
=SUBSTITUTE(A2,”$”,””)
This method is helpful when cleaning product codes, invoice numbers, usernames, and imported text.
SUBSTITUTE is case-sensitive when matching letters. However, for most punctuation and symbol-removal tasks, this distinction will not matter.
5: Remove Multiple Unwanted Characters
Sometimes your text contains several unwanted characters. For example, a cell might contain:
John@Smith#2026!
If you want to remove @, #, and !, you can nest multiple SUBSTITUTE functions.
Enter this formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,”@”,””),”#”,””),”!”,””)
Excel processes the functions from the inside outward. The first SUBSTITUTE removes @, the second removes #, and the third removes !.
The result will be:
JohnSmith2026
If you want to preserve spaces between words, make sure you remove only the symbols you do not need.
For example, if A2 contains:
John@ Smith# 2026!
the same formula will return:
John Smith 2026
This approach works well for a small number of known unwanted characters. If you need to remove many different symbols, a more advanced formula may be easier to maintain.
6: Remove Unwanted Characters Using TEXTJOIN and MID
If you want to keep only letters, numbers, or a selected group of characters, you can use a more advanced formula. This method is useful when the unwanted characters vary from cell to cell.
In newer versions of Excel that support dynamic arrays, you can combine MID, SEQUENCE, ISNUMBER, and TEXTJOIN to examine individual characters and keep only the ones you want.
For example, the following formula keeps letters, numbers, and spaces from the text in A2:
=LET(txt,A2,chars,MID(txt,SEQUENCE(LEN(txt)),1),TEXTJOIN(“”,TRUE,IF((ISNUMBER(SEARCH(chars,”abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 “))),chars,””)))
This formula examines the text one character at a time and joins the characters that match the permitted set.
However, this is a more advanced approach and may not be suitable for every Excel version. It is also important to remember that the formula above is designed for basic English letters, numbers, and standard spaces. It may remove accented letters or characters from other writing systems.
For beginners, it is usually easier to start with CLEAN, TRIM, and SUBSTITUTE. Use this advanced method when you need more control over which characters remain.
7: Remove Unwanted Characters with Find and Replace
If you only need to remove a specific character from a selected range, Excel’s Find and Replace feature can be faster than writing a formula.
First, select the cells or column you want to clean. Then go to Home > Find & Select > Replace. You can also press Ctrl + H to open the Find and Replace dialog box.
In the Find what field, enter the unwanted character. For example, if you want to remove hyphens, enter -.
Leave the Replace with field empty. This tells Excel to replace the unwanted character with nothing.
Select Replace All. Excel will remove the matching characters from the selected range.
For example, text such as ABC-123-XYZ will become ABC123XYZ.
Be careful when using Replace All. If you do not select a specific range, Excel may replace matching characters throughout the worksheet or workbook, depending on the search settings. Always review the results before continuing.
Copy the Cleaned Text and Replace the Original Data
When you use formulas to clean text, Excel creates a new result rather than changing the original cell. Once you are satisfied with the cleaned data, you may want to replace the original text.
Suppose your original text is in column A and your cleaned results are in column B. Select the cleaned cells in column B and press Ctrl + C.
Next, select the first cell in the original column. Go to Home > Paste > Paste Values, or right-click and choose the option for pasting values only.
This replaces the formulas with the cleaned text. You can then remove the helper column if you no longer need it.
Before deleting anything, compare the original and cleaned columns. Make sure that important information, such as spaces, hyphens, decimal points, or other meaningful symbols, has not been removed accidentally.
Check the Cleaned Data
After removing unwanted characters, it is important to verify that your data is correct. A formula may successfully remove unwanted symbols but also remove characters that were meaningful.
For example, a product code such as AB-123 may need its hyphen, while a text value such as John@Smith may not.
You can use Excel functions such as LEN to compare the length of the original and cleaned text. For example:
=LEN(A2)
and:
=LEN(B2)
These formulas show the number of characters in each cell. A difference in length may be expected if you removed unwanted characters, but a large or unexpected difference should be investigated.
You can also compare the original and cleaned columns visually. If you are working with a large dataset, use filters, conditional formatting, or comparison formulas to locate unusual results.
The goal is not simply to remove as many characters as possible. The goal is to create clean, consistent text without damaging the information.
FAQs
1. Which Excel function removes special characters?
The best function depends on the type of character. CLEAN removes certain non-printing characters, while SUBSTITUTE removes specific characters such as hyphens, symbols, and punctuation. TRIM removes extra standard spaces.
2. How do I remove all special characters but keep letters and numbers?
You can use an advanced formula involving MID, SEQUENCE, and TEXTJOIN to keep only selected characters. For simple cleanup, use SUBSTITUTE to remove known symbols. The correct formula depends on which characters you want to preserve.
3. Why does CLEAN not remove all special characters?
CLEAN is designed to remove certain non-printing characters, not all visible symbols. Characters such as @, #, $, and % usually require SUBSTITUTE or another text-cleaning method.
4. How do I remove unwanted spaces in Excel?
Use the TRIM function. For example:
=TRIM(A2)
If the spaces come from copied web content, you may also need to replace non-breaking spaces using SUBSTITUTE and CHAR(160).
5. Can I remove unwanted characters without using a formula?
Yes. Use Find and Replace by pressing Ctrl + H. Enter the unwanted character in the Find what field, leave Replace with empty, and select Replace All. Be sure to select the correct range first.
6. Will removing special characters change my original data?
Using formulas in a separate column does not change the original data. However, Find and Replace changes the selected cells directly. Always keep a backup or copy of your original information before making large changes.
7. Can I remove line breaks from Excel cells?
Yes. You can often remove line breaks using CLEAN. You can also use SUBSTITUTE with CHAR(10) to replace line breaks with a space or an empty string, depending on your needs.
Bottom Lines
Removing special and unwanted characters from Excel text is an important part of keeping your spreadsheet accurate and organized. The right method depends on the type of data you are cleaning.
Use CLEAN for non-printing characters, TRIM for extra standard spaces, and SUBSTITUTE for specific unwanted symbols. When you need to remove several characters, you can combine formulas or use Find and Replace for a quicker solution.
For more advanced cleaning, Excel offers formulas that let you keep only selected letters, numbers, and spaces. Whatever method you choose, always review the results before replacing your original data. A careful cleanup process helps you maintain reliable information and makes your Excel worksheets easier to use.