Excel How to Use Functions to Isolate Everything After the Second Dash in a Column
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
How to Use Functions to Isolate Everything After the Second Dash in a Column
Problem: A vendor gave me a file that contains a three-segment part number. Each segment is separated by a dash, and the length of each segment could be any number of characters. How do I find the second or third segment?
Figure 235 Isolate the second or third segment.
Strategy: There is an optional third argument in the FIND function that tells Excel to start looking after a certain character position in the text. In this case, to find the second dash, you want Excel to start looking after the location of the first dash. Here’s what you do:
1. As in the prior examples, use =FIND(“-",A2) in cell F2 to locate the first dash.
2. Enter =FIND(“-",A2,F2+1) in cell G2. The F2+1 parameter tells Excel that you want to find a dash starting at the fourth character position of cell A2.
Figure 236 The 3rd argument is where FIND should start looking.
3. Enter =LEFT(A2,F2-1) in H2. This formula locates the first segment of the part number.
4. Enter =MID(A2,F2+1,G2-F2-1) in I2. This formula locates the middle segment of the part number.
5. To get the right segment of the part number, use the RIGHT function. (Just like the LEFT function, the RIGHT function requires a cell and the number of characters from the right side of the item number.) To find the number of characters, use =LEN(A2)-G2. So enter the formula =RIGHT(A2,LEN(A2)-G2) in J2.
Figure 237 Use RIGHT to find the third segment.
Gotcha: With all these formulas, you are trusting that the vendor always included two dashes in the item number. If there is an item number that does not have a second dash, the second FIND function will return a #VALUE! error, leading to errors in the calculation for the second and third items. Before converting formulas to values and deleting the original part number, you need to sort the data in descending order by column F and then sort in descending order by column G. Any #VALUE! errors will sort to the top of the data set so you can easily locate and correct them.
Figure 238 The formulas create an error if there is only one dash.
Result: By using combinations of FIND, LEN, MID, LEFT, and RIGHT, you can parse nearly any data imaginable.