Regex design help to parse data from cell

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... 🙂

Regex

Parse

Accepted answers

Jonathan-Sherman

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

20191128 - RegexFunction.yxmd

All comments

Jonathan-Sherman

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

20191128 - RegexFunction.yxmd

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,

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

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703