This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
if regex_match([InputData],".*long.*") then 'long' else null() endif
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.
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:
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:
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.
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")