extracting the largest number in an alphanumeric string

Hi folks,

I'm trying to develop a "register" to record attendances at a Centre, as part of a membership spreadsheet.

Rather than having 365 columns (one for each day they may attend) - I've got 52 columns as one per week is much more manageable. I've got it to work out the date of the Monday of each week for the top of the columns, and am considering having the cells include data that reflects which day of that week someone attends, and what they did: such as Monday="1", Tuesday="2" etc. and "g"=group, "P" = phonecall etc.
The cells will therefore contain strings such as "1s2p" or "3c5p" or "4g" or blank.

Thanks to other threads in this forum, I've managed to get other columns to display which is the most recent week they attended, and what they did in that week. What I would really like to do is to work out the date they last attended.

I'm guessing that means extracting the largest number (of 1 to 7) from the string, and adding that to the date of the Monday of the week. However I can't find any simple way of extracting the largest number from a simple string

Am I missing something ?

PudseySquirrel

A formula like:
=MAX(IF(ISERROR(1*MID(A1,ROW(A1:A1000),1)),0,1*MID(A1,ROW(A1:A1000),1)))
entered as an array formula (confirm with ctrl+shift+enter), would return the largest (single digit) number from an alphanumeric string in A1.

Thankyou for the very prompt response, Can I just ask what the Row(A1:A1000) refers to ? - it's asking for more arguments, and I guess it's doesn't want the Python sketch.

Originally Posted by yudlugar

A formula like:
=MAX(IF(ISERROR(1*MID(A1,ROW(A1:A1000),1)),0,1*MID(A1,ROW(A1:A1000),1)))
entered as an array formula (confirm with ctrl+shift+enter), would return the largest (single digit) number from an alphanumeric string in A1.

@daffodil,

That would actually work better than my solution as an array formula as well:

=MAX(COUNTIF(A1,"*"&ROW(A1:A7)&"*")*ROW(A1:A7))

confirmed with ctrl+shift+enter.

Edit:
I'm not sure - I don't get the more arguments error when I try it.

You can use Daffodil's non-array formula or the array formula version in this post.

Thanks for the offer:

Would love to have put this in a table, but it totally threw me !

String returns:

1c2g 2
4p 4
1p3p4p6u 6
<blank> <blank>

Originally Posted by venkatpvc

can you post sample string and what you want extract from that string

Hi folks,

Well top marks for both daddylonglegs and daffodil there. Wow.

Okay a bonus point if you can also suggest a way of then extracting into another cell what the letter after the highest number is. Thereby I can make two cells display what the last date and activity they took part in was . . .

a highly impressed squirrel