community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Regex_match?

I'm new to Regex and believe I need this function for what I'm trying to do. 

 

I'm looking to setup a formula (Using IF, Then, and Else function I assume) to search for the last few words in a field/string, then if it finds that word, the output column will provide the "then" portion of the formula. For example: 

 

IF (Search column to find "Long" in the last few characters in that field/string)

THEN (Show me just the word "Long" in another output column)

ELSE (Would just return a "Null")

 

I need to do this for finding other words ("Short" and "Net), would I be able to combine or create 3 different formula functions? 

 

Another caveat is that the word "Short" could appear in a field/string with the last words as "Long" which is why I only need to look at the end of the field/string. 

Inactive User
Not applicable

Answer attached.

ACE Emeritus
ACE Emeritus

I'm going to assume that you have a field (I'm calling it [InputData]) that has the following text:

 

The long and short of it is that this is the best set of tools you'll find.

If I understand what you're asking, I can think of four ways to use the Formula tool to make a new output field that contains the text you're searching for.

 

1) Regex_Match

if regex_match([InputData],".*long.*") then 'long' else null() endif

2) Regex_Replace

regex_replace([InputData],".*(long).*","$1")

3) Using "Contains"

if contains([InputData],'long') then 'long' else null() endif

4) Using "Findstring"

if findstring([InputData], 'long') then 'long' else null() endif

All of these options will output to a single field so if you wanted to search for "Short" or "Set" and have those search terms output to new fields, you would need to create additional formulae to run those searches.

Will need to update my Alteryx Designer to view which need my admin to do so will get back to you to see if this was the solution. Thank you for responding. 

Thanks Tom. 

 

I tried these formulas but none of them gave my what I was looking to do. Here are some examples of what my field data has to search through: 

 

Account

AdminFees-Short

CashinLieu-Long

ShortTerm-Long

Cash-Net

 

So the 3rd account has both Short and Long in the field which is why I only want to look at the last word in the field as this gives me my true indication of whether to classify that row as short/long/net. So ideally I'm looking for my output to read the following using the same example above: 

 

Account                            Long/Short/Net

AdminFees-Short             Short

CashinLieu-Long              Long

ShortTerm-Long               Long

Cash-Net                          Net

ACE Emeritus
ACE Emeritus

Try this:

REGEX_Replace(SubString([Account],Max(FindString([Account],'Long'), FindString([Account],'Short'),FindString([Account],'Net')),1000),"(Long|Short|Net).*","$1")

This will return the last occurrence of Long/Short/Net even if those terms are followed by other values.

 

I added a couple examples to your list for my test.

 

Account

AdminFees-Short

CashinLieu-Long

ShortTerm-Long

Cash-Net

AdminFees-ShortTerm
LongNet-ShortTerm

 

Please note that if capitalization is important, you might want to either convert your Account field to a single case, or wrap each FindString([Account]...with a case conversion -- e.g. FindString(UpperCase([Account]),"LONG")

 

Asteroid

I am following this string: if i have a field that contains both letters and then alphanumeric, i want to seperate the alphanumeric from the field that contains all letters , how would i go about It?

For instance the column Diagnosis  contains  

                                     Ghost

                                      hallowen

                                      m.12346T<G

                                     MEF1C

                                      m.556786_778insT

 I want to separate the column to contain

                                      Column A                 Column B

                                      Ghost                      m12346T<G

                                       hallowen                  MEF1C

                                                                       m.556786_778insT

Thanks 

          

Asteroid

If endswithstring([searchcolumn],"long") then "long"

elseif endswithstring([searchcolumn],"short") then "short"

else

  "lifeistooshortforregexes"

endif

Labels