Skip to main content
Solved

REGEX_EXTRACT & SUBSTITUTE to delete string up to a point

M

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.

Best answer by ScottWorld

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)

)
View original
Did this topic help you find an answer to your question?

5 replies

ScottWorld
Forum|alt.badge.img+20
  • ScottWorld
  • Brainy
  • 8705 replies
  • Answer
  • March 9, 2021

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)

)
Scott Rose, Expert Airtable Consultant at ScottWorld.com
M
ScottWorld wrote:

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

ScottWorld
1 person likes this
  • ScottWorld
    ScottWorld
M
ScottWorld wrote:

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)

)


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
Forum|alt.badge.img+20
  • ScottWorld
  • Brainy
  • 8705 replies
  • March 9, 2021
Markus_Wernig wrote:

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:


Scott Rose, Expert Airtable Consultant at ScottWorld.com
M
ScottWorld wrote:

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

Reply


Most helpful members this week

Terms & Conditions