Excel does not have one universal “remove text” function: use SUBSTITUTE to remove specified characters or words, REPLACE to remove text at a known position, or combine text functions when the part to remove depends on its location. Each formula returns a changed string in a separate cell; it does not alter the original cell by itself.
Which Excel function removes text?
The best function depends on what you know about the text. If you know the exact characters or phrase to delete, start with SUBSTITUTE. If you know where the unwanted characters begin and how many there are, use REPLACE. If the removal depends on a fixed number of characters at the beginning or end, use LEFT, RIGHT, and LEN. For content separated by a delimiter such as a hyphen, newer Excel versions also provide TEXTBEFORE and TEXTAFTER.
For example, a product code might contain hyphens, a copied ID might have a prefix, or an imported description might include a suffix you do not need. The formula should describe the rule for removing that text—not just match one example—so it works when you fill it down for other rows.
These examples are for Excel for Microsoft 365 on Windows. They use commas between formula arguments, as in the standard English-language Excel setup. Some regional settings use semicolons instead. TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024; the other functions shown here are also available in many earlier Excel versions.
Remove a specific character or phrase with SUBSTITUTE
SUBSTITUTE replaces a particular piece of text with another piece of text. To delete it, use an empty string—two double quotes with nothing between them. This is usually the simplest approach when the unwanted text is known, wherever it appears in the cell.
With product codes in A2:A20 and A2 containing AB-123-XY, enter this formula in B2 to remove every hyphen; the result is AB123XY.
=SUBSTITUTE(A2,"-","")
Copy or fill the formula down column B to process the other rows. If only one occurrence should be removed, add the optional instance number. With a date-like text value in A2 of 2026-11-02, this formula removes the second hyphen, returning 2026-1102.
=SUBSTITUTE(A2,"-","",2)
The instance number counts matching occurrences from left to right. Omit it to remove all matches. A phrase works the same way as a single character: to remove the literal text Ltd, use that phrase as the old text. SUBSTITUTE is case-sensitive, so Ltd and LTD are different matches. If capitalization varies, consider normalizing the text first or using a different approach suited to the data.
Remove characters at a known position with REPLACE
REPLACE swaps a specified number of characters, starting at a specified character position. Replacing them with an empty string deletes them. Character positions start at 1, not 0, so the first character is position 1.
With codes in A2:A20 and A2 containing PRE-4821, this formula removes the first four characters and returns 4821.
=REPLACE(A2,1,4,"")
This is useful when every value has a prefix of the same length, or when a known section is always in the same position. It is less reliable if prefixes have different lengths: removing four characters from PRE-4821 and LONG-4821 would not remove the same meaningful prefix in both. In that case, identify the delimiter or exact text instead.
For a predictable number of leading or trailing characters, formulas using LEFT, RIGHT, and LEN can be easier to read. LEN counts characters in the cell, and the other functions return a specified number of characters from one end.
Remove a fixed number of characters from an end
To remove a suffix with a consistent length, return everything to its left. With filenames in A2:A20 and A2 containing Report.csv, this formula removes the final four characters, returning Report.
=LEFT(A2,LEN(A2)-4)
The number 4 includes the period as well as the three letters in .csv. This formula assumes every value has that suffix. If some cells do not, the formula may remove valid characters from those values. For a conditional removal, check the ending first or use a delimiter-based method.
To remove a fixed number of characters from the beginning, return the characters to the right of that position. With values in A2:A20 and A2 containing ID-704, this formula removes the first three characters and returns 704.
=RIGHT(A2,LEN(A2)-3)
Both formulas depend on the text being at least as long as the number of characters being removed. If lengths vary or some cells are blank, test the formula on representative values before applying it to a large range.
Remove text before or after a delimiter
A delimiter is a character or string that separates parts of a value, such as a hyphen, slash, comma, or colon. If the position of the delimiter varies but the delimiter itself is consistent, TEXTBEFORE and TEXTAFTER can make the rule clearer than counting characters. These functions require Microsoft 365 or Excel 2024.
With order codes in A2:A20 and A2 containing Order-4821-Blue, this formula returns the text before the first hyphen, Order.
=TEXTBEFORE(A2,"-")
To return the text after the first hyphen instead, use this formula; the result is 4821-Blue.
=TEXTAFTER(A2,"-")
These formulas extract one side of the delimiter; they do not edit the original cell. If you want to remove only the delimiter while joining the parts, the exact formula depends on which sections to keep. If there can be several delimiters, check whether you need the first or last one: optional arguments in TEXTBEFORE and TEXTAFTER can specify an instance. Excel versions without these functions can often use combinations of LEFT, RIGHT, LEN, and FIND, but those formulas require more care when the delimiter is missing.
Clean unwanted spaces and imported characters
Sometimes the apparent “text” to remove is actually extra spacing or a nonprinting character from copied or imported data. TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one. It does not remove every kind of invisible character, and it changes repeated spaces inside the text too.
With imported text in A2:A20 and A2 containing North Office , this formula returns North Office.
=TRIM(A2)
CLEAN removes many nonprinting characters from text. To apply it along with TRIM, use the following formula; with the same example text plus supported control characters, it removes those characters and normalizes ordinary spaces.
=TRIM(CLEAN(A2))
A common complication is a nonbreaking space, which can look like an ordinary space but may remain after TRIM. For text in A2:A20 containing that character, this formula replaces it with an ordinary space and then trims the result.
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Use cleanup functions when the issue is unwanted spacing or control characters, not when you need to delete meaningful content. It can help to inspect a few source values and compare their lengths before and after cleaning; an unexpected length difference may reveal characters that are not visible on the worksheet.
Choose a formula that matches the removal rule
Before choosing a function, define exactly what should disappear and what should remain. A quick decision guide is:
- Known character or phrase, anywhere in the value: use
SUBSTITUTE. - Known starting position and number of characters: use
REPLACE. - Fixed number of characters at the start or end: use
RIGHTorLEFTwithLEN. - Text on one side of a consistent delimiter: use
TEXTBEFOREorTEXTAFTERin Microsoft 365 or Excel 2024. - Extra ordinary spaces or certain control characters: use
TRIMand, where appropriate,CLEAN.
For a one-time cleanup, you can also use Find and Replace: select the relevant cells, press Ctrl+H on Windows, enter the unwanted text in “Find what,” leave “Replace with” empty, and choose Replace All. On Mac, the shortcut is usually Control+H; you can also open Find and Replace from Excel’s Edit menu. Check the selected range and the replacement options carefully: Find and Replace changes cell contents directly, whereas a formula leaves the original data intact.
For repeated work, formulas are easier to review and update. Put the formula in a new column, verify several results, then fill it down. If you need permanent cleaned text rather than formulas, copy the results and use Paste Special > Values to replace or populate the destination cells. Keep a copy of the original data until you have confirmed the output.
Common problems and how to avoid them
A formula that returns an unexpected result often reflects a mismatch between the rule and the actual data. For example, a formula that removes a three-character prefix assumes every value has one; it will also cut valid text from a value without that prefix. Review cases with different lengths, missing delimiters, repeated delimiters, and blank cells—not just the first row.
- The formula shows as text: the cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, then re-enter the formula.
- The unwanted characters remain: check for different capitalization, punctuation, or invisible spaces.
SUBSTITUTEmatches the text you specify, not visually similar alternatives. - You see an error: a negative character count can occur if a
LEN-based formula tries to remove more characters than the value contains. Check the shortest values in the range. - The original value is unchanged: that is expected; formulas return results elsewhere. Use the result column or paste values if you need a fixed replacement.
Do not confuse deleting characters from a text value with deleting worksheet cells or rows. A text formula changes the displayed result in its output cell; it does not remove the source cell, shift other data, or change the underlying source value.
Frequently Asked Questions
What is the easiest Excel formula to remove text?
If you know the exact text, use SUBSTITUTE and replace it with an empty string. For example, with the value in A2, =SUBSTITUTE(A2,"-","") removes every hyphen.
How do I remove the first character in Excel?
With the text in A2, use =RIGHT(A2,LEN(A2)-1). It returns all but the first character. Check that the cell is not shorter than expected before filling the formula down.
How do I remove text after a dash?
In Microsoft 365 or Excel 2024, with the value in A2, =TEXTBEFORE(A2,"-") returns the part before the first dash. If you mean remove the part before the dash and keep what follows, use TEXTAFTER instead.
Can Excel remove text from the same cell with a formula?
A formula cannot overwrite its own source cell; it returns a result in another cell. After checking the result, copy it and use Paste Special > Values if you want to replace the original text.
Does SUBSTITUTE remove every matching character?
Yes, unless you provide its optional instance number. Without that number, it replaces every occurrence of the specified text. Matching is case-sensitive.
Related
- Excel Data Validation Rules: How They Work and When to Use Them
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Find a Position in Excel with MATCH and XMATCH FormulasTo find where a value sits within a list, use MATCH or XMATCH. If the value to find…
- Filter Excel Horizontally: Methods That WorkYes, you can filter Excel horizontally, but the standard Filter dropdown is designed to filter records in rows,…
- ISERROR in Excel: Check for Errors in Your FormulasIn Excel, use ISERROR to check whether a value or calculation produces an error: enter a formula such…
- Excel Trim a Table: Clean Text or Remove Rows?To trim text inside an Excel table, add a column with =TRIM([@[Raw Name]]); to trim the table’s physical…
- Excel SUM Array: Formulas, Examples, and Common FixesTo add the values in an array or range in Excel, use =SUM(A2:A10) when the numbers are in…
- MATCH Excel String: Find Exact Text MatchesTo find an exact text string in Excel, use MATCH with its third argument set to 0. With…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.