Help

REGEX_EXTRACT & SUBSTITUTE to delete string up to a point

Topic Labels: Formulas
Solved
Jump to Solution
3047 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Markus_Wernig
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

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

See Solution in Thread

5 Replies 5
ScottWorld
18 - Pluto
18 - Pluto

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

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

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.

• 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:

Thank you! I appreciate it.