RegEx Issue

[knnwndlm]

Hi SME,

I couldn't figure out why my RegEx formulas are not working for the items below:

                 REGEX                                 EXT_NAME                                        RESULT                                  SHOULD BE

1)    /s*/W*CAIFORNIA/s*/W*       ALHAMBRA , CAIFORNIA                   ALHAMBRAIFORNIA                     ALHAMBRA

2)    \s*\W*CALIF\s*\W*                MONTEREY PARK, CALIF.                 MONTEREY PARKLIF                   MONTEREY PARK

3)    \s*\W*CALIFORNI\s*\W*       HUNGTINGTON BCH. CALIFORNI    HUNTINGTON BCHLIFORNI        HUNTINGTON BCH

Items 1 and 3 are shown as working in Regex101 though.  Also, I have a case where I have the symbol right after the name (UPLAND').  How do I capture that symbol in my RegEx formula?

Thanks,

K

[Felipe_Ribeir0]

Hi @knnwndlm 

If i understood well, this formula should do the job ^(.*?)(?=\,|\.)

[knnwndlm]

Thank you @Felipe_Ribeir0!

How do I incorporate UPLAND' into the RegEx formula to remove the ' mark so that it's just UPLAND?

[Felipe_Ribeir0 · Accepted solution]

Hi @knnwndlm 

1) You could use this one 

^(.*?)(?=\,|\.|\')

[knnwndlm]

Thank you @Felipe_Ribeir0 

It's not a tick mark.  It's a mark that looks like this -  `  - which seems like a tick mark to me.  In any case, I substituted that symbol in place of the tick mark in the formula above and everything stopped working.  I ended up using the Data Cleansing to remove the mark.  Very strange.