How to Remove Characters from a Cell in Excel

To remove a character from cells in Excel, select the cells, press Ctrl+H, type the character in the Find what box, leave the Replace with box empty, and select Replace All. If you would rather keep the original data untouched, use a formula in a new column instead, such as =SUBSTITUTE(A2,”-“,””), which returns the text from A2 with every hyphen removed.

Applies to: Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016 on Windows; Mac differences are noted where Microsoft documents them. Checked against Microsoft Support on October 7, 2026.

Pick the right method first

“Removing characters” covers several different jobs, and Excel has a different tool for each one. Decide which of these describes your data before you start, because the wrong tool either does nothing or removes more than you wanted.

What you want to remove	Best tool	Changes the original cells?
One specific character or word, everywhere it appears (hyphens, a currency symbol, a unit)	Find and Replace, or the SUBSTITUTE function	Find and Replace: yes. SUBSTITUTE: no
Only the first, second or third occurrence of a character	SUBSTITUTE with its fourth argument	No
A fixed number of characters at the start or end	REPLACE, RIGHT or LEFT with LEN	No
Characters in the middle, at a known position	REPLACE	No
Everything before or after a marker such as a hyphen	TEXTBEFORE or TEXTAFTER (Microsoft 365 and Excel 2024)	No
Extra spaces or invisible characters from imported data	TRIM, CLEAN and SUBSTITUTE	No
A pattern that is easy to show but hard to describe	Flash Fill	No

Before any of them, save a copy of the workbook. Microsoft’s own data-cleaning advice starts with a backup copy, and for formula methods it recommends working in a new column next to the original rather than on top of it.

Remove a specific character with Find and Replace

This is the fastest route when the same character should disappear from many cells at once. It edits the cells directly, so there is no formula to maintain afterward.

1. Select the cells, column or row that contains the unwanted character. Selecting first keeps your attention on the data you mean to change.
2. Press Ctrl+H. You can also go to Home > Editing > Find & Select > Replace. The Find and Replace dialog opens.
3. In the Find what box, type the character or text you want to remove, for example a hyphen.
4. Leave the Replace with box empty. Replacing something with nothing is what deletes it.
5. Select Replace All to remove every match in one pass, or select Replace to step through the matches one at a time and decide on each.
6. Look over the cells. If the result is not what you expected, press Ctrl+Z right away to undo it.

On a Mac, Microsoft lists Control+H for Replace, or Home > Find & Select > Replace. Microsoft’s full reference is Find or replace text and numbers on a worksheet.

Options that change what gets removed

Select Options>> in the dialog to see extra settings. Three of them matter for removing characters:

• Within lets you choose Sheet or Workbook. Check this before you select Replace All, because Workbook reaches every sheet in the file.
• Match case limits the search to the exact upper or lower case you typed. Turn it on to remove a capital “X” but keep a lowercase “x”.
• Match entire cell contents only matches cells that contain nothing but what you typed. Leave it off when the character sits inside longer text.

Removing an asterisk or question mark

Find and Replace treats two characters as wildcards. A question mark stands for any single character, and an asterisk stands for any number of characters. If you type a lone asterisk in Find what and select Replace All, Excel matches the whole contents of each cell, not just the asterisks.

To remove a real asterisk, question mark or tilde, put a tilde in front of it. Type ~* to find asterisks, ~? to find question marks, and ~~ to find tildes.

The wildcards are also useful on purpose. Typing a hyphen followed by an asterisk in Find what, with Replace with empty, matches the hyphen and everything after it, so “4471-kg” becomes “4471”. Try it with Replace on one cell before you use Replace All.

Remove a character with the SUBSTITUTE function

SUBSTITUTE is the formula version of Find and Replace. It leaves the original cell alone and returns a cleaned copy, which makes it the safer choice for part numbers, IDs and anything you may need to check against the source later. Its syntax is SUBSTITUTE(text, old_text, new_text, [instance_num]).

1. Insert a new column next to your data. This “helper column” holds the cleaned version.
2. In the first cell of the helper column, type =SUBSTITUTE(A2,”-“,””) and press Enter. A2 is the cell to clean, “-” is the character to remove, and the two quotation marks with nothing between them mean “replace it with nothing”. If A2 contains AB-12-34, the result is AB1234.
3. Fill the formula down the column so that every row has a cleaned value.
4. Compare a few rows against the original, including the shortest and longest entries.

Type the old text exactly as it appears in the cell, including capital and lowercase letters, so the formula finds it.

Remove only one occurrence

The optional fourth argument tells SUBSTITUTE which occurrence to change. According to Microsoft’s SUBSTITUTE reference, if you supply it, only that instance is replaced; if you leave it out, every occurrence is replaced.

• =SUBSTITUTE(A2,”-“,””,1) removes only the first hyphen: AB-12-34 becomes AB12-34.
• =SUBSTITUTE(A2,”-“,””,2) removes only the second hyphen: AB-12-34 becomes AB-1234.

Remove several different characters

Put one SUBSTITUTE inside another. Each layer removes one character and hands the result to the next layer. To strip both parentheses from (555) 0142, use =SUBSTITUTE(SUBSTITUTE(A2,”(“,””),”)”,””), which returns 555 0142. You can keep nesting for a third or fourth character, but the formula becomes hard to read, so past three or four characters it is usually easier to run Find and Replace a few times on a copy of the column.

Remove a whole word or unit

The old text does not have to be a single character. =SUBSTITUTE(A2,” kg”,””) turns “12.5 kg” into “12.5”. Include the space inside the quotation marks so that it is removed along with the unit.

Remove characters by position

Use these formulas when you know where the unwanted characters are, not what they are, for example “the first three characters of every code”. All of them return new text in a helper column and do not edit the original cell.

Remove the first characters

=REPLACE(A2,1,3,””) removes three characters starting at position 1. If A2 contains ID-4471, the result is 4471. The syntax is REPLACE(old_text, start_num, num_chars, new_text): the text, the position to start at, how many characters to replace, and what to put there. Empty quotation marks put nothing there. Change the 3 to the number of characters you want gone.

An older pattern that does the same job is =RIGHT(A2,LEN(A2)-3). LEN counts the characters in the cell, and RIGHT keeps that count minus three from the right-hand end.

Remove the last characters

=LEFT(A2,LEN(A2)-3) keeps everything except the last three characters. If A2 contains 4471-kg, the result is 4471.

Microsoft’s reference for LEFT and RIGHT states that the number of characters must be greater than or equal to zero. That means a cell with fewer than three characters gives this formula a negative number and it returns an error instead of text. Sort or scan for short and blank entries before you rely on the result.

Remove characters from the middle

REPLACE handles this too. =REPLACE(A2,4,4,””) removes four characters starting at position 4, so INV2024-0091 becomes INV-0091. Microsoft’s REPLACE reference has more examples. The quick way to keep SUBSTITUTE and REPLACE straight: SUBSTITUTE looks for specific text, REPLACE works on a specific position.

Remove everything before or after a character

In Excel for Microsoft 365 and Excel 2024, two newer functions cut text at a marker without any counting.

• =TEXTBEFORE(A2,”-“) returns the text in front of the first hyphen. 4471-kg becomes 4471.
• =TEXTAFTER(A2,”-“) returns the text that follows the first hyphen. ID-4471 becomes 4471.

Both accept an optional third argument for which occurrence to use, and a negative number counts from the end of the text. =TEXTBEFORE(A2,”-“,-1) cuts at the last hyphen instead of the first.

If a cell does not contain the marker, both functions return #N/A by default. The last argument, if_not_found, lets you choose what appears instead. =TEXTBEFORE(A2,”-“,,,,A2) returns the original text for any row that has no hyphen. Microsoft lists these two functions for Excel for Microsoft 365 and Excel 2024 only, so in Excel 2021, 2019 or 2016 use the wildcard Find and Replace described above or a LEFT formula. The full argument list is in Microsoft’s TEXTBEFORE reference.

Remove extra spaces and invisible characters

Data pasted from a website, a PDF or another program often carries characters you cannot see. They make two cells that look identical fail to match, and Microsoft notes that they can cause unexpected results when you sort, filter or search.

• =TRIM(A2) removes all spaces from text except single spaces between words. It deals with leading spaces, trailing spaces and doubled spaces in one step. It does not remove every space.
• =SUBSTITUTE(A2,” “,””) removes every ordinary space, including the ones between words. Use it for codes and phone numbers, not for names or sentences.
• =CLEAN(A2) removes the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31), which is the kind of low-level code that often arrives with imported files.
• =TRIM(CLEAN(A2)) does both: it removes those nonprinting characters and then tidies the spaces.

When TRIM does not remove a space

TRIM was designed for the standard space character, value 32. Web pages commonly use a second kind, the nonbreaking space, which has the value 160 and is written in HTML as  . Microsoft states that TRIM by itself does not remove it.

The fix is to swap the nonbreaking spaces for ordinary ones and then trim: =TRIM(SUBSTITUTE(A2,CHAR(160),” “)). CHAR(160) produces the character with that number, SUBSTITUTE replaces each one with a normal space, and TRIM cleans up what is left.

CLEAN has a similar gap. Microsoft lists additional nonprinting characters with the values 127, 129, 141, 143, 144 and 157 that CLEAN does not remove on its own. The same SUBSTITUTE and CHAR pattern handles them: =SUBSTITUTE(A2,CHAR(127),””) removes character 127. Microsoft’s Top ten ways to clean your data covers this in more depth, and our guide to removing a space before text in Excel walks through the leading-space case on its own.

Let Flash Fill copy your pattern

Flash Fill needs no formula. You type the result you want for the first row or two, and Excel works out the pattern and fills in the rest. It suits removals that are easy to show but awkward to describe, such as dropping an area code or a prefix that varies in length.

1. In the empty column next to your data, type the cleaned version of the first entry and press Enter.
2. Start typing the cleaned version of the second entry. Excel senses the pattern and shows a preview for the remaining rows.
3. Press Enter to accept the preview.

If no preview appears, select the next empty cell in that column and go to Data > Flash Fill, or press Ctrl+E. Microsoft notes that Flash Fill may simply not be turned on; the setting is Automatically Flash Fill, under Advanced > Editing Options in Excel’s options. See Using Flash Fill in Excel.

Flash Fill guesses from your examples, so check rows that look different from the ones you typed. If it guessed wrong, undo it and give it one or two more examples that show the unusual cases.

Replace the original data with the cleaned version

A helper column full of formulas still depends on the original column. Delete the original and the formulas have nothing left to read. Convert the formulas to plain values first:

1. Select the helper column and copy it with Ctrl+C.
2. With the same cells still selected, go to Home, select the arrow under Paste, and choose Values. You can also press Ctrl+Alt+V to open Paste Special, select Values, and confirm. The cells now hold the cleaned text itself, not formulas.
3. Click one of the cells and check that the formula bar shows text, not a formula starting with an equals sign.
4. Delete the original column, or keep it until you are sure the cleaned data is right.

This is the sequence Microsoft recommends for column clean-ups: insert a new column, add the formula, fill it down, paste the new column as values, then delete the original.

Check the result and undo mistakes

Press Ctrl+Z, or select Undo on the Quick Access Toolbar, to reverse a Replace All, a Flash Fill or a paste. By default Office keeps the last 100 undoable actions, so you can step back through several changes.

Two things can take Undo away. Microsoft notes that running a macro in Excel clears the undo stack, and that some actions, such as saving a file or choosing commands on the File tab, cannot themselves be undone. Your backup copy is the reliable way back if Undo is no longer available.

For a quick accuracy check, put =LEN(A2) and =LEN(B2) side by side for a few rows. If you removed two hyphens, the cleaned cell should be exactly two characters shorter than the original. A bigger difference means something else was removed as well.

Troubleshooting

Replace All removed far more than expected

Undo with Ctrl+Z, then check two things. First, if the character was an asterisk or a question mark, Excel treated it as a wildcard; search for ~* or ~? instead. Second, open Options>> and make sure Within is set to Sheet and not Workbook.

Find and Replace says it found nothing

Open Options>> and clear Match case and Match entire cell contents, since either one can hide matches. If you are trying to remove a space that will not match, it may be a nonbreaking space, so use the CHAR(160) formula above instead.

The cleaned numbers will not add up

SUBSTITUTE, REPLACE, LEFT and RIGHT return text, even when the result looks like a number. Wrap the formula in VALUE to convert it: =VALUE(SUBSTITUTE(A2,” kg”,””)) returns the number 12.5 from “12.5 kg”. If the remaining text is not in a number, date or time format that Excel recognizes, VALUE returns the #VALUE! error, which tells you some other character is still in the cell.

A LEFT or RIGHT formula shows an error on some rows

Those rows are shorter than the number of characters you are trying to remove, so the formula asks for a negative number of characters. Fix or skip the short entries; they are often blanks or data-entry mistakes worth looking at anyway.

TEXTBEFORE or TEXTAFTER returns #N/A

The marker is not in that cell. Add the if_not_found argument, as in =TEXTBEFORE(A2,”-“,,,,A2), to return the original text for those rows.

The helper column changed after you deleted the original

The helper column still contained formulas. Undo the deletion, paste the helper column as values, and then delete the original.

Frequently asked questions

How do I remove the first character from a cell in Excel?

Use =REPLACE(A2,1,1,””) in a helper column. It replaces one character starting at position 1 with nothing. Change the second 1 to remove more characters from the start.

How do I remove the last character from a cell?

Use =LEFT(A2,LEN(A2)-1). It keeps every character except the final one. Empty cells return an error with this formula, because there is no character to remove.

Can I remove characters without using a formula?

Yes. Find and Replace with an empty Replace with box removes a specific character directly in the cells, and Flash Fill builds a cleaned column from examples you type. Neither leaves a formula behind.

What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE removes specific text wherever it occurs, such as every hyphen. REPLACE removes whatever sits at a position you specify, such as characters 4 through 7. Microsoft’s guidance is the same: use SUBSTITUTE to replace specific text and REPLACE to replace text at a specific location.

Does TRIM remove all spaces?

No. TRIM keeps single spaces between words and removes the rest. To remove every ordinary space, use =SUBSTITUTE(A2,” “,””). Nonbreaking spaces need the CHAR(160) formula.

How do I remove letters and keep only the numbers?

If the letters are the same in every cell, such as a unit or a fixed prefix, remove them with SUBSTITUTE or REPLACE. If they vary from row to row, Flash Fill is usually quicker: type the number alone for the first couple of rows and let Excel fill in the rest. When the numbers and text are divided by a consistent separator, you can also separate numbers in a cell with Text to Columns.

Start with a copy of one column and the method from the table that matches your data. Once the cleaned column checks out, paste it as values and remove the original.

Related: capitalize each word in Excel.

Related: create folders from an Excel list.

Related: find and replace in Excel.

Related: how to select all blank cells in Excel.

Related: how to use the SEARCH function in Excel.

Related: how to use the LEFT function in Excel.