Skip to main content
Solved

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

  • September 19, 2020
  • 8 replies
  • 62 views
K
Forum|alt.badge.img+5

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

Best answer by Kamille_Parks11

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)

8 replies

Kamille_Parks11
Forum|alt.badge.img+27

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

K
Forum|alt.badge.img+5
  • Kelly_Rogala
  • Author
  • Known Participant
  • September 21, 2020

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

J
Forum|alt.badge.img+18

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}&'', ' ('))
Kamille_Parks11
K
Forum|alt.badge.img+5
  • Kelly_Rogala
  • Author
  • Known Participant
  • September 21, 2020

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.

Kamille_Parks11
Forum|alt.badge.img+27

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)

K
Forum|alt.badge.img+5
  • Kelly_Rogala
  • Author
  • Known Participant
  • September 23, 2020

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?

Kamille_Parks11
Forum|alt.badge.img+27

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)
K
Forum|alt.badge.img+5
  • Kelly_Rogala
  • Author
  • Known Participant
  • September 23, 2020

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

Terms & ConditionsAccessibility statement