TrimRight Function

[garretwalters12]

I thought TrimRight([Field1],"-") would work but no luck. Any help would be great!

Field 1	Desired Output
4645-4864	4645
13232-7897	13232
465413-6548678	465413
5613212156-56165465	5613212156
3213-56465	3213

[NicholasM · Accepted solution]

Hey @garretwalters12 ,

There may be many ways to solve this, however, the one that I came up with utilizes the Regex tool. See attached photo configuration. 

If you are new to Regex I would highly recommend checking out our tool mastery article on it here.  

[afv2688]

Hello @garretwalters12,

Use this formula:  (.+)-(.+)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[RolandSchubert]

Hi @garretwalters12 ,

you could try the formula:

Right([Field 1], Length([Field 1]) - FindString([Field 1], '-') - 1)

It should cut the string right to "-".  Hope this is helpful.

Best,

Roland   

[kelsey_kincaid]

All of the above are great solutions. For those that aren't as comfortable with RegEx or with formulas the Text to Columns tool also works nicely. If you delimit on the '-' character it will create two new columns, one with everything before the dash and one with everything after.

[DanAAC]

I know it's been a while since this post was created, but I'm facing a similar situation and would like to understand why the TRIMRIGHT doesn't work in the OP's case? 

In my case, I have company names followed by LLC and loan numbers. I am using TRIMRIGHT([LOAN NAME], "LLC") to trim everything after LLC. For some reason, it doesn't work.

Please explain.

Thank you!

[NeoInfiniTech]

Hello @DanAAC, the TrimRight function is a little different than the usual RegEx Replacement and Replace formula in that it consumes the characters you specify, starting from right to left until there are no characters left to consume. For example, when I type a TrimRight formula like this:

TrimRight([Field1],"LLC")

The results for the specified strings are:

Target String: "Test LLC" => Result String: "Test " (trailing whitespace is not removed)

Target String: "Test LLC #123456" => Result String:  "Test LLC #123456" (no effect as TrimRight formula couldn't find L or C characters at the end of the string)

Changing the "LLC" part of the formula to "LC" makes no difference as well, as it consumes all the characters you specify, in which order (LC or CL) or how many of the same character you specified is not important.

You can remove everything that comes after LLC using the formula below:

   IF   REGEX_Match([Company_Name_and_Loan_Number], "^(.+LLC)(?:.+)$")
 THEN REGEX_Replace([Company_Name_and_Loan_Number], "^(.+LLC)(?:.+)$", "$1")
 ELSE [Company_Name_and_Loan_Number]
ENDIF

How does the formula above work:

If there is any string that comes after the last instance of "LLC", it is removed. If the string doesn't end with LLC or doesn't have anything written after LLC, it is not modified. You can change the field name (and the formula if necessary) to be compatible with your data.

[DanAAC]

Thank you for your reply!

So if I understand this correctly, for TRIMRIGHT or TRIMLEFT to work, the specified term must stand alone either on its left or right side?

Example:

This works: string = "ABC 123", TRIMRIGHT([string], "123") => "ABC " OR TRIMLEFT([string], "ABC") => " 123" 

This won't work: "ABC 123", TRIMRIGHT([string], "ABC") OR TRIMLEFT([string], "123")  

[NeoInfiniTech]

You're welcome @DanAAC. Yes, all assumptions you shared regarding the examples are correct as you can see from the attached screenshot.

[DanAAC]

Thanks for confirming! Interesting, I wonder why the logic is set up this way as opposed to the TRIM function logic in Excel, for instance. Somewhat counterintuitive if you ask me.