# Advanced Formula Challenge #11: All in Order 28

The challenge this week is as follows: given a value in A2, where A2 is a string consisting of upper-case letters of the alphabet only and of minimum character-length 2, a formula to be entered into B2 such that, if and only if the individual characters within the string in A2 are in ascending alphabetical order (from left to right), return TRUE; otherwise return FALSE.

Examples below: Solution next week. Best of luck!

1. Hi excelxor,

`=AND(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1)<=MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1))`

Great website by the way!

2. @Oscar

Nice! Can’t fault that for a solution. First answer received, and a correct one.

Presume that’s an array formula, by the way?

And thanks – glad you like it!

Cheers

Here is a solution for Google Sheets:

`=A2=join(,sort(transpose(split(regexreplace(A2,,"."),"."))))`
4. Thanks!

Yes, it’s an array formula.

5. Thanks, Isai! Keep them coming for the Sheets fans out there!

Cheers

6. Daniel says:
`=AND(MID(A6,ROW(INDIRECT("1:"&LEN(A6)-1))+1,1)>=MID(A6,ROW(INDIRECT("1:"&LEN(A6)-1)),1))`

CSE formula

7. diondan1 says:
`=AND(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1))+1,1)>=MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1))`

Array formula

8. @Daniel

Great! Slight variation on the solution of Oscar, and equally good!

Cheers

9. Bill Szysz says:

A slightly different solution.

`=AND(MMULT(CODE(MID(A2,ROW(INDIRECT("\$1:"&LEN(A2)-1))+{0;1},1))*{-1;1},{1,1})>=0)`

Ordinary, not CSE.

Regards

10. Thanks, Bill!

Almost word-for-word identical to mine!

But have you double-checked this? Why are you using MMULT with {1,1} AND multiplication with {-1;1}? Is this intentional? And are you sure you’ve got the displacement of your addition vector – {0;1} – here correct?

Regards

11. Bill Szysz says:

Hi XOR LX,

You are right… this part is not necessary… I put it for better reading the first array of MMULT function only… so…we can remove this part

*{-1;1}

and change this part

{1,1} to this {-1,1}

We will get

`=AND(MMULT(CODE(MID(A2,ROW(INDIRECT("\$1:"&LEN(A2)-1))+{0;1},1)),{-1,1})>=0)`

And yes {0;1} is intentionally.

Of course we can use RIGHT function also.

`=AND(MMULT(CODE(RIGHT(A2,ROW(INDIRECT("2:"&LEN(A2)))-{0;1})),{-1,1})>=0)`

Regards

12. ikkeman says:

getting rid of the volatile indirect function

CSE

`=AND(MID(A16,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A16)-1)),1)<=MID(A16,ROW(INDEX(A:A,2):INDEX(A:A,LEN(A16))),1))`
13. @Bill

Not sure I understand.

Unless your system settings are somehow different, a semi-colon in Excel represents a row separator. Hence, when you perform the addition of:

`ROW(INDIRECT("\$1:"&LEN(A2)-1))`

and:

`{0;1}`

both of which are single-column arrays, you are adding two arrays of the same orthogonality.

Hence, the above addition is equivalent to:

`{1;2;3;4;5;6;7}+{0;1}`

which is:

`{1;3;#N/A;#N/A;#N/A;#N/A;#N/A}`

I’ve asked you once if you were sure that the second array here shouldn’t actually be orthogonal to the first, i.e.:

`{0,1}`

so I won’t ask you again!

Also, I’m not sure your other array constant (in the MMULT) is of the correct dimensionality either!

This:

`{-1,1}`

should be this:

`{-1;1}`

I believe.

Perhaps you can clarify?

Regards

14. Bill Szysz says:

Hi XOR,

This is my original formula

`=ORAZ(MACIERZ.ILOCZYN(KOD(FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("\$1:"&DŁ(A2)-1))+{0\1};1));{-1;1})>=0)`

As you see it is in polish. So i translate it to english (but american english) and i had to change row/column separator in constant array from ; to , (for rows) and from \ to ; (for columns). I did not know what is a row/column separator in your version of excel (regional settings).
Sorry for inconvenience.

Regards

15. Ah, I see. But, as I understand it, it’s only the semi-colons as argument separators which you need to change to commas, not the semi-colons used in array constants.

So you should have left the semi-colon in your original formula:

`=ORAZ(MACIERZ.ILOCZYN(KOD(FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("\$1:"&DŁ(A2)-1))+{0\1};1));{-1;1})>=0)`

as it is.

It seems as if the “\” symbol represents a column separator in array constants for your system version, correct?

Thanks for clarifying!

Cheers

16. Calvin says:

Almost similar to @Oscars answer. Just added the 2 criteria check and offset function.

`=IF(AND(LEN(A2)>1,CODE(A2)<91),MID(A2,ROW(OFFSET(\$H\$1,,,LEN(A2))),1)<=MID(A2,ROW(OFFSET(\$H\$1,,,LEN(A2)))+1,1),"Alphabets in Lower Case")`

Learnt something new today! Thanks for the challenge @XORLX

17. Thanks, Calvin.

I hope that my comment editor hasn’t somehow amended your formula, but can you just re-check your results for the examples I posted?

I don’t get correct results for e.g. “OOPRRQQQ” or “LLLMNL”.

Regards

18. Calvin says:

Oops! I guess my brain wasn’t working quite well!

This is the corrected version:

`=IF(AND(LEN(A2)>1,CODE(A2)<91),AND(CODE(MID(A2,ROW(OFFSET(\$H\$1,,,LEN(A2))),1))<=IFERROR(CODE(MID(A2,ROW(OFFSET(\$H\$1,,,LEN(A2)))+1,1)),"")),"Alphabets in Lower Case/Less than 2 characters")`

Thanks @XORLX

19. Much better! Nice!

Of course, I did state in the preamble that: “…where A2 is a string consisting of upper-case letters of the alphabet only and of minimum character-length 2…“, so you didn’t really need that IF clause, though it’s a nice touch in any case!

Also, I was wondering if you could somehow amend it so that the IFERROR is not required? Nothing wrong with such functions, though if we can do without then it’s generally preferable.

Thanks again, and congratulations on a correct solution!

Cheers!

20. @ikkeman

Apologies. Your post seems to have got lost in the ether! Only just seen it now.

Yes – nice amendment. I really should be using this construction more myself in place of ROW/INDIRECT. Of course, it should be pointed out that this alternative is not entirely non-volatile: constructions involving INDEX in this way, i.e. INDEX:INDEX are what is referred to as “volatile at workbook open only”.

Still, preferable to being “fully” volatile, as is INDIRECT. So thanks a lot – very nice contribution!

21. @diondan1

Likewise – apologies to you also for having somehow missed your post.

Another spot-on solution. Congratulations!

22. Nhari says:
`=AND((CODE(MID(A2,ROW(OFFSET(\$A\$2,,,LEN(A2)-1)),1))-CODE(MID(A2,ROW(OFFSET(\$A\$1,,,LEN(A2)-1)),1)))>=0)`
23. @Nhari

Hi and welcome to the site!

Another correct solution – well done!

Actually, I would just say that, if you’re using this approach then it’s not strictly necessary to use the CODE function. Simply:

`=AND(MID(A2,ROW(OFFSET(\$A\$2,,,LEN(A2)-1)),1)>=MID(A2,ROW(OFFSET(\$A\$1,,,LEN(A2)-1)),1))`

would suffice, and be a bit shorter.

Thanks a lot!

24. Nhari says:

@XOR LX

Thanks for the welcome and thanks for the suggestion.

25. MichaelCH says:
`=AND(MMULT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1))+{0,1},1)),{-1;1})>=0)`
26. @MichaelCH

Thanks a lot for joining us here and for your many excellent contributions. I understand that you don’t speak much English, and unfortunately I don’t speak any Russian, but hopefully we can translate any comments for the benefit of everyone.

By the way, some of these Formula Challenges to which you are now posting are quite old, and solutions and discussions can be found the following week. So for this challenge, for example, you may wish to read here:

in which you’ll find that my solution is almost identical to yours (although I prefer to not use the volatile INDIRECT for the ROW construction).

Many thanks and look forward to seeing more from you in future.

Regards

27. Dave Wenta says:

This is an old post. Hope it’s still OK. I had to try it.

Array entered.

`=(MATCH(1,IF(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)>MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))+1,1),1),0)=LEN(A2))`

Now I’ll look at the other posts.

28. Dave Wenta says:

I found I could shorten up my previous formula. IF was not necessary.

non-array entered.

`=MATCH(1,INDEX(--(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)>MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))+1,1)),0),0)=LEN(A2)`

This site uses Akismet to reduce spam. Learn how your comment data is processed.