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!

 

[mikerickson]

This sorts text data by the first four characters. The formula uses two Names, a named range (dataRange) and
Name:null4 RefersTo:CHAR(1)&CHAR(1)&CHAR(1)&CHAR(1)

Put this array formula in B1 and drag right:

=INDEX(dataRange,1, _
  MATCH(SMALL(((256^3)*CODE(MID(dataRange&null4,1,1)))+((256^2)*CODE(MID(dataRange&null4,2,1)))+((256^1)*CODE(MID(dataRange&null4,3,1)))+CODE(MID(dataRange&null4,4,1)),COLUMN(A1)) _
  ,((256^3)*CODE(MID(dataRange&null4,1,1)))+((256^2)*CODE(MID(dataRange&null4,2,1)))+((256^1)*CODE(MID(dataRange&null4,3,1)))+CODE(MID(dataRange&null4,4,1)),0))

The underline is where the formula can be adjusted for different start locations.

Its based on this array of numbers.
((256^3)*CODE(MID(dataRange&null4,1,1))) + ((256^2)*CODE(MID(dataRange&null4,2,1))) +((256^1)*CODE(MID(dataRange&null4,3,1))) + CODE(MID(dataRange&null4,4,1))
which converts the first 4 characters of each string into a number.


(Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac))

 

[lenze]

Nice formula, Mike

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

Can't you educate these weird people?

lenze

 

[mikerickson]

Thanks, Lenze.

 

[Yogi Anand]

Hi KaiKai:

here is one way using a Helper Column ... regardless of the length of the string in each cell ...

Book1
	A	B	C	D
1	Unsorted	AutoSortedAscending	HelperColumn
2	fgh	aac	11.334002
3	xyz	abckmnopqrstwzx	13.332003
4		abcmnopqrst	1000000
5	abcmnopqrst	bac	10.79112846
6	abckmnopqrstwzx	def	10.79091835
7	def	fgh	11.112007
8	bac	xyz	10.869008
9	aac		10.769009
Sheet9 (2)

formula in cell B2 is ...

=INDEX($A$2:$A$9,MATCH(SMALL($C$2:$C$9,ROWS($1:1)),$C$2:$C$9,0))

and formula in cell C2 is ...

=IF(A2="",10^6,SUMPRODUCT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*10^-ROW(INDIRECT(LEN(A2)&":1"))))+ROW()/10^6

because of your personal preference and / or projectr constraints this approach may or may not work for you.

 

[KaiKai]

Hi everybody!

Thanks for your help!
We now found a solution that is quite similar to Yogi's, but even a little bit shorter:
We use the same helper column with this formula:
=SUMPRODUCT(N(A$2:A$1000<A2))+(A2="")*9^99+ROW()%%%
In column B we use the same formula as Yogi.

I thank everybody for the support!

 

[KaiKai]

Here's the complete formula again:

=SUMPRODUCT(N(A$2:A$1000<A2))+(A2="")*9^99+ROW()%%%

<d2))+(d2="")*9^99+zeile()%%%[ php]=""><a2))+(a2="")*9^99+row()%%%[ code]=""></a2))+(a2="")*9^99+row()%%%[></d2))+(d2="")*9^99+zeile()%%%[>