Formula To Truncate Text Field

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
I need to write a formula for a text field titled "subject" that consistently removes the text

"Confirmation Copy of Welcome Letter sent to" from the subject heading "Confirmation Copy of Welcome Letter sent to"

and leaves me with only ""

After some trial and error,

I have written the formula 


and this will show me the first 20 characters.

Alternatively, Right([Subject],20) displays the last 20.

These steps are not ideal because I need to display the email address, which is has a varied character length.

I have attempted "Part" but I am likely missing a step with the delimiters.  Could somebody please help me with the formula to just remove the text detailed above?

Thank you!
Photo of Kaisa Janzen

Kaisa Janzen

  • 300 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,758 Points 20k badge 2x thumb
Right Function:
Right (Text t, Text d)

Description: Returns the right part of a text value starting at the character after the last occurrence of a delimiter character.  The first argument, t, is the value to be searched. The second argument, d, is a text value containing all the possible delimiter characters.

Example: Right("abc/def",";/,") returns "def"
Right("Michael Wissner", " ") returns "Wissner"

Something like this:

Right([Subject], "Letter sent to ")

That should catch the entire email
Matthew, actually that will not work as each character in the "Letter sent to " string will be interpreted as a delimiter.
Kaisa, try this

Right(Trim([Subject])," ")
Photo of Kaisa Janzen

Kaisa Janzen

  • 300 Points 250 badge 2x thumb
Right(Trim([Subject])," ") works beautifully!  May I ask how this formula tells QB to trim at the termination of "Confirmation Copy of Welcome Letter sent to" so I can use it again for other formula fields?

Sure, the Trim was just insurance to insure that we were trimming off any trailing spaces.

It will have probably worked just as well like this.

Right([Subject]," ")

That says

Start from the right side of the string and keep going to the left until you hit any of the characters in the list of delimiters in the quotes.  Since the only delimiter in the quotes was a space, it will stop at the first space.

Here is a link to all the formula functions.