Problem: My company assigns an account ID in the format SSS-XX-YYYY. I need to isolate the XX portion of the account ID in order to subtotal or sort the data.
- Use a formula to extract the middle of the account ID.
Strategy: You can insert a new column and use the MID function to isolate the necessary digits from the Account field.
The MID function takes three arguments: (1) a cell that contains a text value, (2) the character number where you want the result to start, and (3) the length of the result.
In a well-formed account number, such as 123-45-6789, you can predict that the start of the second segment will always be in the fifth character position and the length of the second segment is always two characters. Therefore, you can follow these steps:
- In a blank column, such as column G, enter a heading such as Key.
- In cell G2, enter the formula =MID(A2,5,2).
- Copy the formula down to all rows.
- Get the middle text, starting at position 5, for a length of 2.
Additional Details: In order to capture the final four digits of the account number, you could either use =MID(A2,8,4) or =RIGHT(A2,4).
Results: You can now sort by the new column and add subtotals based on the Account field.