Help

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

Solved
Jump to Solution
513 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Franz_Palomares
5 - Automation Enthusiast
5 - Automation Enthusiast

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
1 Solution

Accepted Solutions
JonathanB
8 - Airtable Astronomer
8 - Airtable Astronomer

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?

See Solution in Thread

1 Reply 1
JonathanB
8 - Airtable Astronomer
8 - Airtable Astronomer

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?