How to Remove Characters After a Hyphen in Excel with Formulas?

Do you have data in Microsoft Excel that contains hyphens followed by unwanted characters you want to remove? For example, product codes like “ABC-123” where you only need the “ABC” part before the hyphen. Luckily, there are several easy methods to remove characters after a hyphen in Excel using formulas. In this article, we’ll show you step-by-step how to extract just the text before a hyphen and get rid of everything after it.

Removing characters after a hyphen is a common data cleaning task in Excel. Whether you’re working with product codes, part numbers, or other alphanumeric data, getting rid of extraneous text can help standardize your information and make it easier to work with. Excel’s string functions provide several ways to accomplish this.

Method 1: Remove Characters After Hyphen Using LEFT and FIND Formulas

One of the simplest ways to remove text after a hyphen in Excel is by combining the LEFT and FIND functions. Here’s how it works:

1. Assume your product codes are in column A, starting in cell A2
2. In cell B2, enter the formula:
   =LEFT(A2,FIND(“-“,A2)-1)
3. Press Enter, then drag the formula down to apply it to the rest of column B

The LEFT function extracts characters from the left side of a text string. The FIND function locates the position of the hyphen character (“-“). By subtracting 1 from the hyphen’s position, we tell LEFT to return all the characters up to but not including the hyphen.

For example:

The LEFT and FIND combo is effective and adaptable. You can easily modify it to handle different delimiter characters by replacing the hyphen (“-“) in the FIND function. For instance, using FIND(“.”,A2) would locate a period instead. This makes it a flexible solution for many text parsing needs.

One thing to note is that FIND is case-sensitive. If your actual separator is a different character like an underscore, you may need to use the SEARCH function instead, which works identically to FIND but is not case-sensitive.

Method 2: Use SUBSTITUTE to Replace Everything After the Hyphen

Another option to extract text before a hyphen in Excel is using the SUBSTITUTE function to replace the hyphen and everything after it with empty text (“”), effectively removing it.

1. With your hyphenated codes in column A
2. In B2, use this formula:
   =SUBSTITUTE(A2,MID(A2,FIND(“-“,A2),LEN(A2)),””)
3. Enter and drag down to populate column B

Let’s break this down:

• FIND locates the hyphen
• MID extracts the text from the hyphen to the end of the string
• SUBSTITUTE then replaces that text with “” (empty)

So “ABC-123” becomes just “ABC”.

The SUBSTITUTE approach requires a more complex formula than LEFT/FIND, but it’s a powerful method for manipulating text. By replacing the MID portion with other text-extracting functions, you could adapt this to remove characters before the hyphen, or between two hyphens. SUBSTITUTE provides a versatile foundation for text transformations.

Method 3: Remove Characters with Flash Fill

For a quick, non-formula method to remove everything after the first hyphen:

1. In B2, manually type the characters you want from the first cell (before the hyphen)
2. Click Data > Flash Fill (or Ctrl+E)
3. Excel will detect the pattern and autofill column B

Flash fill is fast but works best with consistent data. The formula methods are more reliable for tricky scenarios.

Flash Fill is a handy Excel feature that automatically fills data when it detects a pattern. It works great for simple text extractions like removing characters after a delimiter. However, Flash Fill can struggle with inconsistent data or more complex patterns. In those cases, formulas provide more control and reliability.

Still, for quick, one-off jobs, Flash Fill is a useful tool to have in your Excel toolkit. It’s always worth trying Flash Fill first before jumping into formulas, as it could save you time on straightforward tasks.

Dealing with Multiple Hyphens

What if your codes contain more than one hyphen and you want to remove everything after the second hyphen? Just use the FIND function again with its optional “start_num” argument to locate the 2nd hyphen:

=LEFT(A2,FIND(“-“,A2,FIND(“-“,A2)+1)-1)

For codes like “ABC-123-XYZ”, this will return “ABC-123”, removing the 2nd hyphen and beyond.

Handling multiple delimiters is a common challenge when parsing text in Excel. By nesting FIND functions and specifying the “start_num” argument, you can locate the position of the second, third, or any subsequent occurrence of a delimiter.

This technique opens up many possibilities for extracting specific portions of text. For instance, you could use it to grab just the middle section of a string between two hyphens, or remove text after the last hyphen in a string with a variable number of hyphens.

The key is understanding how the “start_num” argument works in FIND and SEARCH. It tells the function where to start looking for the specified character. By setting it to the position just after a previous delimiter, you can find the next occurrence. Combine this with other functions like LEFT, RIGHT, and MID, and you have a powerful toolkit for text manipulation.

Extracting Text After the Hyphen

We’ve focused on removing text after hyphens in Excel, but you can tweak these formulas to extract the text after the hyphen instead.

• Using RIGHT and FIND:
  =RIGHT(A2,LEN(A2)-FIND(“-“,A2))
• Using SUBSTITUTE:
  =SUBSTITUTE(A2,LEFT(A2,FIND(“-“,A2)),””)

These formulas assume you want everything after the last hyphen. Adjust the FIND arguments as shown earlier to target different hyphens.

While removing characters after a hyphen is a common need, sometimes you might want to do the opposite and extract just the text after the hyphen. Excel’s RIGHT function is perfect for this.

RIGHT works similarly to LEFT, but starts from the right side of the string. By subtracting the hyphen’s position from the total string length, we can get the number of characters after the hyphen, which is exactly what RIGHT needs to extract that substring.

You can also achieve this with SUBSTITUTE by replacing everything up to and including the hyphen with empty text. Both methods are useful to have in your problem-solving toolkit.

Handling Errors

#VALUE Error

Excel’s FIND function is case-sensitive. If your actual hyphen is a different character like a minus sign, en dash or em dash, FIND won’t locate it and you’ll get a #VALUE error. Use SEARCH instead of FIND in this case, as it’s not case-sensitive.

#NAME Error

Getting a #NAME error? Make sure you’ve spelled the function names correctly. Excel’s formulas are not case-sensitive, but the function names have to be exact.

When working with Excel formulas, it’s inevitable that you’ll encounter errors from time to time. Two common ones when working with text are #VALUE and #NAME errors.

A #VALUE error usually means Excel is expecting one type of input but getting another. With text functions, this often happens when the specified delimiter isn’t found, like when FIND can’t locate a hyphen because the actual separator is a different character. Switching to the SEARCH function (which is case-insensitive) or double-checking your delimiter character can often resolve this.

A #NAME error typically indicates a typo or misspelling in a function name. While Excel isn’t case-sensitive for function names, the spelling must be exact. Double-check your formulas against Excel’s function list to make sure everything is spelled correctly.

Debugging formulas is an essential skill for working with Excel. By understanding common error messages and their causes, you can quickly diagnose and fix problems in your spreadsheets.

Summary

There are multiple ways to remove characters after a hyphen in Excel:

• LEFT/RIGHT and FIND functions
• SUBSTITUTE to replace hyphen and after with blanks
• Flash Fill for simple patterns

By combining these functions in different ways, you can handle any number of hyphens and extract exactly the pieces of text you need. Master these techniques and take control of your Excel data!

Removing characters after a hyphen is just one example of the many text manipulation tasks you can perform in Excel. By understanding functions like LEFT, RIGHT, MID, FIND, SEARCH, and SUBSTITUTE, you can parse and transform text data in countless ways.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.