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

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”

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

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

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

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

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

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

Thanks very much, I will try this as well!

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

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.

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

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

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!

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.

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.