Help

Re: Need help with formula for removing occasional phrase at the end of text

Solved
Jump to Solution
1050 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Kelly_Rogala
5 - Automation Enthusiast
5 - Automation Enthusiast

I have records that are automatically created in my base. Some of them end in the phrase “(X of Y)” to indicate they are one of many. Others do not have this tag at all.

I need help creating a formula that removes all instances of this parenthetical count. This would give me ability to group all records with identical names:

Kelly’s Record (1 of 2)
Kelly’s Recors (2 of 2)
----> all turn in to “Kelly’s Record”

However, I need the formula to leave unique records unaffected:
Kelly’s New Record
—> Kelly’s New Record

1 Solution

Accepted Solutions

You can wrap it in an IF() to only apply to titles with a (

IF(FIND(' (', Name), TRIM(LEFT(Name, FIND(' (', Name))), Name)

See Solution in Thread

8 Replies 8

Assuming that the phrase always starts with (, and no other part of the text will have parantheses, you could use this formula:

LEFT({Field name}, FIND({Field name}, ' ('))

^ Its selecting all the text to the left of the first time [space]( appears

Does not seem to work. Leaves every field blank, even though the formula doesn’t return an error

Is {Field name} a Lookup or Rollup field? If so, you can fix it like this:

LEFT({Field name}&'', FIND({Field name}&'', ' ('))

Nope - tried that and no dice. It is a text field.

Sorry, I had the Find formula backwards:

TRIM(LEFT({Field name}, FIND(' (', {Field name})))

(I have now wrapped everything in a TRIM() to remove any trailing space at the end)

Ok Awesome - this works IF there is a title that has a Parenthetical.

However, for titles that do not have a parenthetical, it returns an empty text field.

Is there a way to apply this formula only if there is a parenthetical, and otherwise simply copy over the title as is?

You can wrap it in an IF() to only apply to titles with a (

IF(FIND(' (', Name), TRIM(LEFT(Name, FIND(' (', Name))), Name)

Thank you! This worked perfectly