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.

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.

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.

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.

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.

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.

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.

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.