Excel 2min Tip #1: Sort data with the =RIGHT function: by David Carter

Share this content
1

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 fo...

Please Login or Register to read the full article

Replies

Please login or register to join the discussion.

avatar
By Anonymous
23rd Feb 2007 13:14

a little less brittle ....
A robuster formula that would handle the case where the reference before the "-" was of variable length would be :
=right(A1,len(A1)-find("-",a1)).

As a by-product it would also handle the case where the reference after the "-" was also of variable length

Thanks (0)