Sorting a column to use with an INDEX function

[Guest]

I have posted a couple of questions concerning looking up an Employee ID and
then a pay increase date, Bob Phillips supplied this formula :
=INDEX($C$1:$C$21,MATCH(MAX(IF(($A$1:$A$21="764753M")*$B$1:$B$21<E1),$B$1:$B$21)),$B$1:$B$21,0)).

My problem is with the employee ID column (A) which is a mixture of numeric
and alpha-numeric, you can get 765132 and 765132B.

My question is, how do I sort this column, should I convert the numerics to
text, or split column into two A & B with A holding the numerica nad B the
Alpha characters.

If the latter, how must the formula be changed to look for the Employee ID
split into two?

 

[Bob Phillips]

One way would be to use a couple of helper columns as you suggest. If the
format is always nnnnX, then you could use

=IF(ISNUMBER(--RIGHT(A1)),A1,--LEFT(A1,LEN(A1)-1))

and

=IF(ISNUMBER(--RIGHT(A1)),"",RIGHT(A1))

If it could be more complex, use

=LOOKUP(9.99999999999999E+307,--MID(G1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G1&"
0123456789")),ROW(INDIRECT("1:"&LEN(G1)))))

and

=SUBSTITUTE(A1,B1,"")


and then sort by these two helper columns.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)