Excel 2007 : Alphanumeric Sorting

**melhalp** · 02-28-2010, 04:52 PM

I have looked at other threads on this topic but I still can't make this work.

I want the data to sort as:

F1
F2
F3
F4....F30

but I get
F1
F10
F11
F12
F13...
F2
F20
F21....
F3
F30

I have tried changing the data into text (i.e. =text(A1,"###") and then sorting but I still get the same thing.

I tried concatenating as well, but no luck there either.

Help!?

**protonLeah** · 02-28-2010, 05:12 PM

Is there a reason why you must type in a "F" prefix rather than using a custom number format of "F#" in the column? Then you would only have to type the digits...

**teylyn** · 02-28-2010, 05:16 PM

Unfortunately, that is the sort order for text, and a value like F22 is text.

To get the sort order you want, you would have to pad the numbers with zeros

F01
F02
F03

etc.

Or use Ben's suggestion with the custom number format.

**melhalp** · 03-01-2010, 06:31 AM

Thanks teylyn and Ben. I padded the numbers and voila - it sorted correctly. I wonder why other support and forum threads gave all those other suggestions that didn't work when there was such a simple solution

**teylyn** · 03-01-2010, 07:16 AM

Glad it worked for you.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save