Finding the Maximal String, Based on Alphabetic Order

[helivw]

Does anyone knows the formula to Find the Maximal String, Based on Alphabetic Order
I have a column with data:
AB001
AB002
EF001
EF002
EF003
KE001
KE002
KE003
AB003

I need a formule which gives me the Maximum String in the Strings starting with EF so the result of my formula must be => EF003

Thanks for helping me out. I appreciate

 

[admiral100]

Hi,

Try this .... fits the example you're asking

Ctl+Shift+Enter NOT just Enter

="EF"&TEXT(MAX((--(LEFT(A1:A9,2)="EF")*(RIGHT(A1:A9,3)))),"000")

 

[helivw]

Thanks a lot, works perfect, what

 

[helivw]

I meant to say, what a quick service

I tried to convert your formula to a a vba formula but until now I can't find it

Could you please help again.
Thanks a lot

 

[admiral100]

Sorry not good enough at VBA, maybe one of the forum members will be able to help you ...

 

[Fluff]

Try

Range("B1").Value = [="EF"&TEXT(MAX((--(LEFT(A1:A9,2)="EF")*(RIGHT(A1:A9,3)))),"000")]

 

[helivw]

This works perfect. Thank you son much. Appreciate

 

[Fluff]

Glad we could help & thanks for the feedback

 

[helivw]

Thanks again for your help in the past

Range("B1").Value = [="EF"&TEXT(MAX((--(LEFT(A1:A9,2)="EF")*(RIGHT(A1:A9,3)))),"000")]
I have an extra question
How can I replace "EF" by a variable

If I write: EF_String="EF"
and I replace it in you formula I get error 2015 as result

Range("B1").Value = [=EF_String&TEXT(MAX((--(LEFT(A1:A9,2)=EF_String)*(RIGHT(A1:A9,3)))),"000")]

Thanks