Skip to main content

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.

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

) )

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!


So simple… Thank you!


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


So simple… Thank you!


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

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


Sometimes I am wondering myself… :winking_face:

Reply


Terms & Conditions