Excel 2007 : Help with Sorting numbers and text

**suzyblue** · 05-29-2012, 05:16 PM

I know this issue has been addressed before but never having been formally trained in Excel, I am ignorant enough to not really understand how to make the answers I found work for me.

I work with architectural drawings on a daily basis and I need to sort room numbers which include text.
Example: 101, 102, 102A, 102B, 102C, 103, 104, 105, 105A, 105B, 106...etc. When I try to sort them the text with letters always end up at the end of the list and I have to cut and paste to put them in order. Can someone give me an easy step-by-step way to resolve this?

Thank you.

**Richard Buttrey** · 05-29-2012, 05:51 PM

Hi,

One way would be to use a helper column.
=TEXT(A1,0)

then sort the data including the helper column specifying the helper column as the key and pick the second option you see.

Regards

**suzyblue** · 05-30-2012, 09:07 AM

I don't understand. Where do I insert the formula? I get that I create a new adjacent "helper" column but I don't understand where to insert the formula. When I do the sort and it asks if I want to sort as text or numbers what do I say. As I stated I am self-taught so I need the instructions spelled step-by-step out for me.

**martindwilson** · 05-30-2012, 09:32 AM

never having been formally trained in Excel

join the club, but this is what richard means

**Cutter** · 05-30-2012, 09:35 AM

Richard was suggesting that you use this method:

Have your list of room designations in range A1:A100 (or whatever other # instead of 100)
Use column B (or any other column) as the helper.
In the first cell of the helper place the suggested formula: =TEXT(A1,0) and drag it down as far as needed
Select the entire range to be sorted.
Click on Data - Sort and select the helper column as the sort key (the Sort By field)
You will get a "Sort Warning" pop up. Choose the "Sort numbers and numbers stored as text separately" option
Done

**suzyblue** · 05-30-2012, 01:32 PM

I did this and it appears that the same information in column A is carried over to the helper column. When I sort it still wants to separate the numbers that have text from the ones that don't.

If I start with this
101
102
102A
102B
102C
103
104
105
What I end up with is this:
101
102
103
104
105
102A
102B
102C