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:

(You can download the workbook here.)

Solution next week. Best of luck!

### Like this:

Like Loading...

*Related*

Hi excelxor,

Here is my answer:

Great website by the way!

@OscarNice! 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:

Thanks!

Yes, it’s an array formula.

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

Cheers

CSE formula

Array formula

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

Cheers

A slightly different solution.

Ordinary, not CSE.

Regards

Thanks, Bill!

Almost word-for-word identical to mine!

But have you double-checked this? Why are you using MMULT with {1,1}

ANDmultiplication with {-1;1}? Is this intentional? And are you sure you’ve got the displacement of your addition vector – {0;1} – here correct?Regards

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 yes {0;1} is intentionally.

Of course we can use RIGHT function also.

Regards

getting rid of the volatile indirect function

CSE

@BillNot sure I understand.

Unless your system settings are somehow different, a semi-colon in Excel represents a

rowseparator. Hence, when you perform the addition of:and:

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

sameorthogonality.Hence, the above addition is equivalent to:

which is:

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

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:

should be this:

I believe.

Perhaps you can clarify?

Regards

Hi XOR,

This is my original formula

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

Ah, I see. But, as I understand it, it’s only the semi-colons as

argument separatorswhich you need to change to commas,notthe semi-colons used in arrayconstants.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

columnseparator in array constants for your system version, correct?Thanks for clarifying!

Cheers

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

Learnt something new today! Thanks for the challenge @XORLX

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

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

This is the corrected version:

Thanks @XORLX

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!

@ikkemanApologies. 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

entirelynon-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!

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

Another spot-on solution. Congratulations!

@NhariHi 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:

would suffice, and be a bit shorter.

Thanks a lot!

@XOR LX

Thanks for the welcome and thanks for the suggestion.

@MichaelCHThanks 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:

https://excelxor.com/2015/02/18/advanced-formula-challenge-11-results-and-discussion/

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

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

Array entered.

Now I’ll look at the other posts.

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

non-array entered.