Formula to sort in alphabetical order

[KaiKai]

Hi!

Once again, I need your help, please!
I have some data in A2:Axx, it could be like this:
fgh;;;xyz;;stu;mno;;;;;;;;;xyz;;;;abc;abc;;def;;;;;;abc
(semicolons represent blanks)

Now I need a formula in B2 that sorts my data in alphabetical order, disregarding the blanks, so that B2:Byy is like

abc;abc;abc;def;fgh;mno;stu;xyz;xyz;;;;;;...

Is there a way?
I mustn't use VBA, since the file is to be sent to very weird people with strange options to activate any VBA...

Thanks a lot in advance!

 

[lenze]

Can't you just select your range and hit the Sort button on the standard Tool Bar?

lenze

 

[KaiKai]

Hi lenze!

That'd be toooooo easy!
The "new" data in B2:Byy is used in some other sheets for several calculations.
I'm quite aware of Excel's power (but not everything, as you see), but my "opponent" is not, that's why!
It needs to change dynamicly.

Do you have an idea for that, too?

Thanks a lot!

 

[lenze]

So, record a macro copying column "A" to Column "B" and then sorting Column "B". Assign it to a button or Shortcut Key.

lenze

 

[KaiKai]

Hi lenze!

Sorry, but I mustn't use VBA!
That would be easy for you, for them and for me (of course).
The file still has to be dynamic, when I e-mailed it! Without VBA!
So the whole thing has to be done by formula(s).
I could have written a VBA-script within minutes, but that's, as I said, not possible!

So, I definitely need a solution based on a formula, please!

 

[KaiKai]

Hi again!

I have a formula to list my data without blanks:
{=IF(ROW(A1)>COUNTA(A:A),"",INDEX(A:A,SMALL(IF(A$1:A$1000<>"",ROW($1:$1000)),ROW(A1))))}

But I still can't find a way to sort this in alphabetical order.
Maybe this formula helps to make it an "easier" problem.

Of course, I might use extra columns, since there are a lot available, but it should be solved with as few extra columns as possible, 'cause there are several columns that need to be sorted, each one for its own.

 

[mikerickson]

I don't know if it counts a VBA, but there is a UDF in this thread:
http://www.vbaexpress.com/forum/showthread.php?t=20494
that sorts. It will need to be tweeked to sort horizontaly, but if a UDF will work for you, let me know.

 

[KaiKai]

Hi Mike!

Absolutely NO VBA!
No UDF, no script, no macro, no whatever...
They don't accept anything that might course anything else...

I really need a solution that is only with a "normal" formula, please!
It has to be possible, please!!!

 

[mikerickson]

Is there a maximum length to the strings?

 

[KaiKai]

No, not as far as I know.
But I think it should be 30 or less, probably even 20 or less per cell.