search for multiple text strings (this or that) simultaneously in a single cell

Hello,

This is quite complicated to explain so bare with me. I am dealing with the following strings in a column, I have thousands of rows so can't do this by hand

L Z9
LAQ18
BAZ8
XARU8
CLQ8
G U8
HCN8
IRM9
MESU8
MFSU8
NGQ18
etc...


These unique codes follow a pretty random construction for the root (first few letters) but what is consistent with all of them is that the last 2 letters and sometimes 3 letters are the date, I'll explain further with the following.


The last 2 or 3 letters always follow this legend where it is indicating a month and date

F = Jan, G = Feb, H = Mar, J = Apr, K = May, M = Jun, N = Jul, Q = Aug, U = Sep, V = Oct, X = Nov, Z = Dec
&
8 = 2018, 9 = 2019, etc however some cases they actually put 2 numbers like 18 = 2018, 19 = 2019

Example:MES being the root, would always be MES but can show up as MESU8 = MES Sept 2018 , MESQ8 = MES Aug 2018
Or G U8 can be G K8 = G May 2018, G V9 = Oct 2019

I am trying to get Excel to dynamically return me the position in the String where the custom code for the date starts, so I can Parce out just the Root. Keep in mind sometimes the last 2 letters are the date code, and sometimes its the last 3 letters. Can I get a formula that automatically knows?

If you have any ideas I would greatly appreciate it, I know this was complicated and all but I hope I was clear in my objective

Try this user defined function

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a suitable cell, enter =GetRoot(A1)

Remember to save the workbook as a macro enabled workbook .xlsm

Too Late

Fluffs solution looks better.

Please Login or Register  to view this content.