sort alphanumberic numbers

L

Legacy 33122

Guest
I have a column of data with both straight numbers & numbers with a letter of the alphabet attached to it. i.e.

1.1
1.1a
1.2
1.2a

When I sort this column it keeps the numbers at the top and then sends the numbers with alphabetical letters to the bottom of the sort. Is there anyway to get the sort to place them in numbers and then letters as above?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Excel sorts number & alphanumeric seperately

I think you have two choices:

1. Convert your numbers (the ones without the alpha characters) to alpha as well, and take steps so that new inputs are also text (format block at text not general or number). I find that even if I format pre-input numbers as text they still sort like numbers and I have to convert them (I do this by creating a formula in a spare column:

=""&a1

and then paste special values the results back over the original



2. ALterantively to avoid messing with you original data use an empty column with basically the same calulation as above and sort on that instead of the original input.

please note I just removed a stray single quote from the above formula
 
Upvote 0
That worked an absolute treat, thanks for that. Appreciate your help! Just so I have my head around it, by adding the ' it converts the cell into what?

Regards,

Tim
 
Upvote 0
A text cell - you dont need the ' - though this is how you can force an entry to be text when you input into a non text formatted cell. Please note that I removed the ' from my formula - still works the same just doesnt show the leading '


ttfn benm
 
Upvote 0
I have noted your change in the formula and it does work the same, cheers for that.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top