String Within Special Characters

[knnwndlm]

Hi SME,

How do I capture an embedded string within special characters?  For example, here's what I have and what I want to get:

What I have:

• PONY///ANTELOPE VALLE S/C
• PONY //REDLANDS(KANSAS BLDG)
• PONY///PALM SPRINGS SERVCE CTR
• PONY///COMPTON 2000
• PONY///COMPTON, 2000
• PO/// MT6 @FULLERTON S/C
• PO/ WHITTIER SERVI
• PON///SAN JOAQUIN V
• PONY///Jason Meter Tech 6 - THOUSAND OAKS S/C ATTN:

What I want to get:

• ANTELOPE VALLE
• REDLANDS
• PALM SPRINGS
• COMPTON
• COMPTON
• FULLERTON
• WHITTIER
• SAN JOAQUIN
• THOUSAND OAKS

I attempted RegEx but only were able to capture some and not all.  I'm doing this long way and would like to learn RegEx to streamline it.  Appreciate your help.

Thanks,

kwl

[apathetichell]

Not optimistic about this. I'd recommend some kind of find&replace off of a master list to tag the entries instead.

[flying008 · Accepted solution]

Hi, @knnwndlm 

FYI.  (what is your logic for string 'PONY///PALM SPRINGS SERVCE CTR' ?)

REGEX_Replace(Trim(REGEX_Replace(Left([Txt], FindStringLast([Txt],' ')), 'P[A-Z]+\s*\/+\s*[^A-Z]|.*(?:@|-\s)', ''),', '), '(?:\(|\sS)[A-Z\/]*?$', '')

Txt	Get
PONY///ANTELOPE VALLE S/C	ANTELOPE VALLE
PONY //REDLANDS(KANSAS BLDG)	REDLANDS
PONY///PALM SPRINGS SERVCE CTR	PALM SPRINGS
PONY///COMPTON 2000	COMPTON
PONY///COMPTON, 2000	COMPTON
PO/// MT6 @FULLERTON S/C	FULLERTON
PO/ WHITTIER SERVI	WHITTIER
PON///SAN JOAQUIN V	SAN JOAQUIN
PONY///Jason Meter Tech 6 - THOUSAND OAKS S/C ATTN:	THOUSAND OAKS

[apathetichell]

If Antelope Valley was spelled correctly you can do find/replace with the location field from your other dataset. You'd use case insensitive and append [Location] to this datasource. Otherwise it works.

[knnwndlm]

Thank you Both!  The dataset I have consists of incorrect spelling of city name and that's fine because I'm doing this as a precursor to using the Fuzzy Match tool.  I used the tool prior to this and got nothing.  I suspected that that had a lot to do with all the special characters.