|  | Sorting | | |
| | | Joel |  |
|  Posted: Tue Sep 09, 2008 10:43 am Post subject: Sorting |  |
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 |
| |
| | | Bob Phillips | |
| | Posted: Tue Sep 09, 2008 11:00 am Post subject: Re: Sorting | |
Sounds like the cells are formatted as text. Do a Data>Text To
Columns>Finish and see if that helps.
--
__________________________________
HTH
Bob
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:2B186C97-F2F6-46DF-A330-4A6FE5E2D1EC@microsoft.com...
| Quote: | 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 |
|
| |
| | | Joel | |
| | Posted: Tue Sep 09, 2008 11:12 am Post subject: Re: Sorting | |
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.
"Bob Phillips" wrote:
| Quote: | Sounds like the cells are formatted as text. Do a Data>Text To
Columns>Finish and see if that helps.
--
__________________________________
HTH
Bob
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:2B186C97-F2F6-46DF-A330-4A6FE5E2D1EC@microsoft.com...
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
|
|
| |
| | | Shane Devenshire | |
| | Posted: Tue Sep 09, 2008 11:13 am Post subject: Re: Sorting | |
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
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:2B186C97-F2F6-46DF-A330-4A6FE5E2D1EC@microsoft.com...
| Quote: | 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 |
|
| |
| | | Bob Phillips | |
| | Posted: Tue Sep 09, 2008 11:23 am Post subject: Re: Sorting | |
| |  | |
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
--
__________________________________
HTH
Bob
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:46AB63C7-AB39-497E-BEF3-3F1366169C7C@microsoft.com...
| Quote: | 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.
"Bob Phillips" wrote:
Sounds like the cells are formatted as text. Do a Data>Text To
Columns>Finish and see if that helps.
--
__________________________________
HTH
Bob
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:2B186C97-F2F6-46DF-A330-4A6FE5E2D1EC@microsoft.com...
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
|
|
| |
| | | Joel | |
| | Posted: Tue Sep 09, 2008 11:55 am Post subject: Re: Sorting | |
| | | |
That sort of worked, but it did not put the dot/period between each octet.
just showed 111222333444.
"Bob Phillips" wrote:
| Quote: | 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
--
__________________________________
HTH
Bob
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:46AB63C7-AB39-497E-BEF3-3F1366169C7C@microsoft.com...
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.
"Bob Phillips" wrote:
Sounds like the cells are formatted as text. Do a Data>Text To
Columns>Finish and see if that helps.
--
__________________________________
HTH
Bob
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:2B186C97-F2F6-46DF-A330-4A6FE5E2D1EC@microsoft.com...
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
|
|
| |
| | | Joel | |
| | Posted: Tue Sep 09, 2008 11:58 am Post subject: Re: Sorting | |
| | | |
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.
"Shane Devenshire" wrote:
| Quote: | 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
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:2B186C97-F2F6-46DF-A330-4A6FE5E2D1EC@microsoft.com...
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
|
|
| |
| | | Bob Phillips | |
| | Posted: Tue Sep 09, 2008 12:05 pm Post subject: Re: Sorting | |
| | | |
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.
--
__________________________________
HTH
Bob
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:535FA85C-DD19-4145-9465-81FA1726CBCB@microsoft.com...
| Quote: | That sort of worked, but it did not put the dot/period between each octet.
just showed 111222333444.
"Bob Phillips" wrote:
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
--
__________________________________
HTH
Bob
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:46AB63C7-AB39-497E-BEF3-3F1366169C7C@microsoft.com...
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.
"Bob Phillips" wrote:
Sounds like the cells are formatted as text. Do a Data>Text To
Columns>Finish and see if that helps.
--
__________________________________
HTH
Bob
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:2B186C97-F2F6-46DF-A330-4A6FE5E2D1EC@microsoft.com...
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
|
|
| |
| | | Traveller | |
| | Posted: Tue Sep 09, 2008 1:24 pm Post subject: RE: Sorting | |
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
jim.cone@rcn.com.
"Joel" wrote:
| Quote: | 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 |
|
| |
|
|
