Trim Right with Wildcard

[ddye]

Hi, 

I have a field with multiple '.' in the string.  I am trying to remove the last instance of this, but the last word in the string varies in lenght.  It there a way to trimright([field], '.*') so it alwasy just removes the last '.abcdf'?

ex. 

pages.0.questions.2.answers.choices.0.text   to    pages.0.questions.2.answers.choices.0

pages.0.questions.2.answers.choices.0.id      to    pages.0.questions.2.answers.choices.0

Thanks a lot.  

[MarqueeCrew · Accepted solution]

Quick answers:

Regex_Replace([text],"(.*)([.].*)$",'$1')

Alternatively 

GetWord(ReverseString(Replacefirst(Reversestring([text]),' ')),0)

A couple of ideas from my iPhone

[ddye]

Thank you very much.  That is perfect.

[MarqueeCrew]

You're quite welcome. Sorry for the terse reply. The first one looks for a group of any characters followed by a period followed by any characters followed by the end of the string. It then returns just the first group of characters.

The second expression flips the string backwards and replaces the first (last) period with a space. It then flips the string again, creating two strings separated by a space. It then gets the first (zero-based) word.

Thanks,
Mark

Sent from my iPhone

[KAFord]

Hi there --- I'm struggling with a regex_replace formula as well, hoping that you can help. Here is an example of the strings: 

PPPA-9000A-28/CRJS-9000-28-Dissertation2016 Fall Qtr 08/29-11/20-PT27

HUMN-9001-48/COUN-8560-48/SOCW-9000-48-Dissertation2016 Fall Qtr 08/29-11/20-PT27

I need to remove everything after the last section number (highlighted in red). I cannot seem to get the syntax right to remove all of the following information and as you can see, the courses listed are not always the same length; the first one has two courses and the second one has three courses...sometimes I might even have four courses listed. 

Any ideas on the syntax that I might use to trim off that -Dissertation... information? Would greatly appreciate it! 

[RodL]

You could use a RegEx tool with "(.+)-Dissertation.+" as the expression and set to Parse.

That will keep just what's in the parentheses...assumption is there is always "Dissertation" in the data.

[KAFord]

Hi Rod --- I used the following syntax and it works:

TRIM(Left([Course Name], FindString([Course Name], "-Dissertation")))

The problem is for another file it has many different course name beginnings so while that works for the file I was working on that only had dissertation courses, for regular courses, it won't work.  Here is an example of the file I am working with now: 

EDDD-8006-10/EDPD-8006-10/EDSD-7006-10-Leading the Future of Ed2016 Fall Qtr 08/29-11/20-PT29 

NURS-6521D-1/NURS-6521N-1-Advanced Pharmacology2016 Fall Qtr 08/29-11/20-PT27

With my "regular" courses, they will all have different names like above. 

One of your colleagues, Andy, provided me with a suggestion in another thread so I'm going to see if that works however, if you have a suggestion, I am open to it since I literally just starting working with Alteryx this week.

Thanks, Rod! 

[RodL]

Yes, your data does vary and that's where you need to understand all of the variations to make sure your RegEx conforms to all situations.

This seems to work for the extra data you are showing...

(.+-\d+)-[[:alpha:]]+.+

This assumes that the structure is...

Course codes, with the final code ending in one or more digits [(.+-\d+)], followed by a hypen [-], one or more alphabetic characters [[[:alpha:]]+], and then any number of other characters [.+].

Note that in the solution provided on your other post by @AndyM, his assumption (based on the data you provided) is that there are 2 digits and a hyphen just before the course description.

Since there will typically be a NULL response if the RegEx doesn't fit, one way to test is to run the process with all of your data and then filter on the output field testing for NULL values.

RegEx is a learning process that I've basically learned by experimentation and lots of googling.

[KAFord]

Thank you so much, Rod. This is my first week working with Alteryx other than following someone else's workflows that I have found online. As I read, I knew Regex was going to be what I needed but I really need to understand how the syntax works. I have the cheat guide for Regex but it is definitely more to learning than just the cheat guide. I really appreciate your guidance. I will give this a shot on Monday and let you know how it turns out. 

Again...many thanks! 

--Kim

[KAFord]

Hi Rod --- I've been trying to run this with no luck. It keeps crashing Alteryx so I'm going to have to find a better way to do this. I only have 9497 records so I'm not certain why it keeps crashing but it does.