I searched on google for this and found a number of answers but am either not excel smart or am doing something wrong.

I need to sort a series of values in numeric order including suffixes. The numbers have a variety of formatting (supplied by the client ) relating to a series of standard documents they provide. There are close to 200 values and will more than likely change several times over the course of the project, otherwise I would just enter it manually...

example numbers:

1
2
15
115a
114a.1
81
114.1

in theory they should sort:

1
2
15
81
114a.1
114.1
115a

I've tried using =""&A1 as well as =Text(A1,"000000") in a seperate column and am still getting nowhere with it. Cells have been formatted as numbers, text and general. FWIW - I tried on a small, arbitrary range of 3 digit values with a suffix and couldn't get that to work either so I'm assuming a fair bit of user error.

thanks