How to strip characters in Excel
Every messy import ends the same way: dashes in order numbers, stray spaces, currency symbols glued to numbers. Excel has a small kit of text functions that strips any of it. Here is the whole kit, with copyable formulas and worked examples, from the one-line fix to the version-proof patterns.
The quick answer: SUBSTITUTE removes a specific character
SUBSTITUTE(text, old, new) replaces every occurrence of old with new. Replace with an empty string "" and the character is stripped:
=SUBSTITUTE(A2, "-", "")Worked example: with INV-00482-A in A2, the formula returns INV00482A. Every dash is removed, everything else is untouched.
To strip several different characters, nest one SUBSTITUTE inside another. Each layer removes one character:
=SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", "")SUBSTITUTE is case-sensitive: substituting "a" will not touch "A".
Strip spaces: TRIM for the accidental ones, SUBSTITUTE for all of them
TRIM(A2) removes leading and trailing spaces and collapses any run of interior spaces down to a single space. It is the standard first-aid for pasted data:
=TRIM(A2)Worked example: " Acme Corp " becomes "Acme Corp".
If you want NO spaces at all (say, building an ID), strip them with SUBSTITUTE instead: =SUBSTITUTE(A2, " ", "") turns "Acme Corp" into "AcmeCorp".
Strip invisible junk: CLEAN and the non-breaking space
Text copied from web pages and PDFs often carries characters you cannot see. CLEAN(A2) removes the non-printing control characters (ASCII 0 to 31), including stray line breaks. The classic combo scrubs both kinds of junk at once:
=TRIM(CLEAN(A2))One character survives CLEAN: the non-breaking space (character code 160) that HTML uses. It looks exactly like a space but is not one. Convert it to a real space first, then trim:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))Strip a fixed number of characters: LEFT, RIGHT, MID, REPLACE
When the junk sits at a known position, cut by position. LEN(A2) is the total length; the rest is arithmetic:
- Remove the first 4 characters:
=RIGHT(A2, LEN(A2) - 4)(keep everything after them), or the same thing spelled directly:=REPLACE(A2, 1, 4, ""). - Remove the last 3 characters:
=LEFT(A2, LEN(A2) - 3). - Keep only characters 5 to 10:
=MID(A2, 5, 6)(start position, then how many).
Worked example: with INV-00482 in A2, =REPLACE(A2, 1, 4, "") returns 00482, and =LEFT(A2, FIND("-", A2) - 1) returns INV.
Strip everything before or after a character: FIND does the measuring
When the cut point moves (an @ sign, a dash, a slash), locate it with FIND(what, where) and feed that position to LEFT or MID. These two patterns work in every version of Excel:
=LEFT(A2, FIND("@", A2) - 1)
=MID(A2, FIND("@", A2) + 1, LEN(A2))Worked example: with john@acme.com in A2, the first formula returns john (everything before the @), the second returns acme.com (everything after it).
If you are on Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER say the same thing more directly: =TEXTBEFORE(A2, "@") and =TEXTAFTER(A2, "@"). They are not available in Excel 2021, 2019, or 2016, so the FIND patterns above remain the portable answer. Both new functions return #N/A when the delimiter is missing; wrap them in IFERROR if that can happen in your data.
FIND is case-sensitive; its sibling SEARCH is the case-insensitive version. Both return #VALUE! when the text is not found.
Strip only the Nth occurrence
SUBSTITUTE takes an optional fourth argument: which occurrence to replace. Use it to remove one specific dash and keep the others:
=SUBSTITUTE("WG-2026-0417", "-", "", 2)Worked example: the formula returns WG-20260417. Only the second dash is stripped; the first survives.
Strip characters so a number becomes a number: VALUE
After stripping currency symbols and thousands separators, the result is still text. Wrap it in VALUE to get a real number you can sum:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", ""))Worked example: "$1,299.00" becomes the number 1299, which SUM and AVERAGE will actually count.
The same formulas in Wisegrid
Wisegrid is a shared, Smartsheet-style grid whose formula engine ships SUBSTITUTE, TRIM, LEFT, RIGHT, MID, LEN, FIND, REPLACE, and VALUE, so the recipes on this page run in a Wisegrid sheet as-is. The only syntax difference is the reference style: instead of A2 you name the column, and @row means "this row":
=SUBSTITUTE([Order Id]@row, "-", "")
=LEFT([Email]@row, FIND("@", [Email]@row) - 1)
=VALUE(SUBSTITUTE(SUBSTITUTE([Amount Raw]@row, "$", ""), ",", ""))Set one of these as a column formula and every row, including new ones, computes itself. Honest differences to know before you rely on them:
TEXTBEFORE/TEXTAFTERare not in Wisegrid's supported set. Use the version-proofLEFT/FINDandMID/FINDpatterns above; they behave identically.CLEANandCHARare not in the supported set either. Strip a specific unwanted character withSUBSTITUTEand the character pasted between quotes.- Wisegrid's
TRIMremoves leading and trailing spaces but does not collapse interior runs the way Excel's does. To also squeeze double spaces, addSUBSTITUTE(text, " ", " ").
Verified against the shipped engine: every function named as supported here is in the live 118-function list, and each example formula above was executed against it before publishing.
How do I remove the first N characters in Excel?
How do I remove specific characters from a cell?
Why does TRIM not remove all the spaces in my cell?
Can I strip characters without a formula?
Formulas that work where your team does.
Wisegrid is the familiar grid with a 118-function formula engine, shared editing, and views your stakeholders can actually read. Start your 7-day free trial, and if you are coming from Smartsheet, bring your workspace with you.