REGEX_EXTRACT & SUBSTITUTE to delete string up to a point

[Markus_Wernig]

I am hoping somebody can point me in the right direction. I think REGEX_EXTRACT and SUBSTITUTE are the right way to go here.

I have titles that all have the word “BPM” followed by a space towards the end. I want to extract everything to the right of "BPM " (and the space).

Examples of the titles I am working with:

• DAM301_203 PULSE WITH SYNTH 80 BPM A FLAT
  what I’d like to extract is “A FLAT”

• DAM100_001 PULSE DEEP END 105 BPM F SHARP
  what I’d like to extract is “F SHARP”

• DAM201_400 PULSE TICKING CLOCK 125 BPM C
  what I’d like to extract is “C”

So I am thinking I need to “substitute/extract” everything up to and including "BMP ", and I should end up with what I’d like to keep, regardless of length. Is my thinking correct? If yes, how would I do that? I have not managed to come up with a regular expression that captures what I"d like to capture.

Thank you so much in advance.

[ScottWorld · Accepted solution]

I wouldn’t bother with REGEX… I would just use the RIGHT(), LEN(), and FIND() functions:

RIGHT(text, LEN(text) - FIND("BPM ",text)-3)

Note that this formula assumes that all of your records have "BPM " in them somewhere. If they don’t, you’ll want to add an IF statement to this formula:

IF(
FIND("BPM ",text) > 0,
RIGHT(text, LEN(text) - FIND("BPM ",text)-3)
)

[Markus_Wernig]

Awesome! This worked great. Thank you so much, @ScottWorld

[Markus_Wernig]

ScottWorld:

IF(
FIND("BPM ",text) > 0,
RIGHT(text, LEN(text) - FIND("BPM ",text)-3)
)

One quick question - so that I understand what you’re doing here.

The -3 at the end of your formula, what exactly is it doing?
I saw that -1 would result in “M A FLAT”, “M F SHARP”, “M C”.
Using - 2 would leave the space in.

Where does the counting for -3 start? And I am sorry, if this is more than obvious to most people here. I am just trying to learn the basics.

Thank you. I appreciate it.

[ScottWorld]

• The RIGHT() function returns a certain number of characters from the righthand side of a string of text.
• The FIND() function gives you the starting position of a string of text within another string of text.
• The LEN() function returns the length of your string.

So using the RIGHT() function to return “the length of the entire text” minus “the starting position of the string you’re looking for (which is 4 characters long)” returns a portion of the righthand side of your entire text, but it also includes part of the string you’re looking for. Subtracting another 3 characters removes that part of the string.

You can learn more about these functions here:

Airtable Support

Formula field reference

Formulas may involve functions, numeric operations, logical operations, and text operations that operate on fields. For an overview of formula fields, please refer to the Guide to Formula, Lookup, ...

[Markus_Wernig]

Thank you! I appreciate it.