How to Remove Characters From Text in Excel

To remove characters in Excel, choose the method by what you know about the characters: what they are, or where they are. SUBSTITUTE removes a specific character wherever it appears, such as every dash in a phone number. LEFT, RIGHT and REPLACE remove characters by position, such as the first three or the last two. This guide shows each method with worked examples, plus Find and Replace for quick one-off fixes.

Which method should you use?#

Start with the question "do I know which character, or where it is?":

Spaces are a special case with their own tricks, so they have a separate guide: how to remove spaces in Excel.

Remove a specific character with SUBSTITUTE#

SUBSTITUTE replaces text with other text. Replace a character with nothing, two double quotes, and it disappears:

=SUBSTITUTE(A2,"-","")

With "555-123-4567" in A2, the result is 5551234567. SUBSTITUTE has three required parts: the text, what to find, and what to put instead. It changes every copy it finds.

To remove only one copy, use the optional fourth argument, the instance number. =SUBSTITUTE(A2,"-","",1) removes only the first dash: 555123-4567. The SUBSTITUTE function guide covers all four arguments.

SUBSTITUTE is case-sensitive: =SUBSTITUTE(A2,"x","") leaves capital X untouched. To remove both, nest two SUBSTITUTEs, one for each case.

Remove several characters at once#

To remove more than one kind of character, put SUBSTITUTE inside SUBSTITUTE. Each one cleans the result of the one inside it. For "(555) 123-4567":

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")," ","")

Work from the inside out: the first SUBSTITUTE removes "(", the next removes ")", then "-", then the space. The result is 5551234567.

Nested formulas get long, so build them one layer at a time and check the result after each layer. In Microsoft 365, you can also remove every non-digit with one formula:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

MID and SEQUENCE split the text into single characters, *1 turns digits into numbers and everything else into an error, IFERROR blanks the errors, and TEXTJOIN joins the digits back together. The result is again 5551234567.

Remove a word or a longer piece of text#

SUBSTITUTE isn't limited to single characters. It can remove a whole word or phrase, such as "Ltd" from company names:

=TRIM(SUBSTITUTE(A2,"Ltd",""))

For "Redwood Paper Ltd", SUBSTITUTE removes "Ltd" and leaves a space at the end, and TRIM tidies it up. The result is Redwood Paper. Be careful with short words: removing "Co" would also cut it from the middle of names like "Cooper". Include a space before the word, such as " Co", to make the match more specific.

Remove the first or last characters#

When the characters you want to remove are always in the same place, work by position. LEN counts the characters in the cell, and you keep everything except the part you don't want.

To remove the first three characters of "ID-48213" (8 characters long):

=RIGHT(A2,LEN(A2)-3)

LEN(A2) is 8, so RIGHT keeps the last 8 - 3 = 5 characters: 48213. =REPLACE(A2,1,3,"") does the same by replacing the first three characters with nothing. The guide to removing the first characters covers more variations.

To remove the last two characters, such as "kg" from "125kg":

=LEFT(A2,LEN(A2)-2)

The result is 125, as text. Wrap it in VALUE to get a number you can add up: =VALUE(LEFT(A2,LEN(A2)-2)). Counting characters is explained further in how to count characters and words.

Remove everything before or after a character#

To remove everything after a marker, keep only the text before it. For "Blue Shirt - Large":

=TEXTBEFORE(A2," - ")

The result is Blue Shirt. In older Excel, find the marker's position and take everything to its left: =LEFT(A2,FIND(" - ",A2)-1).

To remove everything before a marker, use TEXTAFTER: =TEXTAFTER(A2," - ") returns Large. TEXTBEFORE and TEXTAFTER need Microsoft 365 or Excel 2024. If you need the text between two markers instead, see extracting text between characters.

Remove characters with Find and Replace#

For a one-off clean-up, Find and Replace removes characters without any formula:

1. Select the cells.
2. Press Ctrl+H (Mac: ⌃H).
3. Type the character in Find what and leave Replace with empty.
4. Click Replace All.

Find and Replace understands wildcards. To remove brackets and everything inside them, such as " (old)" in product names, find (*) and replace with nothing. An asterisk stands for any characters. To find a real asterisk, type ~*.

Unlike a formula, Find and Replace changes the original cells, so save a copy or be ready to undo (Windows: Ctrl+Z; Mac: ⌘Z). Select only the cells you want to change first; with a single cell selected, Excel searches the whole sheet.

Remove invisible characters#

Some characters can't be seen but still break formulas. CLEAN removes non-printing characters, such as line breaks, from imported text: =CLEAN(A2). Non-breaking spaces from web pages are character 160, and SUBSTITUTE can remove them: =SUBSTITUTE(A2,CHAR(160),"").

If a character won't go away, check its character number with the CODE function, or copy it straight from the cell into your formula. Many "dashes" are actually longer en or em dashes, which are different characters. For a full clean-up routine, see the guide to cleaning messy data.

Practise it#

Removing unwanted characters is one of the most common data-cleaning tasks in real jobs and in tests. Practise cleaning text in a live spreadsheet: you type SUBSTITUTE, TRIM and CLEAN formulas, and each result is checked instantly. Then try the data analyst Excel test to clean data under time pressure.