How can I sort the contents of one cell

[GLeeds]

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.

 

[Gary''s Student]

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

 

[GLeeds]

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..

 

[GLeeds]

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?

 

[Gary''s Student]

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".

 

[Gord Dibben]

The first column in Data>Text to Columns was probably set to Column Data
Format>Text.

Try setting all columns in Text to Columns as General before you hit Finish.

On second problem...................

Have you tried selecting all rows then Data>Sort>Options>Left to right and
OK?

All selected rows sort for me in 2003.


Gord Dibben MS Excel MVP

 

[GLeeds]

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.

 

[GLeeds]

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...