Sorting the contents of a cell

  • Thread starter Thread starter Jack Brown
  • Start date Start date
J

Jack Brown

I would like to sort the contents of a cell.

For example, let us assume that cell A1 contains the
text "SORTS". In cell B1 I would like to get the result
as "ORSST" i.e. the contents of cell A1 but sorted.

Sorting order can be ascending or descending, it is not
important for me.

You can assume that cells are formatted as Text.

Same letter could occur more than once in the cell.

If it helps you then I can tell you that the cell could
only have the following letters "A", "I", "P", "R", "S".

Thanks in anticipation of your valued ideas.
 
I would like to sort the contents of a cell.

For example, let us assume that cell A1 contains the
text "SORTS". In cell B1 I would like to get the result
as "ORSST" i.e. the contents of cell A1 but sorted.

Sorting order can be ascending or descending, it is not
important for me.
Click to expand...
...

The general solution requires either add-in or user-defined functions because
Excel provides no generalized concatenation function or operator, only pairwise
concatentation. If you used Laurent Longre's MOREFUNC.XLL add-in, available from

http://longre.free.fr/english/

it provides a function named MCONCAT which could be used in array formulas like

=MCONCAT(CHAR(SMALL(CODE(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)),
ROW(INDIRECT("1:"&LEN(A3))))))

There are a few udf appearing in this newsgroup's archives if you can't use
MOREFUNC.XLL.

If you're always dealing with strings of exactly 5 characters, then you could
use the brute force equivalent of the formula above.

=CHAR(SMALL(CODE(MID(A3,{1;2;3;4;5},1)),1))
&CHAR(SMALL(CODE(MID(A3,{1;2;3;4;5},1)),2))
&CHAR(SMALL(CODE(MID(A3,{1;2;3;4;5},1)),3))
&CHAR(SMALL(CODE(MID(A3,{1;2;3;4;5},1)),4))
&CHAR(SMALL(CODE(MID(A3,{1;2;3;4;5},1)),5))
If it helps you then I can tell you that the cell could
only have the following letters "A", "I", "P", "R", "S".
Click to expand...

If you need the result to be text, that doesn't help since the big problem is
concatenating the letters.
 
Back
Top