Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I sort the contents of one cell
I want to sort a cell with text numbers. Example. Cell B3 equals 12 54 36 01
17 23 07 and I would like to sort it low to high. 01 07 12 17 23 36 54. Each number is two digits with a space between each number. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I sort the contents of one cell
1. use Text to columns to copy the cell into a row of single items
2. use copy and pastespecial transpose to put the row into a column 3. sort the column 4. use the CONCATENATE function to re-combine the values -- Gary''s Student - gsnu200860 "GLeeds" wrote: I want to sort a cell with text numbers. Example. Cell B3 equals 12 54 36 01 17 23 07 and I would like to sort it low to high. 01 07 12 17 23 36 54. Each number is two digits with a space between each number. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I sort the contents of one cell
Thank you for your help. I di step one which worked great but when I did step
two the first number in the column is left justify and the other numbers are right. When I sort the left number is always on top and the other numbers do sort. Also, will I be able to use this method with 1400 rows? Thanks again.. "Gary''s Student" wrote: 1. use Text to columns to copy the cell into a row of single items 2. use copy and pastespecial transpose to put the row into a column 3. sort the column 4. use the CONCATENATE function to re-combine the values -- Gary''s Student - gsnu200860 "GLeeds" wrote: I want to sort a cell with text numbers. Example. Cell B3 equals 12 54 36 01 17 23 07 and I would like to sort it low to high. 01 07 12 17 23 36 54. Each number is two digits with a space between each number. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I sort the contents of one cell
The problem with the top number being left justified had something to do with
using the original cell as to where the first number is stored. I don't need to concatencate back. I can highlight the cells and then sort low to high but can only do one row at a time. Is there a faster way since I have 1200+ rows or do I need record a macro? "GLeeds" wrote: Thank you for your help. I di step one which worked great but when I did step two the first number in the column is left justify and the other numbers are right. When I sort the left number is always on top and the other numbers do sort. Also, will I be able to use this method with 1400 rows? Thanks again.. "Gary''s Student" wrote: 1. use Text to columns to copy the cell into a row of single items 2. use copy and pastespecial transpose to put the row into a column 3. sort the column 4. use the CONCATENATE function to re-combine the values -- Gary''s Student - gsnu200860 "GLeeds" wrote: I want to sort a cell with text numbers. Example. Cell B3 equals 12 54 36 01 17 23 07 and I would like to sort it low to high. 01 07 12 17 23 36 54. Each number is two digits with a space between each number. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I sort the contents of one cell
If you are willing to use a macro, check back Monday morning. We can come up
with some code that can actually sort the data "in place". -- Gary''s Student - gsnu200860 "GLeeds" wrote: The problem with the top number being left justified had something to do with using the original cell as to where the first number is stored. I don't need to concatencate back. I can highlight the cells and then sort low to high but can only do one row at a time. Is there a faster way since I have 1200+ rows or do I need record a macro? "GLeeds" wrote: Thank you for your help. I di step one which worked great but when I did step two the first number in the column is left justify and the other numbers are right. When I sort the left number is always on top and the other numbers do sort. Also, will I be able to use this method with 1400 rows? Thanks again.. "Gary''s Student" wrote: 1. use Text to columns to copy the cell into a row of single items 2. use copy and pastespecial transpose to put the row into a column 3. sort the column 4. use the CONCATENATE function to re-combine the values -- Gary''s Student - gsnu200860 "GLeeds" wrote: I want to sort a cell with text numbers. Example. Cell B3 equals 12 54 36 01 17 23 07 and I would like to sort it low to high. 01 07 12 17 23 36 54. Each number is two digits with a space between each number. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I sort the contents of one cell
Thanks a lot. I played around with a couple of macros but I don't know how to
make it skip to the next row. I'm sure I need to add an IF or next statemnet somewhere. If I get it done by Monday I wiull let you know.... Thanks again... "Gary''s Student" wrote: If you are willing to use a macro, check back Monday morning. We can come up with some code that can actually sort the data "in place". -- Gary''s Student - gsnu200860 "GLeeds" wrote: The problem with the top number being left justified had something to do with using the original cell as to where the first number is stored. I don't need to concatencate back. I can highlight the cells and then sort low to high but can only do one row at a time. Is there a faster way since I have 1200+ rows or do I need record a macro? "GLeeds" wrote: Thank you for your help. I di step one which worked great but when I did step two the first number in the column is left justify and the other numbers are right. When I sort the left number is always on top and the other numbers do sort. Also, will I be able to use this method with 1400 rows? Thanks again.. "Gary''s Student" wrote: 1. use Text to columns to copy the cell into a row of single items 2. use copy and pastespecial transpose to put the row into a column 3. sort the column 4. use the CONCATENATE function to re-combine the values -- Gary''s Student - gsnu200860 "GLeeds" wrote: I want to sort a cell with text numbers. Example. Cell B3 equals 12 54 36 01 17 23 07 and I would like to sort it low to high. 01 07 12 17 23 36 54. Each number is two digits with a space between each number. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I sort the contents of one cell
The first column in DataText to Columns was probably set to Column Data
FormatText. Try setting all columns in Text to Columns as General before you hit Finish. On second problem................... Have you tried selecting all rows then DataSortOptionsLeft to right and OK? All selected rows sort for me in 2003. Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 10:44:01 -0700, GLeeds wrote: The problem with the top number being left justified had something to do with using the original cell as to where the first number is stored. I don't need to concatencate back. I can highlight the cells and then sort low to high but can only do one row at a time. Is there a faster way since I have 1200+ rows or do I need record a macro? "GLeeds" wrote: Thank you for your help. I di step one which worked great but when I did step two the first number in the column is left justify and the other numbers are right. When I sort the left number is always on top and the other numbers do sort. Also, will I be able to use this method with 1400 rows? Thanks again.. "Gary''s Student" wrote: 1. use Text to columns to copy the cell into a row of single items 2. use copy and pastespecial transpose to put the row into a column 3. sort the column 4. use the CONCATENATE function to re-combine the values -- Gary''s Student - gsnu200860 "GLeeds" wrote: I want to sort a cell with text numbers. Example. Cell B3 equals 12 54 36 01 17 23 07 and I would like to sort it low to high. 01 07 12 17 23 36 54. Each number is two digits with a space between each number. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I sort the contents of one cell
Thanks for the response.
In 2007 it asks which row to sort by which means to me that you can only sort on row at a time. You can add a level but by the time I add 1200 levels (if it will go that high) I might as well do them one at a time. "Gord Dibben" wrote: The first column in DataText to Columns was probably set to Column Data FormatText. Try setting all columns in Text to Columns as General before you hit Finish. On second problem................... Have you tried selecting all rows then DataSortOptionsLeft to right and OK? All selected rows sort for me in 2003. Gord Dibben MS Excel MVP On Fri, 7 Aug 2009 10:44:01 -0700, GLeeds wrote: The problem with the top number being left justified had something to do with using the original cell as to where the first number is stored. I don't need to concatencate back. I can highlight the cells and then sort low to high but can only do one row at a time. Is there a faster way since I have 1200+ rows or do I need record a macro? "GLeeds" wrote: Thank you for your help. I di step one which worked great but when I did step two the first number in the column is left justify and the other numbers are right. When I sort the left number is always on top and the other numbers do sort. Also, will I be able to use this method with 1400 rows? Thanks again.. "Gary''s Student" wrote: 1. use Text to columns to copy the cell into a row of single items 2. use copy and pastespecial transpose to put the row into a column 3. sort the column 4. use the CONCATENATE function to re-combine the values -- Gary''s Student - gsnu200860 "GLeeds" wrote: I want to sort a cell with text numbers. Example. Cell B3 equals 12 54 36 01 17 23 07 and I would like to sort it low to high. 01 07 12 17 23 36 54. Each number is two digits with a space between each number. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort by cell contents | Excel Discussion (Misc queries) | |||
concatenate contents of cells whose contents resemble cell referem | Excel Worksheet Functions | |||
how do I sort rows by the contents of one cell | Excel Discussion (Misc queries) | |||
Excel Sort Contents of cells | Excel Discussion (Misc queries) | |||
Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) |