sorting text with numbers

W

wavers3

I have been reading for the better part of two hours trying all of your
suggestions. I am still unable to get the following data to sort. I have
formatted the cells. The data is similar to this:
1
2
5
8
10
13
3a
3b
4a
6a
7a
7b
9a
11a
12a
12b

With data going on up into the hundreds.
trying all the formulas that were out there did not bring me even close. I
am using 2007.

Thank you for your help...
 
M

Mike H

Hi,

You need a helper column. Assuming your data in a1 down put this in B1 and
fill down. Now select both columns and sort on column B which can then be
hidden

=LOOKUP(6.022*10^23,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Mike
 
L

Luke M

Assuming the data you presented was in the order you wanted, and the letters
that are tagged are never more than 1 character, you could create 2 helper
columns.

Helper column 1:
=ISTEXT(A2)

Helper column 2:
=IF(ISNUMBER(A2),A2,VALUE(LEFT(A2,LEN(A2)-1)))

Then go to Data, sort. Sort by Helper 1, then Helper 2.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top