Match string and find the maximum value

[tuankluu]

Hi

Please help, I'm looking for a formula that can return the max values contain RTS ranges from B1:D1. The result in cell A1 should show RTS-2123 as the max value.
----A-----B-----------C------------D
1---???---RTS-1012--- RTS-902--- RTS-2123

Thanks

[Ace_XL]

Try..these array formulae. Confirm with Ctrl+Shift+Enter and not just Enter

="RTS-"&MAX(SUBSTITUTE(B1:D1,"RTS-","")*1)

or

="RTS-"&MAX(RIGHT(B1:D1,LEN(B1:D1)-4)*1)

[Marcol]

Try this array formula, confirm with Ctrl+Shift+Enter not just enter

Please Login or Register  to view this content.

[Marcol]

Possibly better to use either of these non-array formulae

Please Login or Register  to view this content.

Or maybe this to allow for any prefix

Please Login or Register  to view this content.

[tuankluu]

It work Thank you so much

[tuankluu]

Sorry but need your help again and I hope you can solve this problems. Need to return Maximum value of RTS. The result should show in A1 is RTS-2123 12-Feb-2012

------A------ B--------------------------C-------------------------D---------------------
1-----???---- RTS-1012 10-June-2011 ----RTS-902 12-Oct-2012-----RTS-2123 12-Feb-2012

[Ace_XL]

Tweaking Marcol's formual above..

=INDEX(B2:D2,0,MATCH("*"&MAX(INDEX(MID(B2:D2,FIND("-",B2:D2)+1,FIND(" ",B2:D2)-4)*1,1,))&"*",B2:D2,0))

[tuankluu]

Thanks you

[Cutter]

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[tuankluu]

I've another problem, I can't seem work this out. I hope you can help me.
See the excel attached. Many Thanks Book1.xlsx

[CheshireCat]

Spaces and errors just complicate things. In this case, you're back to an array formula. Try this:

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just enter.