Can you sort data alphabetically in one particular cell?

Column K, Row 4 (or pick a cell) contains the following information:

Life, Accident & Health, Variable, Legal, Property

I would like to be able to sort the data in this one cell alphabetically without retyping the data......ie use a sort function but only for a cell.

In other words, sort the data so that it appears; Accident & Health, Legal, Life, Property, Variable

Does this function exist?

"Does this function exist?"

No.

From excel's perspective, it's just a text string, it doesn't "understand" what it's got, it merely "holds" it, it can't really break it into smaller strings and order them. A function like PROPER() doesn't understand what it's doing, it just follows a rule: make all letters lower-case, except the first letter after a space character, which should be upper case. Excel can't "read".

You could probably write a macro to do this, though.

Can be done with a user defined function.

Please Login or Register  to view this content.

Alt F11 to open the VBA editor. Insert - module. Paste in the above. Alt F11 back to worksheet,

In B1 enter

=SortWithinCell(A1,",",TRUE)

assuming starting string is in A1

Does the function work for you or do you want to know how it is working?

Originally Posted by mrice

Does the function work for you or do you want to know how it is working?

I followed your instructions but its not working. Let me try and help make this a bit easier on both of us.

The data is in column J cells 1 through 34610. Once I delimit it (after your solution) it will be located in Column J, Column K, Column L, Column M, Column N, Column O, Column P and Column Q

here is current column J data:

LINE_OF_AUTHORITY
Life,Accident & Health
Life,Accident & Health
Life,Accident & Health
Credit Products,Property,Vehicle Physical Damage
Property,Casualty
Life,Variable Contracts,Vehicle Physical Damage,Casualty,Property,Accident & Health
Life,Variable Contracts,Credit Products,Property,Casualty,Vehicle Physical Damage,Accident & Health
Life,Accident & Health
Life
Property,Vehicle Physical Damage,Casualty
Life,Credit Products
Life,Property,Accident & Health,Vehicle Physical Damage,Property (Industrial Fire),Casualty
Casualty,Property
Life,Property (Industrial Fire),Accident & Health
Life,Casualty,Vehicle Physical Damage,Variable Contracts,Credit Products,Property,Accident & Health
Life,Accident & Health
Life,Variable Contracts,Accident & Health

Please see the attachment to make things clearer.

Originally Posted by mrice

Please see the attachment to make things clearer.

I'm clearly missing something, very sorry for drilling this down.....


I've gone ahead and put in the =SortWithinCell (J2,",",TRUE) but it is returning a #NAME? error. FYI - I did not go the alt-11 route, will I still need to do that in order for the formula to work correctly?

Truly appreciate your help.

Ignore, ignore....I've got it figured out...you are AWESOME!!!!!!!!!!!

Damn it!!!!!!!!!! Now that I've got the sort function figured out, when I go and try to delimit the data into individual columns, Excel is now deliminitating the =SortWithinCell(J1,",".TRUE) instead of the text within the box. Now what?

Hi Martin
I have cells containing data in a list i.e. delimited with a carriage return not a comma, how do i amend your formula to cater for this and alphabetically arrange the data back into a list within the cell? At the moment I have a list of text values in each cell each consisting of 5 characters e.g. B0690 your solution above gives me an unsorted concatination e.g. B0690B0720B0760B0830B0837B0955B1058B0625 and I need a list (one beneath the other) of alphabetically sorted values. thanks Mike

So how do I get it to do it in reverse order

Hi ,

I would like to sort one word per cell.
For example

JCDELBVGAQS

result:
ABCDEHJLQSV

Thannks,
Archana

Is it possible to have it ready and available everytime you open a new excel file or will have to paste the code into every new worksheet?

How did you fix this? I'm trying to sort but have the #name error as well.

you need to insert module in vba editor over the sheet you want to make it work with and paste that code shared above and simply close the editor. it doesn't matter the way you opened vba editor.

Originally Posted by obethgarcia1

How did you fix this? I'm trying to sort but have the #name error as well.

you need to insert module in vba editor over the sheet you want to make it work with and paste that code shared above and simply close the editor. it doesn't matter the way you opened vba editor.