Ordering a list within a cell (comma separator)

mawheele

New Member
Joined
Jul 16, 2007
Messages
14
Is there a way to order a list within a cell? The entries have both letters and numbers. I'm assuming I should use Split to separate the entries. I have a column of cells similar to:

CC-23601, CC-23702, CC-23801, CC-23802, CC-23803, CC-23804, CC-23701

I want to put them in ascending order (in the same cell). I already have a loop set up to go through the column. Should I create an array, then sort that array? If so, how would I go about this?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
One way around this would be to use Data>TextToColumns. Set commas and spaces as the delimiters. Then once the values are separated, copy them and Edit>PasteSpecial>Transpose to convert the row of values into a column of values. Even with the letters in the cells, you should be able to just do a basic sort to put these in order and then Copy>Transpose them back into a row.
 

mawheele

New Member
Joined
Jul 16, 2007
Messages
14
Sorting

Thats actually a method I considered, however, after I Text to Columns, then transpose, and try to sort, it won't sort them correctly because, I'm assuming because they numbers have "CC-" before them and excel is considering them all the same.. I don't see why it would do this, but thats the only reason I can think of why its not sorting correctly. I would do a find and replace and get rid of them, but all the numbers are different. So at this point, there has gotta be an easier way using a macro. I just can't believe there is a sorting method using arrays.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Interesting....when I tried sorting your numbers, it worked even with the letters there. You could always TextToColumns again after transposing to split the cell into it's 2 parts, letters and numbers.
 

Forum statistics

Threads
1,181,730
Messages
5,931,703
Members
436,799
Latest member
BasOo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top