Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

TrimRight Function

garretwalters12
8 - Asteroid

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

 

 

Field 1Desired Output
4645-48644645
13232-789713232

465413-6548678

465413
5613212156-561654655613212156
3213-564653213
9 REPLIES 9
NicholasM
Alteryx Alumni (Retired)

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.  

 

Capture.PNG

afv2688
16 - Nebula
16 - Nebula

Hello @garretwalters12,

 

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

 

Untitled.png

 

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

Regards

RolandSchubert
16 - Nebula
16 - Nebula

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
12 - Quasar

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.

 

kayers_0-1580328366219.png

DanAAC
5 - Atom

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
8 - Asteroid

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
5 - Atom

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
8 - Asteroid

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

 

 

DanAAC
5 - Atom

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.

Labels