Arrange letters in alpha mode

[makinmomb]

AAADLTA
AAEDLTA
AAIDLTA
AAODLTA
AAUDLTA

I have this on A1

Need formula on b1 to give me following

AAAADLT
AAAAEDLT
AAADILT
AAADLOT
AAADLTU

[FDibbins]

I dont see how a formula with give what you expect, seeing as the text in your answer range does not match the text in your data range?

[BMV]

It is impossible for Excel 2010 without VBA.

[makinmomb]

=REPT("A",LEN(A1)-LEN(SUBSTITUTE(A1,"A",)))&REPT("E",LEN(A1)-LEN(SUBSTITUTE(A1,"E",)))&REPT("I",LEN(A1)-LEN(SUBSTITUTE(A1,"I",)))&REPT("O",LEN(A1)-LEN(SUBSTITUTE(A1,"O",)))&REPT("U",LEN(A1)-LEN(SUBSTITUTE(A1,"U",)))

someone was able to do for vowels only using the below data on a1 and see results on b2

AMANDLA AAA
MANDALA AAA
BALADIN AAI
CANALED AAE
CANDELA AAE
DECANAL AAE
ACNODAL AAO

[menem]

if you don't mind to use helper cells (with limited or fixed length , length of string effect number of formulas)

please try

B1
=SUBSTITUTE(CONCATENATE(P1,Q1,R1,S1,T1,U1,V1,W1,X1,Y1)," ","")

E1
=MID($A1,COLUMNS($D1:D1),1)&" "
copy thru N1

P1
=INDEX($E1:$N1,1,MATCH(SMALL(CODE($E1:$N1),COLUMNS($O1:O1)),CODE($E1:$N1),0))
follow with Ctrl-Shift-Enter for array formula, copy to Y1

Regards.

[makinmomb]

wow so beautiful , thank you so much

[BMV]

makinmomb you have written

I have this on A1

but actually it is not so as i see.

[janmorris]

All letters in alphabetical order (but will not work for your second example because E is alphabetically AFTER D)

If you want D to be after E, then change the order of each section of this formula to be in the order you want the letters.

Please Login or Register  to view this content.

[makinmomb]

thank you jan , i will this too