See all Microsoft Excel tips
Strategy: Insert a new column and use the MID function to isolate the necessary digits from the account field. The MID function takes three arguments. The first argument is a cell containing a text value. The second argument is the character number where you want the result to start. The final argument is the length of the result. In a well-formed account number, like 123-45-6789, you can predict that the start of the second segment will always be in the fifth character position. The length of the second segment is always two characters.
In a blank column, enter a heading,
such as the word “Key”. In cell G2, enter the formula =MID(A2,5,2). Copy the formula down to all rows, as shown in Fig. 267.
Additional Details: In order to capture the final four digits of the account number, you could either use the =MID(A2,8,4) or the =RIGHT(A2,4) function to isolate the final four digits of the account number.
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: =MID(); =RIGHT()
Cross Reference: How to Sort on One Segment of an Account ID
See all Microsoft Excel tips