sorting text with numbers
 

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...

sorting text with numbers
 

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

"wavers3" wrote:

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...

sorting text with numbers
 

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.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"wavers3" wrote:

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...