Formula Help! How do I edit some numbers and parentheses out of text?

[Dennis_Petrou]

Hello, I’m kinda new to this and hopefully someone can help me figure out a formula to help me with this. My data in one column consists of a number, a name, and some extra information in parentheses. I would like to extract just the name into a new column. Here are some examples:

1234 This Is What I Want To Keep (not this)
555 Keep This Text (get rid of this and the number)

Is this possible?

Thanks so much

[Kamille_Parks]

Assuming your data is always [numbers] [space] [name] [stuff in parenthesis], create a formula field with this formula:

TRIM(
   MID(
      {field name}, 
      FIND(" ", {field name}), 
      MAX(FIND("(", {field name}) - FIND(" ", {field name}), LEN({field name}))
   )
)

^ In plain English, that formula says “Pick out the text inbetween the first space and the first parenthesis, then remove any whitepsace at the front and end”

[kuovonne]

Another option is to use regular expressions.

REGEX_REPLACE({field name}, "(\\d* )([\\w ]+)( \\(.*\\))", "$2")

In English, the formula says to look for three groups of text in a row:

1. a series of digits followed by a space
2. a series of “word” characters and spaces
3. a space followed by an opening parenthesis, followed by any text, followed by a closing parenthesis.

Then, replace all of that text with just the second group of text.

You can expand this formula to add some error checking to display the result only if there actually is text, and to display an error if the text doesn’t match the pattern.

IF(
  {field name},
  IF(
    REGEX_MATCH({field name}, "^(\\d* )([\\w ]+)( \\(.*\\))$"),
    REGEX_REPLACE({field name}, "^(\\d* )([\\w ]+)( \\(.*\\))$", "$2"),
    "⚠️Text does not follow pattern"
  )
) 

[Dennis_Petrou]

Thank you so much! I can’t wait to try this out!

[Dennis_Petrou]

Thanks very much, I will try this as well!

[Dennis_Petrou]

Hello again. So its very close to working perfectly. There are a couple of issues I am having…

First of all, I forgot that sometimes there will be additional numbers after the text… and also its not removing the stuff in parentheses. Here’s is an example of what I have and what the formula is coming out with:

1234 Text I Want To Keep 5 (remove all this as well)

and what I end up with using your formula is

Text I Want To Keep 5 (remove all this as well)

So i want to remove all the numbers, both before the text (which is always 3 or 4 digits then a space) and the number after the text if there is one (its always a space after the text, then 1 or two digits, then a space then possibly parentheses but not always)

Sorry for the confusion, thanks so much for your help on this. I am trying to learn formulas but am getting very confused.

[Dennis_Petrou]

Hello, how exactly do I get your regular expressions to work? I copy and pasted into the formula but don’t know how to configure it properly to choose the correct field. Thanks!

[kuovonne]

To get my formulas with regular expressions to work, you need to replace {field name} with your field name. In my original version of the single line formula, this was Notes (because that was the field name when I tested the formula), but I have since changed it to {field name} to make it easier to understand. If you use the multi-line formula, you will need to change {field name} in three places.

Also, in @Kamille_Parks’s formula, try changing MAX to MIN.

However, the fact that you also want to get rid of numbers after the text makes me recommend a solution with regular expressions instead of the MID formula.

I am away from my computer, so I cannot build the more complex regular expression that you would need to take into account all of the variations in your pattern now.

[Kamille_Parks]

TRIM(
   MID(
      {field name}, 
      FIND(" ", {field name}), 
      IF(FIND("(", {field name}), FIND("(", {field name}) - FIND(" ", {field name}), LEN({field name}))
   )
)

For other’s reference, ^ the above will more consistently remove the parenthetical, but won’t solve removing numbers just before the parenthetical.

Use Kuovonne’s REGEX approach. I’m not as familiar with regular expressions.