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.