Nested IF Statement

[Markus_Wernig]

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

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.

[Grunty · Accepted solution]

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)))
) )

[Markus_Wernig]

Grunty:

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!

[Grunty]

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

[Grunty]

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

[Markus_Wernig]

Sometimes I am wondering myself… :winking_face: