How do I find the position of the last alphanumeric character in an Excel string using an Excel formula?

Question

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.

Answer 1

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:

1. Creates a vertical array assigning each character of the string into each row, the height of the array is automatically defined by the length of the string.

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, -, -}

2. Validates the upper case ASCII Codes of the characters in each row of the vertical array against the Lookup Vector assigning 1 if failed or 2 if passed the validation.

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}

3. Fills up the array based on prior validation assigning the row number within the array (or position) to those who passed the validation and leaving blank the records that failed.

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,,}

4. Finally retrieves the 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

Answer 2

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))

Answer 3

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.