Can't debug formula, and a quick array question

[Chinchin]

Hi,

I'm getting a strange result from this formula, and when I go through the 'evaluate formula' it seems to be fine until the last step - would be grateful if someone could take a quick look:

A20 is "ksdfh 34 aserk234 der", and I'm trying to get the largest digit with this formula but it is returning 3:

Please Login or Register  to view this content.

And a quick question - alot of the ways I'm attempting to do things are coming unglued because an array within the formula is not sorted (so I can't use MATCH for example). Is there any way to force the result into ascending order please?

Thanks a lot,

Chinchin

[zbor]

What is expected result of above example?

[Chinchin]

The largest digit - 4

[zbor]

Try this, comfirmed with ctrl+shift+enter

=MAX(IFERROR(--MID(A1, ROW($A$1:$A$100), 1), 0))

Note: doesn't work for pre-2007 XL

[Chinchin]

Thanks a lot - very nice. Can you see what was not working in my formula too? I like to know so I can learn from my mistakes!

[zbor]

LOOKUP formula will return you last number, not biggest in any way... In your example it flip your word from AB32C4 into 4-C-2-3-B-A and looking for last number you get 3.

[Chinchin]

Ah, ok same problem as with match - thanks again. I guess that could be fixed up if I can force an array into order in a function - anyone know if that's easily done?