Extract using right most delimiter

[zchotani]

Hello,

I need help to get text in a separate column/field using the right most delimeter. Example below:

MN-Owatonna - 26th - Ortho Clinician ---> New Column = Ortho Clinician

Can someone please help. I suppose this will require RegEx under parsing.

[ChrisTX]

Try the RegEx tool in Parse mode with this regex:   .*\-\s(.*)

The website regex101.com helps to test and learn regular expressions.

Chris

[ShankerV]

Hi @zchotani 

Please find the expected output.

Many thanks

Shanker V

[MarqueeCrew]

@zchotani ,

No RegEx is required!

You can use a couple of string functions to accomplish this task.  The last delimiter is the first delimiter if you look in a mirror.  So use the ReverseString() function.

ReverseString([Field1])

This will yield you with: naicinilC ohtrO - ht62 - annotawO-NM

Now you can use the FindString() function to find the first Delimiter "-":

FindString(ReverseString([Field1]), "-")

This will yield you:  16

Now use a TRIM function) to make sure that any leading/trailing spaces are removed as:

Trim(Right([Field1],FindString(ReverseString([Field1]), "-")))

Cheers,

Mark

[zchotani]

Thank you all, all of the solutions above worked but I realized my data is not as clean as I would've liked. Here is a more complicated situation where I need to extract the right most delimiter but I have multiple delimiters. One way I can think of it is to run multiple regex tools to address each delimeter:

MN-Owatonna - 26th - Ortho Clinician

MN-Owatonna - 26th- Ortho Clinician

MN-Owatonna - 26th -Ortho Clinician

I guess for the above three I need regex to pull everything after "-" instead of " - " then I can use cleanse tool to get rid of leading spaces from Ortho Clinician

MN-Owatonna - 26th Ortho Clinician

MN-Owatonna - 26nd Ortho Clinician

For the two above, I need a regex expression to parse out after "th " and "nd "

Can someone please help me with both RegEx expressions? I don't know if regex can handle multiple delimiters. If so, we need one expression to parse anything after the right most delimeter which can following:

"-", "th ", "nd "

Thank you in advance!

[ShankerV]

Hi @zchotani 

Please find the expected result.

[MarqueeCrew]

@zchotani ,

You're right.  It is impossible to do this without RegEx.  Not.

IF 
     Right(GetWord(Trim(Right([Field1],FindString(ReverseString([Field1]), "-"))),0),2) in 
     ('st','nd','th') 
     AND Length(GetWord(Trim(Right([Field1],FindString(ReverseString([Field1]), "-"))),0)) <=4 
THEN
Replace(Trim(Right([Field1],FindString(ReverseString([Field1]), "-"))),GetWord(Trim(Right([Field1],FindString(ReverseString([Field1]), "-"))), 0)+" ",'')
ELSE
Trim(Right([Field1],FindString(ReverseString([Field1]), "-")))
ENDIF

Cheers,

Mark

[zchotani]

@ShankerV  Will your regex also address the following so I can get Ortho Clinician from scenarios below as well?

Note, if you use - here then we get 26th and 22nd in there. In the case below we may have to leverage "nd " and "th "

MN-Owatonna - 26th Ortho Clinician

MN-Owatonna - 22nd Ortho Clinician

[Christina_H]

I'm sure there are neater ways to do it, but here's some RegEx that works for your examples:

.*\d{1,2}\u{2}\s?-?\s?(.*)$

[zchotani]

Sorry, this one didn't work. My extraction is not always "Ortho Clinician"...

How can I extract anything after a right most "nd " or "th " using regex?