Microsoft Excel: How To Isolate The Center Portion Of An Account ID

Article by Mr Excel (11,387 pts ) , published Jul 1, 2008

Problem: Your company assigns an account ID in the format of SSSXX-YYYY as shown in Fig. 266. You need to isolate the XX portion of the account ID in order to subtotal or sort the data.

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

Images

Fig. 266Fig. 267
 
Subscribe to Home Office
RSS
Get free weekly updates, directly to your inbox.