Forum Discussion

willwonka's avatar
willwonka
Tin Contributor
Jul 04, 2026

>">" Logic Question

I love looking at the Excel BI Challenges on Linked in.  I was going back over challenge 7 and saw a new answer with some new logic that I don't quite understand.

Here is the challange

Provide a formula to count the number of English alphabets and numeric digits in every cell. Expected answers given against all cells

StringExpected Answers
V%ij5yVer_m@y10
&Qw00Ty]6
 0
M%a^r#sh!ALL8
12344
EXCEL5
$9%6#-2+3

 

https://www.linkedin.com/posts/excelbi_excel-advancedexcel-excelchallenge-share-6969180817432424448-EKbX/?utm_source=share&utm_medium=member_desktop

 

One new since the REGEX functions came out was the true "best" answer.... =LEN(REGEXREPLACE(A6:A12,"[\W_]",""))

But another user this formula:

=REDUCE(0,SEQUENCE(MAX(LEN(A6:A12))),LAMBDA(a,n,a+(MID(A6:A12,n,1)>">")))

I understand it for the most part; but can't quite figure why they used the greater than sign to their true answers.  Why the ">"?

I started looking at character codes to see if that was it:

8TRUE=N87>">"
9TRUE=N88>">"
:FALSE=N89>">"
;FALSE=N90>">"
<FALSE=N91>">"
=FALSE=N92>">"
>FALSE=N93>">"
?FALSE=N94>">"
@FALSE=N95>">"
ATRUE=N96>">"
BTRUE=N97>">"
CTRUE=N98>">"

But that must not be it as numbers come before ">".  I thought maybe it was a number within a text that made it true; but then i looked at other characters that come further down the character code index (see below).  There are some trues mixed in there.  I tried adding those characters into the question and it didn't change anything.

 

yTRUE=N152>">"
zTRUE=N153>">"
{FALSE=N154>">"
|FALSE=N155>">"
}FALSE=N156>">"
~FALSE=N157>">"
FALSE=N158>">"
€FALSE=N159>">"
TRUE=N160>">"
‚FALSE=N161>">"
ƒTRUE=N162>">"
„FALSE=N163>">"
…TRUE=N164>">"
†TRUE=N165>">"
‡TRUE=N166>">"
ˆFALSE=N167>">"
‰TRUE=N168>">"
ŠTRUE=N169>">"

 

Thoughts?

 

excel
Formulas and Functions

5 Replies

  • djclements's avatar
    djclements
    Silver Contributor
    Jul 04, 2026

    When comparing text values with greater than or less than operators, the result is based on the sort order. To illustrate, place the following formula in cell A1:

    =SORT(CHAR(SEQUENCE(93,,33)))

    Then, in cell B1:

    =A1#>">"

    You'll notice ">" is the last symbol in the array that comes before any of the numbers and letters, which is why everything that comes after returns TRUE.

    • willwonka's avatar
      willwonka
      Tin Contributor
      Jul 05, 2026

      Thanks.  It was weird after I did sort it; but it now makes sense.  Thanks again.

  • IlirU's avatar
    IlirU
    Iron Contributor
    Jul 04, 2026

    Hi willwonka​,

    Below formula is used to separate each character of cell A2 (I have applied it in cell C2).

    =MID(A2, SEQUENCE(MAX(LEN(A2))), 1)

    To find TRUE or FALSE and to distinguish of English alphabets and numeric digits use below formula (I have applied it in cell D2).

    =C2# > ">"

    So now, due to the use of the ">" symbol, we have the results TRUE or FALSE.

    In Excel, when you use the ">" operator to compare two values, it does not check whether they are letters or numbers, but makes a comparison according to the order of the characters in the ASCII (or Unicode) code.

    Letters and numbers have ASCII codes ordered sequentially. For example:

    "A" has the code 65

    "B" has the code 66

    So, when Excel compares "B" > "A", it returns TRUE because 66 is greater than 65.

    In this case, ">" has the code 62. So if any given text has a code greater than 62 then this is counted as TRUE, otherwise FALSE.

    Unicode collation depends on the lexicographical order set by Windows based on the selected locale / language. If Windows is in English then the Unicode Collation Algorithm (UCA) is used and in this case @ is positioned before the letters of the English alphabet and numbers as well.

    So basically the process has to do with the Unicode ordering of different characters.

    Have a nice day.

    IlirU