Excel: How to Isolate Everything Before a Dash in a Column by Using Functions

Problem: A vendor has given me an Excel worksheet. One field has a manufacturer code, a dash, and a part number. I need to isolate the manufacturer code in a new column, but the manufacturer codes are not always the same length.

1. Get the left text, up to the dash.

   Strategy: You can use the FIND function to locate the character position of the dash. You can use that result minus one in the LEFT function.

   The FIND function requires two arguments. The first is the text that you are trying to locate. Use a dash in quotation marks. The second argument is the cell that contains the text to search. Here's how the process works:

   1. Enter =FIND("œ-",A2) in cell F2.
   2. Copy this formula down to all the other cells. The 3 in cell F2 indicates that the dash is located in the third character position of cell A2. The 6 in cell F3 indicates that the dash is in the sixth position of cell A3.
   3. To isolate the manufacturer code, you need a number that is one less than the number in column F, so in cell G2, enter the formula =LEFT(A2,F2-1).

   

2. The 3 says the dash is the 3rd character in A2.
   4. Double-click the fill handle to copy this formula down to all cells.

   

3. The FIND provides an end point for the LEFT function.

   Instead use a single formula in F2: =LEFT(A2,FIND("œ-",A2)-1)