How to Extract Characters After a Dash in Excel Using Formulas?

Do you need to extract just the characters that come after a dash in your Excel data? For example, maybe you have product codes like “ABC-123” and you want to pull out only the “123” portion. Or perhaps you have a list of names in the format “Last-First” and you need to separate the first names into their own column. In this article, we’ll cover how to use Excel formulas and features to extract the characters after a dash, with several different methods to accomplish this common task.

Extracting characters after a dash is a useful skill to have in your Excel toolbox, as it can help you clean up and standardize your data. By isolating just the parts of the text you need, you can more easily analyze, sort, and work with your information. Let’s explore a few ways to tackle this.

Using the RIGHT, LEN, and FIND Functions

One way to extract characters after a dash in Excel is by combining the RIGHT, LEN, and FIND functions in a formula. Here are the steps:

1. Assume your product code (or other dash-separated text) is in cell A2.
2. In another cell, enter the following formula: =RIGHT(A2,LEN(A2)-FIND(“-“,A2))
3. Press Enter, and the formula will return only the characters after the dash.

Here’s a breakdown of how the formula works:

• The FIND function locates the position of the first dash (“-“) within the text string in A2
• The LEN function calculates the total number of characters in the string in A2
• We subtract the FIND result from the LEN result to determine how many characters are to the right of the dash
• Finally, the RIGHT function extracts that number of characters counted from the right side of the text in A2

So if cell A2 contained the product code “ABC-123”, the formula would return “123”.

You can then drag the formula down to apply it to a whole column of product codes or other dash-separated text. The cell references will automatically adjust for each row.

Dealing with Text Containing Multiple Dashes

What if your original text contains more than one dash? For example, what if you had a product code like “ABC-123-XYZ”? The formula above will extract everything after the first dash it encounters, so in this case it would return “123-XYZ”.

If you want to get the characters after the last dash instead, you can use this slightly modified formula:

=RIGHT(A2,LEN(A2)-FIND(“*-“,SUBSTITUTE(A2,”-“,”*-“,(LEN(A2)-LEN(SUBSTITUTE(A2,”-“,””)))/LEN(“-“))))

This looks for the last dash in the string by using SUBSTITUTE to replace the dashes with asterisks, and the asterisk followed by a dash (“*-“) tells FIND to locate the last occurrence.

In the case of “ABC-123-XYZ”, this version of the formula would correctly return just “XYZ”.

Using Flash Fill

Another easy way to extract characters after a dash in Excel is to use the Flash Fill feature, introduced in Excel 2013. Flash Fill automatically fills data when it senses a pattern. Here’s how to use it:

1. In the cell adjacent to your first product code (or other dash-separated text), manually type what you want to extract. For example, if your product code is “ABC-123”, type “123”.
2. Select that cell and the cells below it where you want to fill in the rest of the characters after the dashes.
3. On the Data tab, in the Data Tools group, click Flash Fill.
4. Excel will automatically detect the pattern and extract just the characters after the dash for all the selected rows!

Flash Fill is a very convenient way to quickly extract text based on a pattern, without needing to write your own formula. Just keep in mind that if your data doesn’t consistently follow the exact same pattern, Flash Fill may not give you the results you expect. It works best for straightforward, uniform text structures.

Using Text to Columns

Excel’s Text to Columns feature is yet another way to split up the characters before and after a dash into separate columns. Here are the steps:

1. Select the cells containing your product codes or other dash-separated text.
2. On the Data tab, in the Data Tools group, click Text to Columns.
3. In the Convert Text to Columns Wizard, select Delimited and click Next.
4. In the next dialog, check the box for Other under Delimiters and type a dash (-) in the field next to it. Click Next.
5. In the final dialog, choose the destination for where you want the split data to be placed. You can select the existing column (to replace your original data) or an adjacent empty column. Click Finish.

Text to Columns will put the characters before the dash in one column and the characters after the dash in the adjacent column to the right.

This method is handy if you want to permanently separate your codes into two distinct columns rather than just extracting the latter part into a new column. However, keep in mind that it does overwrite your original data.

Using Power Query

For a more advanced and flexible method, you can use Power Query (called Get & Transform in Excel 2016) to extract characters after a dash. Power Query is a powerful tool for shaping and transforming data in Excel. Here’s how to use it:

1. Select any cell in your data range.
2. On the Data tab, in the Get & Transform group, click From Table/Range. This will open the Power Query editor with your data loaded.
3. In the editor, select the column containing your product codes or dash-separated text.
4. On the Transform tab, in the Text Column group, click Split Column, then By Delimiter.
5. In the Split Column by Delimiter dialog, set the delimiter to –Custom and enter a dash (-). Under Split At, choose Right-most delimiter. Click OK.
6. The characters before and after the dash will now be in separate columns. You can rename the new column to something meaningful.
7. If you don’t need the column with the characters before the dash, you can right-click its header and choose Remove.
8. When you’re done, click Close & Load in the Home tab to load the results back into a new worksheet in Excel.

Using Power Query gives you a lot of flexibility in how you manipulate and transform your data before bringing it back into Excel. You can perform multiple splits, filter rows, change data types, and much more. It’s a great tool to learn if you frequently work with large or complex datasets.

Summary

In this article, we covered four different ways to extract characters after a dash in Excel:

1. Using a formula that combines the RIGHT, LEN, and FIND functions
2. Using the Flash Fill feature to automatically detect and fill in the desired characters
3. Using the Text to Columns feature to split the data into separate columns
4. Using Power Query for more advanced data transformation

Here is a comparison table summarizing the pros and cons of each method:

Which method you choose will depend on your specific data, your level of comfort with different Excel tools, and how you need to use the extracted characters in your workbook. If you have a one-off task with simple, consistent data, Flash Fill might be the quickest solution. If you need something more robust and repeatable for complex data, Power Query would be better suited.

In any case, we recommend experimenting with each method to find what works best for your situation. With practice, identifying text patterns and extracting just the parts you need will become increasingly quick and easy. These skills will make you more efficient and effective in cleaning, transforming, and analyzing your Excel data.

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.