Reverse Find / Max Find Substring in String

I'm cleaning some Project strings in Prep.

They might have names like

"CHECKERS ID 123"

"CHESS ID 123456789"

I want to clean to read simply "CHECKERS" or "CHESS"-- this is fairly easy by finding "ID", removing anything after it, and trimming spaces. That works for 95% of our entries.

NOTE: The length of the ID number value varies, so I can't just say "take all but the final 7 characters"

But then we run into:

TIDDLY-WINKS ID 55555"

Which our cleaning step translates into simple "T" 😮 This is because it bumps into an unexpected "ID" in the middle of the string, not towards the end.

How can I best generate the "maximum occurrence" of a substring in a string?

Is there a way to make the FIND function work from back-to-front?

Could I quickly reverse the primary string?

Should I just link together several FINDNTH calcs and use the results of those?

Thoughts?

Thanks all for your help!

Michael Hesser (Tableau Forum Ambassador)

If this response has answered your question, kindly click "Best Answer"