Sorting mixed numeric values

**cellophane** · 05-30-2012, 06:11 PM

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

**Cutter** · 05-30-2012, 06:21 PM

See this thread from earlier today: http://www.excelforum.com/excel-2007...16#post2806216

Change one step for your scenario. When the Sort Warning pops up choose the first option.

**cellophane** · 05-30-2012, 06:37 PM

Still no luck =/ It sorted numbers, then letters and is still putting values in the wrong order. Again - I'll assume user error since I can get almost there.

After sorting I'm getting the following:

161
800
114a
114a.1
14m
150m
150s
15a

14m and 15a should be well before any of those values.

If I pick the second option in the sort warning it sorts almost correctly. Everything is in the correct order by the first digit, however 2 ends up 20 spots down the list after everything with a 1 in front:

12
114a
114a.1
115
2

**Cutter** · 05-30-2012, 06:48 PM

Yes, it worked on your first sample but not your second one. I'll take a closer look.

**martindwilson** · 05-30-2012, 07:13 PM

best i can get it is
1
2
15
115a
114a.1
81
114.1
12
161
to sort as
1
2
12
15
81
114.1
114a.1
115a
161

using as helper =IF(ISNUMBER(A1+0),TEXT(A1+0,"0000000"),"0000"&SUBSTITUTE(A1,".",""))

**Cutter** · 05-30-2012, 07:21 PM

EDIT: I was going to suggest VBA but I see MDW has offered a solution.

**cellophane** · 05-31-2012, 07:35 AM

Originally Posted by Cutter

Yes, it worked on your first sample but not your second one. I'll take a closer look.

Sorry about that. I didn't realize it would make a difference =/

MDW: Thanks - I'm about to be late for a meeting but I'll give it a go this afternoon. I do have one question - could you translate all that and explain what the functions mean? I try to learn this stuff when I can but it honestly is like a foreign language to me