# Formula To Truncate Text Field

• 0
• 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 example@address.com"

and leaves me with only "example@address.com"

After some trial and error,

I have written the formula

Left([Subject],20)

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!
• 300 Points

Posted 2 years ago

• 0
• 31,758 Points
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
• 75,144 Points
Matthew, actually that will not work as each character in the "Letter sent to " string will be interpreted as a delimiter.
• 31,758 Points
• 75,144 Points
Kaisa, try this

Right(Trim([Subject])," ")
• 300 Points
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?

THANKS!
• 75,144 Points
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.