How to return all items from a multiple select that begin with a certain set of 2 letters?

[Franz_Palomares]

Hmm, I feel like this should be easier, but I'm running into a stumbling block.

I have a Multiple Select column {Dubbing VO (all languages)} that has a list of VO actors. Each name begins with the two letter code for what language they are working in. i.e. ES for Spanish, HI for Hindi etc. There are multiple actors for each language.

Then I have another column that is the VO artist per language. i.e. {ES VO actor}. I'm currently using the formula below to pull in the selected name that begins with that language's code. The problem I'm running into is when there is more than 1 ES actor on a project. It only returns the first name it comes across. The code I'm using as an alternative returns all of the names in the {Dubbing VO (all languages)} field.

Code that works for 1 name:

IF(

FIND('ES', ARRAYJOIN({Dubbing VO (all languages)}&',')) > 0,

TRIM(LEFT(

RIGHT(

ARRAYJOIN({Dubbing VO (all languages)}&','),

LEN(ARRAYJOIN({Dubbing VO (all languages)}&','))

- FIND('ES', ARRAYJOIN({Dubbing VO (all languages)}&','))

+ 1

),

FIND(

',',

RIGHT(

ARRAYJOIN({Dubbing VO (all languages)}&','),

LEN(ARRAYJOIN({Dubbing VO (all languages)}&','))

- FIND('ES', ARRAYJOIN({Dubbing VO (all languages)}&','))

)

)

)),

""

)

Thanks in advance,

Franz

[JonathanB · Accepted solution]

I think I would set this up differently, with one table for actors and another for languages.  Use the languages table instead of your multi-select option to link actors to languages.  In the languages table you'll then be able to look up which actors use that language.  If you just want to display the first two letters for the VO code in the actors table, use LEFT({VO langauge}, 2).  Assuming the language code is always two letters long, this will give you what you want I think?