Lookup for Comma-separated values with numerical suffixes

[100]

Hi there, I was wondering if would be possible to use a cell with comma-separated values that have suffixes (i.e. iterations or repetitions) that in turn translate into the full value in the lookup table. The dataset we use is frankly ugly, but it would be nice if there was a way to make the process easier as at the moment, I have a formula that strips all the numbers from the data and then does an TEXTJOIN(IF(ISNUMBER(SEARCH())) resulting in the full values, but not the numerical suffixes.

Data	Desired Result		Value	Full Value
APP,BAN1	Apple, Banana		APP	Apple
ORA1,ORA2	Orange, Orange 2		BAN	Banana
APP,ORA1,BAN2	Apple, Orange, Banana 2		ORA	Orange

Values without a numerical suffix or a suffix of 1 have the same behavior, whilst values greater than 1 result in their suffixes being included at the end of their full value. I am unfortunately unable to use VBS in our workflow as our work is done using Office 365 on the Web.

I greatly appreciate the help!

 

[100]

How about

=LET(t,TEXTSPLIT(SUBSTITUTE(A2,"0",""),","),b,ISNUMBER(RIGHT(t)+0),TEXTJOIN(",",,VLOOKUP(LEFT(t,LEN(t)-b),$D$2:$E$5,2,0)&MID(t,LEN(t)+1-b,4)))

Thank you so much!! This is just what I wanted and works perfectly.

 

[Fluff]

Glad to help & thanks for the feedback.