Sorting text within a single cell

Hi,

Someone ten years ago posted this VBA code which created a function which sorts text in an excel cell. Problem with the code is that it removes the spaces if any of the text is made up of more than one word, i.e Sourcing & Admin is changed to be Sourcing&Admin. Any ideas on how to change the code to leave the spaces in.

This is the VBA code:

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

This is the formula in Excel:

Formula:

Please Login or Register  to view this content.

Many thanks

Just delete (or comment out) the first line of code. The first thing this function does is delete all the spaces. I don't even know why that would be there.

Please Login or Register  to view this content.

Thanks for the quick reply, that didn't work (it needs cell to sort string to have value as used later) but it put me on the right track as instead of replacing spaces with nothing I've changed it to replace comma spaces with commas so now only removes the spaces between the different options.

Formula:

Please Login or Register  to view this content.

I have a follow up question though if that's allowed. Can I get the above code to work as a macro instead of needing an excel formula to use it. I'm tagging this on the back of a private worksheet change code which allows data validation drop downs to select multiple entries in a single cell (hence the need to have it sort)

Please Login or Register  to view this content.

You can do that with any function. For example this sorts the text in cell A1:

Please Login or Register  to view this content.

Here is a correction to my correction which I've actually tested. The error I made was the code used an intermediate variable and I did not notice. Maybe the change you made works better for your specific purposes, but my change does specifically what was asked in the original post. Take your pick.

Please Login or Register  to view this content.