For example, in the string VA4940--05-LAMB --,%2--, 2 is the last alphanumeric character. It is in the 22nd spot. I'm looking for a formula that will return "22".
Thanks to everyone who responded. This gave me the answer I'm looking for, specifically tiger's. All were helpful, though.
Alternate regular formula (no array entry required) solution:
=LOOKUP(2,1/(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz1234567890"))),ROW(INDIRECT("1:"&LEN(A1))))
Also, given your provided sample string VA4940--05-LAMB --,%2-- the last alphanumeric character (2) is at position 21, not 22.
Same formula, but without using INDIRECT (it's a volatile function and should generally be avoided). This formula also assumes the maximum length of any given string is 99 or less. Adjust the 99's to be higher if necessary:
=LOOKUP(2,1/(ISNUMBER(SEARCH(MID(A1&REPT(" ",99),ROW($1:$99),1),"abcdefghijklmnopqrstuvwxyz1234567890"))),ROW($1:$99))
If you don't want a dedicated VBA function, here's one way. Start with:
=ROW(OFFSET(F1,0,0,LEN(MyStr),1))
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23}
entered as an array formula (CTRL-SHIFT-ENTER --- all of the formulas in this answer will be array formulas). As shown, this will generate a series of integers 1, 2, ... LEN(MyStr), where MyStr is your target string and F1 can be any cell in row 1.
Embed that in the MID function to produce an array of single chars in MyStr:
=MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1)
{"V";"A";"4";"9";"4";"0";"-";"-";"0";"5";"-";"L";"A";"M";"B";" ";"-";"-";",";"%";"2";"-";"-"}
The UPPER function is used as a convenience so that there's no need to test for lower case alpha-chars.
Embed that in the CODE function to produce an array of ASCII codes:
=CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))
{86;65;52;57;52;48;45;45;48;53;45;76;65;77;66;32;45;45;44;37;50;45;45}
Now determine which ones are (upper case) letters. An IF function test those codes against the condition >=CODE("A"). If that test passes, we'll perform another test against the condition <=CODE("Z"). If both tests pass, we know it's a letter, so return the character position. Otherwise return a zero.
=IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))>=CODE("A"),IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))<=CODE("Z"),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),0),0)
{1;2;0;0;0;0;0;0;0;0;0;12;13;14;15;0;0;0;0;0;0;0;0}
We've only tested for alpha chars so far. Now instead of returning 0 if the first test failed, substitute an analogous test for digits:
=IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))>=CODE("A"),IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))<=CODE("Z"),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),0),IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))>=CODE("0"),IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))<=CODE("9"),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),0),0))
{1;2;3;4;5;6;0;0;9;10;0;12;13;14;15;0;0;0;0;0;21;0;0}
Finally, to get the last matching char take the MAX of the entire thing:
=MAX(IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))>=CODE("A"),IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))<=CODE("Z"),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),0),IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))>=CODE("0"),IF(CODE(MID(UPPER(A1),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),1))<=CODE("9"),ROW(OFFSET(F1,0,0,LEN(MyStr),1)),0),0)))
which returns 21 (not 22 --- 22 is wrong).
A VBA custom function is surely more straightforward.
To find the position of the First or Last Character\Type in a string use the following Array Formula:
= [First or Last] (
CHOOSE( LOOKUP( CODE( UPPER(
MID( Cll , ROW( $A$1 : INDEX( $A:$A , LEN( Cll ) , 0 )), 1))),
[Lookup Array] ), "" ,
ROW( $A$1 : INDEX( $A:$A , LEN( Cll )))))
Where:
[First or Last]: Use MIN for First Character or MAX for Last Character
Cll: Cell containing the string to search
[Lookup Array]: Array to use for validation based on Type of Character to find
Type of Character - Classification (see Fig. 1)
Numeric: 0 to 9
Alpha: A to Z (lower & upper case)
Alpha International: Š Ž Ÿ À Á Â Ã Ä Å Ç È É Ê Ë Ì Í Î Ï Ñ Ò Ó Ô Õ Ö Ù Ú Û Ü Ý (lower & upper case)
Other Characters: Any other ASCII character
Fig. 1
Lookup Array to use for each Type of Character * - Short Array to be used with UPPER function (see Fig. 2)
Numeric: {1,1;48,2;58,1}
Alpha: {1,1;65,2;91,1}
Alpha International: {1,1;138,2;139,1;142,2;143,1;159,2;160,1;192,2;
198,1;199,2;208,1;209,2;215,1;217,2;222,1}
Other Characters: {1,2;48,1;58,2;65,1;91,2;97,1;123,2;131,1;132,2;138,1;
139,2;142,1;143,2;154,1;155,2;156,1;157,2;158,1;160,2;192,1;
198,2;199,1;208,2;209,1;215,2;217,1;222,2;224,1;247,2;248,1}
Fig. 2
These arrays can be combined as needed, for example in this case the question asks to find the Last Alphanumeric character in the string, for that we'll use the Numeric and Alpha arrays combined.
String: VA4940--05-LAMB --,%2--
For this sample this string is located in cell C21
Let’s replace the arguments of the Array Formula:
[First or Last] => MAX
Cll => C21
[Lookup Array] => '{1,1;48,2;58,1;65,2;91,1} - Alphanumeric
Enter the following Array Formula in cell G24:
=MAX(CHOOSE(LOOKUP(
CODE(UPPER(MID($C21,ROW($A$1:INDEX($A:$A,LEN($C21),0)),1))),
{1,1;48,2;58,1;65,2;91,1}),"",
ROW($A$1:INDEX($A:$A,LEN($C21)))))
Basically what the formulas does is:
MID($C21,ROW($A$1:INDEX($A:$A,LEN($C21),0)),1))
Partial result: {V, A, 4, 9, 4, 0, -, -, 0, 5, -, L, A, M, B, , -, -, ,, %, 2, -, -}
LOOKUP(CODE(UPPER(
{V, A, 4, 9, 4, 0, -, -, 0, 5, -, L, A, M, B, , -, -, ,, %, 2, -, -}),
{1,1;48,2;58,1;65,2;91,1})
Partial result: {2,2,2,2,2,2,1,1,2,2,1,2,2,2,2,1,1,1,1,1,2,1,1}
CHOOSE({2,2,2,2,2,2,1,1,2,2,1,2,2,2,2,1,1,1,1,1,2,1,1},"",
ROW($A$1:INDEX($A:$A,LEN($C21))))
Partial result: {1,2,3,4,5,6,,,9,10,,12,13,14,15,,,,,,21,,}
MAX (as we’re looking for the last character) value in the array, representing the last valid row in the array complying with the Lookup Array condition.MAX({1,2,3,4,5,6,,,9,10,,12,13,14,15,,,,,,21,,})
Position 21
See Fig. 3 with additional samples.
Fig. 3
