Help

Re: How to add two more conditions to nested IF statements

Solved
Jump to Solution
411 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Markus_Wernig
8 - Airtable Astronomer
8 - Airtable Astronomer

I am trying to add conditions to my nested IF statement, but somehow it’s not working.
I want to extract musical key information from the end of file names. The information I am trying to extract is marked bold.

BRASS CHORDS DARK G
BRASS CHORDS DARK D1 FLAT
BRASS CHORDS DARK C SHARP
BRASS CHORDS FORCEFUL A FLAT
BRASS CHORDS FORCEFUL B

Screen Shot 2021-03-19 at 11.51.13 PM

The extraction should only happen if the file name belongs to the category “Musical Sound Design”, but I can’t figure out how to get that condition into my formula.

I am using this formula:

TRIM(
IF(REGEX_MATCH(NAME,'SHARP'),RIGHT(NAME,8),
IF(REGEX_MATCH(NAME,'FLAT'),RIGHT(NAME,7),
RIGHT(NAME,2)))
)

Am I trying to get too much into this formula? The musical key information can either be 1 or 2 digits (G or G1), or 7-8 digits (C SHARP or C1 SHARP), or 6-7 digits (B FLAT or B1 FLAT).
The CATALOG field determines tells us if a filename contains musical key information or not, but I cannot figure out how to work that condition into the formula.

Thank you in advance for any hint.

1 Solution

Accepted Solutions
Grunty
7 - App Architect
7 - App Architect

Just precede the formula with the Catalog condition:

IF(CATALOG='Musical Sound Design',
TRIM(
IF(REGEX_MATCH(NAME,'SHARP'),RIGHT(NAME,8),
IF(REGEX_MATCH(NAME,'FLAT'),RIGHT(NAME,7),
RIGHT(NAME,2)))
) )

See Solution in Thread

5 Replies 5
Grunty
7 - App Architect
7 - App Architect

Just precede the formula with the Catalog condition:

IF(CATALOG='Musical Sound Design',
TRIM(
IF(REGEX_MATCH(NAME,'SHARP'),RIGHT(NAME,8),
IF(REGEX_MATCH(NAME,'FLAT'),RIGHT(NAME,7),
RIGHT(NAME,2)))
) )

So simple… Thank you!

You’re welcome! Please mark the topic as solved (if it actually worked, that is :winking_face: )

Markus, thanks for the intent, but you have to mark my post as a solution, not yours :laughing:

Sometimes I am wondering myself… :winking_face: