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

Trim right hand portion of text

jb
7 - Meteor

Hi,

 

I've looked at the different trim variations, but I need one that will cut off a certain amount of characters from the right hand of a field.

 

My values look like Blue4509, Blue2034, Yellow2345, Orange2341

I'd like to cut off the last 4 values so that I get Blue, Yellow, Orange

 

Thanks!

9 REPLIES 9
RodL
Alteryx Alumni (Retired)

You could use Left(Data, (Length(Data) - 4))

tom_montpool
12 - Quasar

Using Left([Data],Length([Data])-4) will work well if all your numbers are four digits.

A more forgiving approach would be to use a regular expression either in the RegEx tool or using Regex_Replace in the Formula tool.

In the Formula tool, your expression could be RegexReplace([Data],"(\l+)\d*","$1")

simpleminded
7 - Meteor

Hi Tom_montpool,

 

I am new to Regular Expressions. Can you explain what does the expression do? The one that you have written. Thanks.

tom_montpool
12 - Quasar

Hi,

 

The \l is looking for lowercase letters

The + specifies that the lowercase letter(s) must appear at least once

The \d is looking for digits

The * specifies that the digits may or may not appear (or is there "zero or more times").

The brackets around the \l+ "mark" that part of the pattern as special

 

So if the input is "Blue450"

 

REGEX_REPLACE([Input],"(\l+)\d*","$1") will return "Blue".

 

If you changed the expression to "mark" the digits portion, you could separate the two marked portions

 

REGEX_REPLACE([Input],"(\l+)(\d*)","$1 light has a wavelength of at least $2 nanometers") which will return the phrase:

 

"Blue light has a wavelength of at least 450 nanometers"

 

It is important to note that this function is case-insensitive by default so a \u (uppercase) would give the same result. If you want the expression to be case sensitive, add a ",0" to the end of the expression.

 

Also, if you use the REGEX tool (from the Parse toolbox) you can access a list of commonly used Regex 'tags'...and don't forget to access the Alteryx help with F1.

 

I hope this helps...if not, at least get a chuckle out of this:

 

http://xkcd.com/208/

 

 

 

 

mweiser_dup_512
6 - Meteoroid

How would I replace a text such as "Vacation 2/2 - 2/8 / Training" to "Vacation 2-2 to 2-8 / Training" (I still want the last slash to remain)?

 

Thank you

tom_montpool
12 - Quasar

There are probably a bunch of ways, there usually are in Alteryx.

 

To do it all in one replace would be pretty messy so I'd suggest doing it in steps.

 

By default, regular expressions are "greedy", meaning they match as much as they can, so if you used:

 

Regex_Replace([Example],"(.*)/(.*)","$1"+'~'+"$2") you would get "Vacation 2/2 - 2/8 ~ Training" as the result.

 

Then you could issue some standard Replace() commands:

 

Replace(Replace(Replace([Result],"-","to"),"/","-"),"~","/") and you would end up with "Vacation 2-2 to 2-8 / Training"

 

And you could accomplish the same as the nested Replaces above by using the Find/Replace tool...

 

Alternatively, you could use ReverseString() and then a ReplaceFirst() to change the "/" to something else, followed by a ReplaceChar() to change all the remaining "/" characters, and capped off with another ReverseString() to flip it around again.

 

Others might advocate the TextToColumns() tool to split your string (on the space (\s) character?) into components which you can parse individually and then concatenate together again.

 

There are undoubtedly other approaches...it might be fun to see how many different ways the Community could accomplish this.

benjaminmartin16
6 - Meteoroid

hello, thank you for this reply! I was wondering if someone could explain what it is doing in simple terms for me so that I can learn how to adapt it in the future instead of just copying your work. thanks in advance!!!

PanPP
Alteryx Alumni (Retired)

Hi @benjaminmartin16 

 

Essentially what we are trying to do is separate the color name from the digits. 

 

1) Left(Data, (Length(Data) - 4))

 

we are removing the 4 digits from the right and trimming them so we are left with the color name.

 

2) Looking at the regex expressions (explained below) :

 

The \l is looking for lowercase letters

The + specifies that the lowercase letter(s) must appear at least once

The \d is looking for digits

The * specifies that the digits may or may not appear (or is there "zero or more times").

The brackets around the \l+ "mark" that part of the pattern as special

 

 

Hope this helps! If it does, please like this post. If you have any other questions, please let us know.

EN6924
10 - Fireball

EN6924_0-1670570076996.png

 

Labels