To remove text before a specific character in Excel, use the REPLACE function combined with FIND or SEARCH. The core formula is =REPLACE(A1,1,FIND("char",A1),""), which deletes everything up to and including that character. For example, to strip everything before the first hyphen in cell A1, enter =REPLACE(A1,1,FIND("-",A1),"").
Understanding the Core Formula
The formula works by telling Excel exactly where to start cutting and how much to remove. FIND locates the position of your target character (the hyphen in the example) and returns a number, like 6 if the hyphen is the sixth character. REPLACE then removes that many characters starting from position 1, leaving only the text after the hyphen. This is the cleanest method because it doesn’t require knowing the length of the original string.
If you want to keep the character itself (e.g., keep the hyphen), subtract 1 from the FIND result: =REPLACE(A1,1,FIND("-",A1)-1,""). This removes everything before the hyphen but leaves the hyphen in place.
Using MID Instead of REPLACE
An alternative approach uses MID with FIND and LEN. The formula is =MID(A1,FIND("-",A1)+1,LEN(A1)). This extracts text starting one position after the hyphen and continues to the end of the string (using LEN to ensure it grabs everything). This method is slightly longer but easier to read for some users. Both formulas return identical results for the same input.
For example, with the text “Order-12345” in A1, =MID(A1,FIND("-",A1)+1,LEN(A1)) returns “12345”. The FIND returns 6, so MID starts at position 7 and extracts 10 characters (the length of the original), which safely covers all remaining text.
Case Sensitivity and Wildcards
Use SEARCH instead of FIND when you need case-insensitive matching. FIND is case-sensitive, so FIND("a","Apple") returns an error because the “A” is uppercase. SEARCH("a","Apple") returns 1 because it ignores case. For most data cleaning, SEARCH is safer.
Neither function supports wildcards like * or ? for this specific task. If you need to find the last occurrence of a character (e.g., remove everything before the last hyphen), you must use a more complex formula: =REPLACE(A1,1,FIND("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))),""). This replaces the last hyphen with a unique placeholder (@) and then finds that placeholder.
Handling Missing Characters
If the target character doesn’t exist in the cell, FIND returns a #VALUE! error. To avoid this, wrap the formula in IFERROR. For example: =IFERROR(REPLACE(A1,1,FIND("-",A1),""),A1). This returns the original text unchanged when no hyphen is found, which is usually the desired behavior.
Alternatively, use IF with ISNUMBER: =IF(ISNUMBER(FIND("-",A1)),REPLACE(A1,1,FIND("-",A1),""),A1). This is more explicit but longer. The IFERROR version is cleaner and recommended for most spreadsheets.
Working with Multiple Characters or Delimiters
To remove text before the first space, comma, or any other single character, simply replace the hyphen in the formula with that character. For a comma, use =REPLACE(A1,1,FIND(",",A1),""). For a space, use =REPLACE(A1,1,FIND(" ",A1),"").
If you need to trim before the first occurrence of any of several characters (like the first digit or first letter), you would need an array formula or a more complex approach using MIN with multiple FIND calls. For a single delimiter, the simple formula works fine. For splitting text into multiple columns, consider Excel’s built-in Text to Columns (Data tab > Data Tools) or the TEXTSPLIT function in Microsoft 365.
Common Mistakes and Fixes
One frequent error is using FIND with a multi-character delimiter. FIND only looks for a single character, so FIND("ab",A1) works but returns the position of the first “a” in “ab”, not the end. If your delimiter is two characters like “–“, you need to adjust: =REPLACE(A1,1,FIND("--",A1)+1,"") adds 1 to skip both characters.
Another mistake is confusing LEFT with this task. LEFT extracts from the beginning; it doesn’t remove text before a character. To remove text after a character, use =LEFT(A1,FIND("-",A1)-1). Remember: LEFT keeps the start, REPLACE or MID keeps the end.
Frequently Asked Questions
How do I remove text before a character in Excel on a Mac? The formulas work identically on Mac Excel. The only difference is keyboard shortcuts for entering formulas (Command+Enter instead of Ctrl+Enter) and the ribbon layout is slightly different, but the functions and syntax are the same.
Can I use this with a cell reference for the delimiter? Yes. If B1 contains the delimiter character, use =REPLACE(A1,1,FIND(B1,A1),""). This makes the formula dynamic—change B1 and the formula updates automatically.
What if I want to remove text before the last occurrence of a character? Use the complex formula with SUBSTITUTE and LEN shown earlier. It replaces the last occurrence with a placeholder, then finds that placeholder. This is the standard solution for the “last delimiter” problem.
Does this work with numbers or dates? Yes, as long as the cell is formatted as text or the number is stored as text. If A1 contains a number like 123-456, Excel may treat it as text automatically. If it’s a true number, FIND will still work because Excel converts it to text for the function, but the result will be text. To convert the result back to a number, wrap with VALUE(): =VALUE(REPLACE(A1,1,FIND("-",A1),"")).
Is there a way to do this without formulas? Yes, use Text to Columns (Data > Text to Columns > Delimited) and choose your delimiter. This splits the data into separate columns, then you can delete the first column. This is faster for one-time cleanup but doesn’t update dynamically.
Why does my formula return #VALUE!? This almost always means the delimiter character is not found in the cell. Check for typos, extra spaces, or case differences. Use SEARCH instead of FIND if case might be the issue, and wrap with IFERROR to handle missing delimiters gracefully.
Related
- Excel Spreadsheet Calendar Template: How to Make One
- Excel Split Numbers From Text: 4 Easy Ways
- Excel Sign Up Calendar Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel UNIQUE Formula: Return a List Without DuplicatesIn Microsoft 365, Excel 2021, or Excel 2024, use =UNIQUE(A2:A100) to return a list of distinct values from…
- VLOOKUP Excel Grading: Match Scores to Letter GradesTo assign a letter grade from a numeric score in Excel, put the grade cutoffs in ascending order…
- Excel SUMIF When Range Contains TextYes, you can use SUMIF when the range contains text, but the text is used as a criteria…
- Sequence Excel Without Duplicates: Formulas & TipsTo create a duplicate-free sequence in Excel for Microsoft 365, enter =SEQUENCE(10) in an empty cell; it spills…
- Excel SUMIF Equal to Cell: Formula & ExamplesYes, you can use SUMIF with a cell reference as the criteria by writing the formula as =SUMIF(range,…
- Excel COUNTIF Contains Text: Formula & ExamplesTo count cells that contain a specific text string anywhere within them, use =COUNTIF(range,"*"&text&"*") or simply =COUNTIF(range,"*text*") when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.