Sorting by number values

**raze** · 01-13-2013, 03:06 PM

I have a column with the text and letters that look like this"

Protocol 01
Protocol 02

and so on to protocol 155

When i use this macro to sort it doesn't put them into the proper order. Example it will put Protocol 10 and Protocol 110 in consecutive order vs protocol 110 being much further in the column. I searched this forum for more than an hour looking for a fix.

Please Login or Register  to view this content.

**martindwilson** · 01-13-2013, 05:27 PM

you will need the code to pad out the number. so protocol 01 becomes protocol 0001 and protocol 150 becomes protocol 0150
so when sorted it will do it correctly,not that i know how to do that! i can do it with a formula
in another column put
=LEFT(A1,FIND(" ",A1))&TEXT(MID(A1,FIND(" ",A1)+1,10),"0000") where a1 is your first cell with protocol xx in ,fill down then sort by that

**Ghozi Alkatiri** · 01-13-2013, 07:41 PM

ie data you want to sort at A1:A155
the result at B1:B155
the first formula at B1 (array formula)
{="protocol "&TEXT(SMALL(--SUBSTITUTE($A$1:$A$155;"protocol";"");ROW(1:1));"00")}
copy down

**Ghozi Alkatiri** · 01-13-2013, 07:47 PM

ie data you want to sort at A1:A155
the result at B1:B155
the first formula at B1 (array formula)
{="protocol "&TEXT(SMALL(--SUBSTITUTE($A$1:$A$155;"protocol";"");ROW(1:1));"00")}
copy down