Trim right hand portion of text

[jb]

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!

[RodL · Accepted solution]

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

[tom_montpool · Accepted solution]

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]

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]

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]

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]

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]

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]

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]