Sorting numeric and alphanumeric normally

[SoTM]

Before I go completely out of my mind on this, could someone please point me how to do this? I have tried fromatting as text, numbers, as "general". I just cannot get this to work. This is a list of town map and parcels, the parcels have all sorts of letters and some do not. Similar to this:

1
2
3
3a
3b
3c
4
6
7
7b
7c
10
11
12
12a
12b


That is how I would like them to sort.....but they are coming out all sorts of different way depending on if they are as text, or if I sort "anything that looks like a number as a number". The letters will be sorted in a clump after the numbers, or the single digits will be within the double digits, ie 16,17,18,19,2,20....

HELP! Thanks in advance for your time.

[martindwilson]

try assuming your numbers start in a 1 in another column put
=IF(ISNUMBER(RIGHT(A1)+0),TEXT(A1,"0000"),TEXT(LEFT(A1,LEN(A1)-1),"0000")&RIGHT(A1))
fill down and sort by that

[SoTM]

Thank you for the reply. It is still not working with that. That seems to make all numbers 4 "spaces" (ie 0000, 0001).

But, for example, this is still happening:

0088
0089
0001A
0001B

I would want the 0001A and 0001B before 0088 and 0089

[jeffreybrown]

Use the formula Martin provided in post #2 then highlight the two column together.

Right click on the column and go to sort >> custom sort

Sort by column B >> A to Z

Next options you see, select >> Sort numbers and numbers stored as text separately.

[SoTM]

After I highlight both these columns- the original and the one created to insert martins formula- I see no option for custom sort when I right click? When I click the sort tab on the top, there is no option either.

Sorry if these are really basic excel questions....I turly get by on this for work by pretty much luck . These type of sorting problems have plagued me for years!

[jeffreybrown]

I don't have a good answer for you from here, but hoping others will.

In the meantime, maybe this will help...

http://support.microsoft.com/kb/214282

[martindwilson]

there is no custom sort required just select both columns click data click sort then choose sort by column b

http://spreadsheets.about.com/od/oth...4_2003sort.htm