3

I am looking for a formula or script that can show me the highest number within a range of values that have a leading alpha character. For example:

D101
D102
D103
D104
J101
J102
J103

I would like a cell that shows the highest D# in column is: D104 & highest J# in col is J103.

I tried using RIGHT, in combination with MAX but was unsuccessful. This is the formula I tried which evaluates to 0:

=MAXA(B:B,RIGHT(B:B,3))

migrated from superuser.com Dec 20 '17 at 19:46

This question came from our site for computer enthusiasts and power users.

2

Please try:

=ArrayFormula(max(value(right(filter(A:A,Left(A:A)="D"),3))))

and adjust D to the letter of your choice (or parameterise it).

MAX
VALUE
RIGHT
FILTER
LEFT

Expect 0 from attempts at maths operations on string functions since these always return Text.

  • 1
    this worked great- thanks alot for your expertise! – OblongMedulla Dec 20 '17 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.