Microsoft Excel Help: How To Isolate Everything Before A Dash In A Column By Using Functions
Strategy: Use the FIND function to locate the dash in the item number. The FIND function will return the character position of the dash. You can then use that result minus one in the LEFT function to isolate the manufacturer code.
The FIND function requires two arguments. The first argument is the text that you are trying to locate. In this case, you are trying to locate a dash, so you should include the dash in quotes. The second argument is the location of the cell containing the text to search.
- Enter =FIND(“-”,A2) in cell F2. Copy down to all of the other cells, as shown in Fig. 269.
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. If you want to isolate the manufacturer code, you will want to grab one less than this number from the left of the code.
- In cell G2, enter the formula =LEFT(A2,F2–1), as shown in Fig. 270. Double-click the Fill handle to copy this formula down to all cells.
Alternate Strategy: You do not need to enter the formulas in two different columns. You could easily have used this formula in cell F2: =LEFT(A2,FIND(“-”,A2)–1)
Additional Details: Once the formula has isolated the manufacturer code, change the formulas to values. See the Cross Reference.
Result: You can now sort by the new column and add subtotals by this field.
Summary: When you need to isolate a portion of the characters in another column, creating a temporary column is the solution.
Functions Discussed: =FIND(); =LEFT()
Cross Reference: How to Sort on One Segment of an Account ID
Images
