Sort issue with 1,1a,2,2a,2b etc

[dazacx2]

Hello,

Looking for solution to sort some data,

I have 4 columns with numerical numbers from 1 to 500 with in this range i have a.b.c.d.e. next to various numbers in the last column.
When i use the sort function it will sort the first 3 as normal as they are only numerical when i sort the last column it separates the ones with abcd next them at the bottom i want them all in order.

1
2
3
1a
1b
2a
i want
1
1a
1b
2
2a
2b etc

i have tried both options when the sort comes up with recognise ans number or not it still has not worked.

Any solutions would be great.

Daza

 

[MARK858]

In E2 put the formula

=TEXT(D2,"###")

and drag down.
Select columns D and E and sort on column E.
When the dialog box comes up choose the "Sort numbers and numbers stored at text separately" option.
Delete column E.

 

[dazacx2]

That worked thanks

Daza

 

[dazacx2]

hello,
sorry to reopen this but it only works if it reads direct numbers in the cell, i have formula in the cell to get the numbers and it doesnt sort now,
is there a way to read the data not the formula?

 

[MARK858]

Formulas adjust themselves based on the cell location, you can try making them absolute but I can't remember if they adjust when sorting (and not by a computer to test).

 

[dazacx2]

Formulas adjust themselves based on the cell location, you can try making them absolute but I can't remember if they adjust when sorting (and not by a computer to test).

is there a way of posting the file so you can look at it?

 

[MARK858]

You can upload it to www.box.coorm, mark it for sharing and paste the link it provides in the thread.
Please note that I am at work now and so probably won't look at it until tomorrow.