sorting error

[Guest]

Suppose I have 6Y,3M in one cell 6Y,7M in another cell and 6Y,10M in another
cell.While I am sorting in descending order 6Y,10M shoul come first then
6Y,7M, then 6Y,3M should come. But in my case 6Y,10M comes last.But if I am
inserting one 0 my problem can be solved ,but without this how can it be
done.Or If I have to insert 0 , how this process be automated by using any
function.becauz I have lots of data , so 0 cann't be inseted manually.

 

[Jim Cone]

ananga,

Since you asked "how this process be automated"
Maybe this recent post of mine will help...

http://makeashorterlink.com/?R2B662F4B

The code in the post creates additional data columns that
can be used to sort your data.

Jim Cone
San Francisco, USA


Suppose I have 6Y,3M in one cell 6Y,7M in another cell and 6Y,10M in another
cell.While I am sorting in descending order 6Y,10M shoul come first then
6Y,7M, then 6Y,3M should come. But in my case 6Y,10M comes last.But if I am
inserting one 0 my problem can be solved ,but without this how can it be
done.Or If I have to insert 0 , how this process be automated by using any
function.becauz I have lots of data , so 0 cann't be inseted manually.

 

[Guest]

Hi,
If there are always two characters (e.g., 6Y or something like it)
before the comma, and only one letter at the right hand side end (e.g., 7M or
10M and not 7NM or 10NM), you can create a helper column with a formula that
would extract the numbers by which you can sort the spreadsheet.
For example, if your data are in column A starting at row 2, enter the
following formula in a row 2 of a blank column (say B2)

=(LEFT(MID(A2,4,20),LEN(MID(A2,4,20))-1))*1

and fill-in the column down to the last data-containing row. Now, sort the
spreadsheet by column B in descending order.
Regards,
B.R.Ramachandran