Sort Mixed Info in Excel
- Thread starter Misha
- Start date
- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- Points
- 38
- Location
- Nanaimo, BC, Canada
- Website
- www.excelguru.ca
- Excel Version(s)
- Excel Office 365 Insider
What are you trying to sort it by? I.e. what order would you expect the above to be sorted into?
- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- Points
- 38
- Location
- Nanaimo, BC, Canada
- Website
- www.excelguru.ca
- Excel Version(s)
- Excel Office 365 Insider
Curious... why a formula? It seems to be sorting fine using the built in sort commands from the ribbon...
I guess the prob is that I don't want the numbers with letters attached sorted at the bottom. So, if there is a 1655 I would like 1655-A to follow. These are exhibits for a trial so I also can't pad them with 0000's.
| 4013 |
| 4014 |
| 5050 |
| 8536 |
| 1655-A |
| 1657-A |
| 1657-B |
| 1659-C |
| 1704-A |
| 1712-A |
| 1715-A |
| 1720-A |
| 1726-A |
| 1732-A |
| 1733-A |
| 1851-A |
| 1917-A |
| 1958-A |
| 1958-A |
| 1979-A |
| 2214-A |
| 238-A |
| 238-C |
| 243-A |
| 243-B |
| 2501-DA |
| 2501-DB |
| 2501-LA |
| 2501-NA |
| 2502-2 |
| 2605-A |
| 2607-B |
| 2607-C |
| 2607-D |
| 2607-E |
| 2607-F |
| 2607-G |
| 2607-H |
| 2607-J |
| 3000-S |
| 3001-S |
| 3001-S |
| 3002-S |
| 3003-S |
| 3004-S |
| 3005-S |
| 3006-S |
| 3007-S |
| 3008-S |
| 3009-S |
| 3010-S |
| 3011-S |
| 3012-S |
| 3013-S |
| 314-A |
| 3510-3 |
| 3516-3 |
| 461-A |
| 586-A |
| 586-B |
| 600-A |
| 719-B |
| 720-A |
| 775-A |
| 780-A |
| 781-A |
| 8529-A |
| 8529-B |
| 8529-C |
| 905-A |
| 919-A |
- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- Points
- 38
- Location
- Nanaimo, BC, Canada
- Website
- www.excelguru.ca
- Excel Version(s)
- Excel Office 365 Insider
Ah, okay, I get what you mean.
I'm assuming that there are no mathematical calculations that are ever done off this column, correct? If you convert them to text, then they will sort exactly as you're after.
To do that, you're going to need to select all the cells, then go to Format Cells-->Number-->Text
The rub is that it still won't work properly until you re-commit any "numbers" to the cell so that Excel can reformat them as text properly. To do that, you pretty much just need to go to the cell, press F2, then press Enter.
If you have a lot of them, then you can do it with a macro:
You still need to set the range as text first, then copy that into a standard module (see the steps in my signature), select the cells in question, and run it.
I'm assuming that there are no mathematical calculations that are ever done off this column, correct? If you convert them to text, then they will sort exactly as you're after.
To do that, you're going to need to select all the cells, then go to Format Cells-->Number-->Text
The rub is that it still won't work properly until you re-commit any "numbers" to the cell so that Excel can reformat them as text properly. To do that, you pretty much just need to go to the cell, press F2, then press Enter.
If you have a lot of them, then you can do it with a macro:
Code:
Sub RePaste()
Dim cl as Range
For each cl in Selection
cl.value = cl.text
Next cl
End SubYou still need to set the range as text first, then copy that into a standard module (see the steps in my signature), select the cells in question, and run it.
Correct no mathematical calculations performed. So, I was reformatting the cells using F2 and enter and I noticed the green dash does not appear in the cells that have a number and letter (ie 1655-A). Any thoughts on why?
- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- Points
- 38
- Location
- Nanaimo, BC, Canada
- Website
- www.excelguru.ca
- Excel Version(s)
- Excel Office 365 Insider
Yep. 
The green mark is Excel's way of telling you that the cell format in inconsistent with the others. It's because you've forced it to text rather than it being implicitly converted. It's nothing to worry about.
The green mark is Excel's way of telling you that the cell format in inconsistent with the others. It's because you've forced it to text rather than it being implicitly converted. It's nothing to worry about.