Jtdylktuy

I need to find the position of the first uppercase character in an Excel string. There are lots of resources on the web that have the same (or effectively the same) solution for this:

=MIN(IF(ISERROR(FIND(CHAR(ROW(INDIRECT("65:90"))),A1)),"",FIND(CHAR(ROW(INDIRECT("65:90"))),A1)))

However, this is an unusual usage of the functions. I couldn't find an explanation of exactly what it does and how it works. When I use this formula myself, it doesn't seem to work.

Can someone explain how this formula works so I can figure out what I might be doing wrong?

Note: revision 2 includes my own answer. The original version was put on hold, and I gather the main issue was that I included the answer in the "question" part of the post. If the question is taken off hold I will make the answer an answer and delete this note.

To simplify the mechanism behind the Array formula to return the position of the 1st capital letter in string , insted of your's, I've used a simple Formula will help you to realize the method.

{=MATCH(1,(CODE(MID(B2,ROW($A$1:$A$255),1))<=90)*(CODE(MID(B2,ROW($A$1:$A$255),1))>=65),FALSE)}

How the formula works:

• Since it's an Array Formula, so it needs to be finished with Control+Shift+Enter.


• Formula assumes that the capital letter would not be more than 255 characters.


• With Match, 1 is for TRUE.


• 
  CODE(MID(B2,ROW($A$1:$A$255),1)), returns an array of ASCII characters that make up the string, returns a 255 element array.

Suppose in B2 value is TxtDate.

• Then Formula returns,{84;120;116;68;97;116;101}.

CODE(MID(B2,ROW($A$1:$A$255),1))<=90,
returns an array of TRUE and FALSE based on whether or not the ASCII code is less than or equal to 90, where 90 is the ASCII code for capital Z and will return,

{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

Here the 1st alphabet T has an ASCII code less than 90.

The 3rd part of the Formula checks for ASCII codes greater than or equal to 65, where 65 is for capital A, returns similar array which is then multiplied by the first array.

In Excel, FALSE is equivalent to zero and TRUE is to one. After Formula multiplies these set of Arrays, it gets an Array with Ones & Zeros. Which looks like this.

{1;0;0;1;0;0;0}

Shows, at position 1 and 4 are capital letters. Now the Formula uses the MATCH function to find the 1st Caps in the array and returns 1.

I do believe that above example with explanation will help you to understand that how the Formula works.