Sorting

  • Thread starter Thread starter Joel
  • Start date Start date
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
 
Sounds like the cells are formatted as text. Do a Data>Text To
Columns>Finish and see if that helps.
 
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.
 
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
 
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
 
That sort of worked, but it did not put the dot/period between each octet.
just showed 111222333444.
 
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.
 
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.
 
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
(e-mail address removed).
 
Back
Top