Sorting the contents of a text string in Excel in Alphabetical order.
Hello Gurus,
I have the need to sort a text string in alphabetical order. (I would prefer a formula based solution if possible.)
Contents in cell A1: A123,B456,C789
Desired Output: ,,123456789ABC
As far as the order of the output, I don't really care. Could be Numbers, Letters, Special Characters or any mix therein...
Can this be done?
Thank you.
I have the need to sort a text string in alphabetical order. (I would prefer a formula based solution if possible.)
Contents in cell A1: A123,B456,C789
Desired Output: ,,123456789ABC
As far as the order of the output, I don't really care. Could be Numbers, Letters, Special Characters or any mix therein...
Can this be done?
Thank you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have been looking at using a User Defined Function.
I can get the characters from the cell into a comma separated array but I can't find a way of then sorting that array. Still working on it though!!
I can get the characters from the cell into a comma separated array but I can't find a way of then sorting that array. Still working on it though!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the string has unicode characters, then the buckets array would increase to 64K. I would probably use the StrConv() function instead of the Asc(). Alternatively, I could use the AscW() and ChrW() functions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to everyone for your assistance.
ASKER
The need for this came from a problem that I posed in this post: Index/Match Question
Your solution does accomplish the goal. However, I have some text strings that are LEN=305+ characters in length.
The reason I want this is because I have a spreadsheet which contains Bill of Material data which quantities are suspect of being incorrect.
Sheet1 Columns (Model, ChildPN, Qty, DataPoint) (No ParentPN column)
Sheet2 Columns (ParentPN, ChildPN, Qty, DataPoint) (No Model column)
Trying to find a way to join the data to pull in ParentPN to Sheet1 and Model to Sheet2 has been challenging. I have created helper columns for an INDEX/MATCH formula, but it has proven to be inaccurate.
While Sheet1 quantities are suspect, the DataPoint fields are out of order between Sheet1 and Sheet 2, but the data is the same. Sorting these fields in alphabetical will provide a unique helper column to do my index/match formula. There is likely a much better way, but I don't know how to get there quickly.
Thank you. While your solution does the trick, can you think of a way to do this without all the needed helper fields?
I appreciate your help.