Forum Discussion

KaisaJanzen's avatar
KaisaJanzen
Qrew Cadet
9 years ago

Formula To Truncate Text Field

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!

6 Replies

Sort By
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    9 years ago
    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
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    Matthew, actually that will not work as each character in the "Letter sent to " string will be interpreted as a delimiter.
  • KaisaJanzen's avatar
    KaisaJanzen
    Qrew Cadet
    9 years ago
    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!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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.

    https://login.quickbase.com/db/6ewwzuuj?a=q&qid=1000080