Skip to main content
Solved

How to add two more conditions to nested IF statements

  • March 19, 2021
  • 5 replies
  • 53 views
M
Forum|alt.badge.img+14

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.

Best answer by Grunty

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

5 replies

Grunty
Forum|alt.badge.img+15
  • Grunty
  • Inspiring
  • Answer
  • March 20, 2021

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)))
) )
M
Forum|alt.badge.img+14
  • Markus_Wernig
  • Author
  • Inspiring
  • March 20, 2021

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!

Grunty
Forum|alt.badge.img+15
  • Grunty
  • Inspiring
  • March 20, 2021

So simple… Thank you!


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

Grunty
Forum|alt.badge.img+15
  • Grunty
  • Inspiring
  • March 21, 2021

So simple… Thank you!


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

M
Forum|alt.badge.img+14
  • Markus_Wernig
  • Author
  • Inspiring
  • March 21, 2021

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:

Terms & ConditionsAccessibility statement