Sort by last two characters, not the first.

[gino986]

I have a simple database; multiple rows and columns. I need to sort all the
data by info in one of the columns. I need to sort by the LAST TWO characters
of records in that column. Example of records:

R-BOILER-DA
R-FANCOILUNIT-QT
U-TRANSPAD-AN

All the data in the rows needs to stay together.

 

[Jacob Skaria]

1. Select the column. From menu Data text to columns. with delmiter as
hyphen. will split this to three columns..
2. Now sort by the 3rd column
3. Combine the 3 columns using
=A1&"-"&B1&"-"&C1
4. Now copy> paste special> values to remove the formulas...

If this post helps click Yes

 

[FSt1]

hi
use a helper column along side of your data.
assuming the example data in in column A, in the helper column, enter this
=right(A2,2)
this will draw the last 2 characters of the data into the helper column
use the helper column as the primary sort.

regards
FSt1

 

[Rick Rothstein]

Assuming your data starts on Row 2, put this formula...

=RIGHT(A2,2)

in an unused column, copy it down for the length of your data, then select
all the rows of data and sort it using the above column as the primary sort
column. You can then delete the above column's formulas when you are
through. If you think you might need to sort the data again some time, use
this formula instead...

=IF(A2="","",RIGHT(A2,2))

copy it down as far as you think you might ever have data in the future and
then hide the column (rather than delete its values) after the sort (so that
you can use it again in the future).

 

[Gord Dibben]

I know this post is a couple days late but thought I would post another
method without using formulas.

Copy the entire column so's you have two identicals.

Data>Text to Columns>Delimited by>Other>Hyphen>Next.

Select columns 1 and 2 and "Do not import(skip)"

Finish. Sort on that coumn.


Gord Dibben MS Excel MVP