How to sort in within a cell alphabetically For Excel

I have a list of names in one cell and I am trying to sort them alphabetically. I am having trouble finding some way to make this happened. I made unaware of any Macro code that would be able to sort the string text in one cell alphabetically. I have a list of names that go over ten columns and more than one name in each cell. Understand I am not trying to sort a column, I am trying to sort text within one cell, not a column. Please help if you can. Thank you in advance.

Attached is an example of an excel sheet, so you are able to see what I am trying to do.

The big question is... how are the multiple values in a single cell separated?? Here, I have assumed that they have been separated by a "carriage return", i.e. CHAR(10). So, to sort text values in a single cell, ensure text wrapping is on and use:


=TEXTJOIN(CHAR(10),TRUE,SORT(FILTERXML("<A><B>"&SUBSTITUTE(A1,CHAR(10),"</B><B>")&"</B></A>","//B")))

That is because you didn't tell us how the names were separated. I TOLD you that I had assumed they were carriage return separated. It turns out that they're space separated, so:

=TEXTJOIN(" ",TRUE,SORT(FILTERXML("<A><B>"&SUBSTITUTE(A1," ","</B><B>")&"</B></A>","//B")))

Hello Glenn,

Can you explain how this formula works?

=TEXTJOIN(" ",TRUE,SORT(FILTERXML("<A><B>"&SUBSTITUTE(A1," ","</B><B>")&"</B></A>","//B")))

The formula relies on FILTERXML, very useful for all sorts of parsing and which has the following syntax:

=FILTERXML(XML_string,XPATH)

So the first part is to convert your cell to a valid XML string. If you think of your string like a family tree… the whole string is a parent, and the individual names are children. So to get a valid XML string you need one identifier (Node) for the entire string (I used the letter A) and one to designate each child separately (I used the letter B).

Within the string, the names are space separated, to I replaced them with a B-B node separator:
SUBSTITUTE(A1," ","</B><B>")

To complete a valid XML string, I added "<A><B>" at the start and </B></A>" at the end. The XML string is then fed into FILTERXML where the XPATH bit… "//B" isolates all the B nodes (ie the names) in a semicolon-separated array.

SORT then sorts them into alphabetical order and TEXTJOIN joins them back together into a single string, again separated by a space.

Clear enough??

Hello Glenn,

Thank you so much for this. It has been a huge help you have no idea.

Jumper23

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar or even the same to this thread, we have a rule that you open your own thread on the issue and do not piggy back another member's thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread