Regex design help to parse data from cell

[CraigF]

I'm struggling trying to design code to pull specific data out of an excel column and put it into a new column. Essentially a new output column LOCATION that has any info after LOCATE, LOCATE:, LCN, LCN: excluding any leading space. Since the location information, if included, is always at the end, and can be anywhere from 9 to 30 characters long so I assumed "everything after the four variations of LOCATE/LCN" is easiest.

Example of data contained in one column:

1.  ACCOUNT: 65423 LOCATE: 32498FORT (VANC-263-50)
2.  SERV 141831 ACCOUNT 14628-52971 LOCATE:32498WASH( VANC-263-100)
3.  ACCOUNT: 54255 TICKET NUMBER:6019144
4.  ACCOUNT: 56289
5.  SERV 141825 ACCOUNT 11628-52970 LCN:32193WASH
6.  ACCOUNT# 78305-49308 SERV: 120231

7.  

8.  LOCATE 70018MEDI (VANC-2379-10)

Desired new column result:

LOCATION
1.  32498FORT (VANC-263-50)
2.  32498WASH( VANC-263-100)

3.  

4.  
5.  32193WASH

6.  

7. 

8.  70018MEDI (VANC-2379-10)

I'm sure I'm overlooking something obvious... 🙂

[Jonathan-Sherman · Accepted solution]

Hi @CraigF,

This regex expression should get your desired outcome:

.*(?:LOCATE:|LOCATE|LCN:|LCN)(?:\s+)?(.*)

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan

[Thableaus]

Hi @CraigF 

Try something like this - Formula tool

IF Contains([Field1], "LOCATE") OR Contains([Field1], "LCN") THEN
REGEX_Replace([Field1], ".*(?:LOCATE|LCN)\W*(.*)", "$1")
ELSE "" ENDIF

Cheers,

[CraigF]

I was off on the wrong track, you put me back in line)