Discussions

Expand all | Collapse all

Formula To Truncate Text Field

  • 1.  Formula To Truncate Text Field

    Posted 04-24-2017 21:45
    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!


  • 2.  RE: Formula To Truncate Text Field

    Posted 04-24-2017 21:51
    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


  • 3.  RE: Formula To Truncate Text Field

    Posted 04-24-2017 22:00
    Matthew, actually that will not work as each character in the "Letter sent to " string will be interpreted as a delimiter.


  • 4.  RE: Formula To Truncate Text Field

    Posted 04-25-2017 03:30
    My Bad


  • 5.  RE: Formula To Truncate Text Field

    Posted 04-24-2017 22:02
    Kaisa, try this

    Right(Trim([Subject])," ")


  • 6.  RE: Formula To Truncate Text Field

    Posted 04-24-2017 22:09
    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!


  • 7.  RE: Formula To Truncate Text Field

    Posted 04-24-2017 22:13
    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