When Excel sorts a column of items, it looks at the initial character of each item, that is, the characters to the LEFT. But suppose you want to sort according to characters on the right?
For this you need to use the =RIGHT function.
Example problem:
You have a series of account codes, where the first five digits represent the nominal code, the last three digits the cost centre.
They are in cells A1:A6 and in nominal code order like this (51000-52000):
51000-100
51000-200
51000-300
52000-100
52000-200
52000-300
You want to sort them into cost centre code order like this (100, 200, 300):
51000-100
52000-100
51000-200
52000-200
51000-300
52000-300
Solution
- Create a blank column to the right of column A
- In B1 type =RIGHT(A1,3).
This will return the value 100.
Copy this formula down the rows.
Now sort the data on column B.
More Excel tips from David Carter
Tip 2: Use & to combine two columns into one
Tip 3: Create a run-time filter when importing into Excel
Tip 4: Filter faster with the AutoFilter and Show All icons
Tip 5: How to print just a part of your worksheet
ExcelZone's Five Minute Pivot Table Tips - Index
Subscribe to the ExcelZone newswire
To ensure you don't miss any of David Carter's handy Excel tips, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.