Sorting

J

Joel

I need to sort a rangeof number that contain from 1 to 3 numbers. for
example, 1, 11, 100. When I sort i get numbers 1 then 100 and then 11. Is
htere anyway around this without adding zeros to the numbers?

Joel
 
B

Bob Phillips

Sounds like the cells are formatted as text. Do a Data>Text To
Columns>Finish and see if that helps.
 
J

Joel

Nope, no change. The fields I am sorting are ip addresses so they have a
period in them. example. 111.222.333.1, 111.222.333.11 and 111.222.333.100.
 
S

Shane Devenshire

Hi,

Your numbers are stored as text. Convert them to numbers and everything
should work fine. If there is a green triangle displayed on the cells,
select all the cells and put your mouse over that triangle, open the
resulting drop down and choose Convert to Number.

Alternatively you can select a blank cell and copy it. Then select all the
text numbers and choose Edit, Paste Special, Add.

Cheers,
Shane Devenshire
Microsoft Excel MVP
 
B

Bob Phillips

That is different.

Tis is a bit klunky, but you could add this to column B

=TEXT(LEFT(A1,FIND(".",A1)-1),"000")&TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-(FIND(".",A1)+1)),"000")
&TEXT(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1)+1)-(FIND(".",A1)+1)),"000")
&TEXT(MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,FIND(".",A1,FIND(".",A1)+1)-(FIND(".",A1)+1)),"000")

copy down and sort both columns by B
 
J

Joel

That sort of worked, but it did not put the dot/period between each octet.
just showed 111222333444.
 
J

Joel

already tried that. the numbers that I am sorting are ip addresses. makes
it a little more difficult. 111.222.333.1, 111.222.333.11 and
111.222.333.100 do not sort ascending like i need them too.
 
B

Bob Phillips

Yes Joel, but that was just a scratch field it turned them into numbers so
that it WOULD sort correctly. You still have the original column which
should be part of the sort.
 
T

Traveller

An add-in called "Special Sort" does exactly what you want. (Use "All Data -
Last nums"). It comes from Jim Cone of Primitive Software. His email is
[email protected].
 
Top