Sorting

[dalei]

I have a huge set of data, a tiny part of which is listed after this
message. Now the set is sorted in normal order. But I like to sort it
by the last numbers, that is, all data with same last number are
grouped together. Is such sorting possible with Excel? If necessary,
I don't care turning each piece by 180 degrees. For instance, the
seplling 'ai1' becomes '1ia'. Thanks.

a5
ai1
ai2
ai3
ai4
an1
an3
an4
ang1
ang2
ang4
ao2
ao3
ao4
ba1
ba2
ba3
ba4

 

[Dave Peterson]

You can use a helper column of cells to extract the last character.

=right(a1,1)
copy it down and select your range (all the columns) and do data|sort. Sort by
the helper column. You could even use the original column as the secondary key.

When you're done, delete that helper column.

 

[dalei]

I inserted a column, and have been manually entering the numbers, such
as:

ai1 1
an2 2
ang3 3

etc.

I still have a long to go, and will try your command. My question
about the command is: In the =right(a1, 1), do the 'a1' and '1' stand
for cell addresses or particular data?

Thanks.

Dalei

 

[David McRitchie]

Hi Dale,
The A1 in the formula is an address not to be confused with the
sample data that you supplied.

A1: a5
B1: =RIGHT(A1, 1) has a value of 5 and displays as 5

See your HELP (F1) file, RIGHT Worksheet Function

Also you would use the fill handle to copy the formula down to
save a lot of typing. Either by dragging it dow, or by doubleclicking
it if there are no gaps in data to the left.
http://www.mvps.org/dmcritchie/excel/fillhand.htm

 

[Dave Peterson]

Just to add to David's reply...

Since you didn't say what column your list was in, I assumed column A, starting
with row 1 (A1).

And the 1 means to take the single rightmost character.

=right(a1,2)
would take the final two characters.

There are a bunch of useful string functions: =right(), =left(), =mid()
that you can use to "take a part" an existing string.

Take a look in Excel's help for more info.