How to Use Excel Functions to Isolate Everything after the Second Dash in a Column
Strategy: There is an optional third argument in the FIND function. This 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.
-
As in the prior examples, use =FIND(“-”,A2) in cell F2 to locate the first dash.
-
Enter =FIND(“-”,A2,F2+1) in cell G2, as shown in Fig. 277. The F2+1 parameter tells Excel that you want to find a dash starting in the fourth character position of cell A2.
-
Enter =LEFT(A2,F2–1) in H2. The formula in H2 locates the first segment of the part number.
-
Enter =MID(A2,F2+1,G2–F2) in I2. The formula in I2 locates the middle segment of the part number.
-
To get the right segment of the part number, you can 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.
Enter the resulting formula, =RIGHT(A2,LEN(A2)–G2), in J2. See Fig. 278.
Gotcha: All of these formulas are trusting that the vendor always included two dashes in the item number. If an item number exists without a second dash, the second FIND function would 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, sort the data by column F and then sort descending by column G. As shown in Fig. 279, any #VALUE! errors will sort to the top of the dataset, where you can locate and correct the errors in the part number.
Summary: Using combinations of FIND, LEN, MID, LEFT, and RIGHT, it is possible to parse nearly any data imaginable.
Functions Discussed: =FIND(); =LEN(); =MID(); =LEFT(); =RIGHT()
Images
