Data in regular formats such as those with multiple dashes can be difficult to segment into parts. Learn how to use Excel formulas to separate data that uses multiple dashes.
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.
Advertisement -
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.
Advertisement -
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.
Advertisement
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
