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 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.
Figure 231 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.
Figure 232 The 3 says the dash is the 3rd character in A2.
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).
4. Double-click the fill handle to copy this formula down to all cells.
Figure 233 The FIND provides an end point for the LEFT function.
Instead use a single formula in F2: =LEFT(A2,FIND(“-",A2)-1)
<-Previous Topic Next Topic->
For more resources for Microsoft Excel: