Hi folks, trying to get the maximum value of a set of values, some may contain letters and can vary from one character to ten. MAX function works for numeric values, but I cannot figure out how to make something work with alphanumeric. Any help would be appreciated, thanks! I have attached an example.
Try this:
=MAX(--RIGHT(A3:E3,1))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks AliGW, your answer is close, but I forgot to explain it has to return the exact value, not just the number because it is also then used in a vlookup formula to find another value in a spreadsheet. Maybe somehow I can make this work if I don't get an exact answer. Again, thanks!
Last edited by AliGW; 07-01-2020 at 12:23 PM. Reason: Please don't quote unnecessarily!
Perhaps this?
=INDEX($A$3:$E$3,MATCH("*"&MAX(--RIGHT(A3:E3,1)),$A$3:$E$3,0))
Success! Thanks, although I only tried one line, but it does work!
It will work as long as there is only one number at the end that needs taking into account ... Fingers crossed!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
I see, now that you said, "as long as there is only one number at the end", which is a problem. These alphanumeric values can range from A1 to who knows as these are from various customers. Back to the drawing board as they say. Let me revise my example to reflect all the possibilities that could arise.
Last edited by thecdnmole; 07-01-2020 at 01:09 PM.
What's the ultimate purpose? Is there anything else associated with these entries (e.g. a date) that could be used to help identify the maximum?
Are you still using Office 2010?
That's basically it, although this MAX value is part of a complex variations of formulas for a chart, but way to hard to explain all of that. Why the variation in numbers as they are provided by customers, therefore I must work with that. It would be simpler to drop the alpa characters, but that could also vary from one to who knows. No, these values are provided and used to lookup other values on a spreadsheet using vlookup. Actually, I just upgraded to Office365, but customers using this may have older versions of Excel.
I am sure there is a better way to do what you need, but we'd need to see the data in more context in order to advise.
Perhaps you can mock something up?
based on your examples:
the above would return 00011 ahead of 0005 -- whereas in literal string sense the 0005 would be the 'greater' of the two valuesFormula:
Please Login or Register to view this content.
if the 00011 was a typo, and that numbers would follow same format {i.e. 0011}, then you might find something like below would work:
both assume, per sample, that the alpha prefixes are consistent on the row - where present -- if not the COUNTIF will return highest string (so ZZ0001 would > AA0099), whilst the Array will fail altogether.Formula:Please Login or Register to view this content.
Thanks Xlent, I tried your second formula and that seems to work the best. I did have to tweak another formula so adjust and it does seem to work for the amount of testing I have been able to do. Sorry AliGW, what I provided in the second example would be about all I could do, as the worksheet this is used on probably would not work standalone, and if it did, you might not be able to figure out easily what it is I am trying to do, although maybe you'd come up with a much simpler/better way! Thanks again to both of you for your help and I will now mark this as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks