Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 20, 2021 12:34 PM
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
Apr 20, 2021 01:44 PM
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”
Apr 20, 2021 02:12 PM
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:
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"
)
)
Apr 20, 2021 02:59 PM
Thank you so much! I can’t wait to try this out!
Apr 20, 2021 02:59 PM
Thanks very much, I will try this as well!
Apr 20, 2021 03:20 PM
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.
Apr 20, 2021 03:22 PM
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!
Apr 20, 2021 04:04 PM
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.
Apr 20, 2021 05:18 PM
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.