Parse a String using Regex/Formula tool with String commands

Question

Hello community,

I would like to extract data from the data in a column and extract the data using a delimiter second occurrence (.) from the end of the string. Below is the example and the expected output.

InputExpected OutputABC.DEF.GHIDEF.GHIJKL,MNO,PQRMNO.PQRSTU,VWX,YZAVWX.YZA

Can I get two possible options of achieving this. One would be using Regex and one would be using a formula similar to below, I was able to figure out on extracting the text after the last delimiter, but not two occurrences.

Right([JSON_Name], FindString(ReverseString([JSON_Name]), ".")) -  Formula to extract from the end for the first occurrence of a delimiter

Thank you in advance.

binu_acs · Accepted Answer

@ssripat3 one way of doing this with the regex

REGEX_Replace([Input], "^[^,.]+[,.]", "")

CoG · Accepted Answer

Here is another way to solve the problem with non-Regex Functions:

Formula (just make sure you reverse input before and after the following 2 formulae):

ReverseString(LEFT([Input],IIF(FindString(Substring([Input],FindString([Input],".")+1),".")=-1,Length(Substring([Input],FindString([Input],".")+1)),FindString(Substring([Input],FindString([Input],".")+1),".")) +FindString([Input],".")+1))

Or using Alteryx version 2024:

ReverseString(LEFT([Input],FindNth([Input],".",2)))

Alternative Regex approach that works for lists of longer than three strings:

REGEX_Replace([Input], "^.*[.](.*?[.].*?$)", "$1")

Note: I assumed that period character "." was your separator per the OP even though the sample data has bother "." & "," (i.e. commas were included in sample data).

Hope this helps and Happy Solving!