- Excel Book Excerpt
Excel How to Use Functions to Isolate Everything After a Dash in a Column
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 Use Functions to Isolate
Everything After a Dash in a Column
Problem: I need to isolate everything after the dash from Figure 233 above.
Strategy: You can use the MID function to extract a portion of text from the middle of the text. The MID function requires three arguments: =MID(Cell with Text, Character Number to Start, Number of Characters). You can use the FIND function to locate the dash in the item number and start at one character to the right. Rather than use the LEN function to figure out the length, ask for a really large number for the number of characters. MID will only return characters to the end of the cell.
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 quotation marks. The second argument is the location of the cell that contains the text to search. Here’s how the process works:
1. Enter =FIND(“-”,A2) in cell F2 and copy it down as in the previous topic.
2. To isolate the part number, you need to start one character to the right of the dash, so start your formula with =MID(A2,F2+1,
3. Next you need the number of characters. While you could use LEN(A2)-F2, it is not necessary. Use a large number such as 20 for the number of characters. This should be large enough to handle the largest possible part number. Close the formula with a ).
4. Copy the formula =MID(A2,F2+1,20) down to all rows.
Figure 234 MID with Find
To combine the two formulas into a single formula, use =MID(A2,FIND(“-”,A2)+1,20).
See “Combine Formulas into a Mega-Formula” on page 218 for an easy way to combine intermediate formulas.
For more resources for Microsoft Excel: