Excel Trim Based on Character: How to Remove Text

Formulas & functions · Updated November 22, 2026

To trim text in Excel based on a specific character, use the LEFT, RIGHT, and MID functions combined with FIND or SEARCH to locate that character. For example, to remove everything after the first hyphen in cell A1, use =LEFT(A1,FIND("-",A1)-1). This returns the text before the hyphen, effectively trimming based on that character.

Understanding the Core Functions

Excel does not have a single “trim by character” function, but you can build one using text functions. The key functions are:

  • LEFT(text, num_chars): Extracts a specified number of characters from the start of a string.
  • RIGHT(text, num_chars): Extracts from the end.
  • MID(text, start_num, num_chars): Extracts from any position.
  • FIND(find_text, within_text, [start_num]): Returns the position of a character, case-sensitive.
  • SEARCH(find_text, within_text, [start_num]): Same as FIND but case-insensitive.
  • LEN(text): Returns the length of a string.

These functions let you locate a character (like a hyphen, comma, or space) and then extract the part you want. For instance, if you have a list of names like “Smith, John” and want just the last name, you can find the comma and take everything to its left.

Removing Text After a Character

To remove everything after a specific character (including that character), use LEFT with FIND. The formula =LEFT(A1,FIND("-",A1)-1) assumes cell A1 contains text with a hyphen. It finds the hyphen’s position, subtracts 1 to exclude it, and returns all characters before it. For example, if A1 is “ABC-123”, the result is “ABC”.

If the character might not exist, wrap it in IFERROR to avoid errors: =IFERROR(LEFT(A1,FIND("-",A1)-1),A1) returns the whole text if no hyphen is found.

For case-insensitive matching, use SEARCH instead of FIND. For instance, =LEFT(A1,SEARCH("x",A1)-1) removes everything from the first “x” or “X” onward.

Removing Text Before a Character

To keep only the part after a character, use RIGHT with LEN and FIND. The formula =RIGHT(A1,LEN(A1)-FIND("-",A1)) returns everything after the hyphen. For A1=”ABC-123″, this gives “123”. If you want to exclude the character itself, subtract 1 from the position: =RIGHT(A1,LEN(A1)-FIND("-",A1)) already excludes it because FIND returns the position, and LEN minus that position gives the number of characters after the hyphen.

If the character appears multiple times, FIND returns the first occurrence. To use the last occurrence, use FIND with a negative start number or use SUBSTITUTE to find the last one. A common trick is =RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))) to get text after the last hyphen.

Extracting Text Between Two Characters

To extract text between two different characters, use MID with FIND twice. For example, to get text between parentheses in A1: =MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1). This finds the opening parenthesis, adds 1 to start after it, then calculates the number of characters between the parentheses. If A1=”Hello (World)!”, the result is “World”.

If the characters are the same (like extracting between two hyphens), you can use FIND with a start position: =MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1). This finds the first hyphen, then finds the second hyphen starting after the first, and subtracts to get the length.

Trimming Spaces and Non-Printing Characters

Often, you need to trim spaces or line breaks in addition to a specific character. The TRIM function removes extra spaces between words and leading/trailing spaces. For example, =TRIM(A1) cleans up text. To remove line breaks, use SUBSTITUTE with CHAR(10) (line feed) or CHAR(13) (carriage return). For instance, =SUBSTITUTE(A1,CHAR(10)," ") replaces line breaks with spaces.

If you need to remove all spaces, including between words, use SUBSTITUTE(A1," ",""). This is useful for cleaning data like phone numbers or IDs.

Using Flash Fill for Quick Trims

For one-off tasks, Flash Fill can be faster than writing formulas. Type the desired result in the adjacent column, then press Ctrl+E (Windows) or Cmd+E (Mac) to auto-fill the pattern. For example, if you have “John Smith” and type “Smith” in the next cell, Flash Fill will extract last names for the entire column. Flash Fill works by recognizing patterns, but it is not dynamic—if the source data changes, the results do not update.

Common Problems and Misconceptions

One common issue is that FIND is case-sensitive, so if you search for “a” and the text has “A”, it won’t match. Use SEARCH for case-insensitive. Another issue is that FIND returns a #VALUE! error if the character is not found. Always use IFERROR to handle missing characters.

Another misconception is that TRIM removes all spaces. It only removes extra spaces, not single spaces between words. To remove all spaces, use SUBSTITUTE.

Also, when using MID, the start number must be at least 1. If you use FIND and add 1, it could exceed the string length, causing an error. Test your formulas with sample data.

Alternatives: Text to Columns and Power Query

If you need to split text at a delimiter into separate columns, use Text to Columns (Data > Data Tools > Text to Columns). Select Delimited, choose your delimiter (e.g., comma, space), and it splits the data. This is static, but quick for one-time splits.

For dynamic splitting, Power Query (Data > Get & Transform Data > From Table/Range) is more powerful. You can split columns by delimiter and refresh when data changes. In Power Query, go to Transform > Split Column > By Delimiter, choose the delimiter, and it creates new columns.

Frequently Asked Questions

How do I remove everything after a specific character in Excel?
Use =LEFT(A1,FIND("-",A1)-1) to get text before the first hyphen. Replace “-” with your character. For case-insensitive, use SEARCH.

How do I remove text before a character in Excel?
Use =RIGHT(A1,LEN(A1)-FIND("-",A1)) to get everything after the hyphen. Adjust the character as needed.

Can I trim based on multiple characters?
Yes, you can nest FIND or use SUBSTITUTE to replace multiple characters with a single one, then trim. For example, to remove everything after the first comma or semicolon, replace both with a common character first.

Why does my FIND formula return #VALUE!?
This happens when the character is not found in the text. Wrap the formula in IFERROR to return a default value, like the original text.

Is there a way to trim based on character without formulas?
Yes, use Flash Fill (Ctrl+E) or Text to Columns. Flash Fill is good for one-off tasks, while Text to Columns splits into columns. Both are static, so they won’t update if source data changes.

How do I remove all characters after the last occurrence of a character?
Use a formula that finds the last occurrence, such as =LEFT(A1,FIND("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))-1). This replaces the last hyphen with a placeholder, finds its position, and takes the left part.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.