Problem: Your company assigns an account ID to customers. One segment of the account ID contains useful information, such as a parent company code. You want to be able to sort on the basis of a portion of the account ID. In Fig. 263, the first 3 digits of the account are used to identify an office.
Strategy: Insert a new column and use the LEFT function to isolate the necessary digits from the account field.
1) In the blank column, enter a heading such as the word “Key”. In cell G2, enter the formula =LEFT(A2,3), as shown in Fig. 264. This indicates that the new field should contain just the three characters leftmost of the ID field.
2) Double-click the Fill handle in cell G2 to copy the formula down to all of the rows in your dataset. The Fill handle is the black square dot in the lower right corner of the cell pointer.
3) Change the formulas in column G to values. Highlight all of the cells in column G. Use Ctrl+C to copy. As shown in Fig. 265, from the menu, select Edit – Paste Special – Values – OK.
Result: A certain portion of the Account field is now available in a new column. You can now use data tools, such as Sort, Filter, or Subtotal, to isolate certain offices.
Summary: When you need to isolate a portion of the characters in another column, creating a temporary column is the solution.
Commands Discussed: Edit – Paste Special
Functions Discussed: =LEFT()
See all Microsoft Excel tips
Images
No comments:
Post a Comment