Regex for conditions to concatenate columns

Question

I have a dataset where the data is fairly fragmented. With plenty of missing information. Thankfully I was able to find the supplemental data in another table. I need a way to smartly merge these fields together.

I figure a regex character match is the best option but my regex is very rudimentary.

Examples are as follows:

Station NameOutlet NameEast SDTV GuideWest SDTV GuideCH 14NBCCH 11NBCNational HDTV One

Expected output

Station NameOutlet NameTV Guide East SDTV GuideTV Guide West SDTV GuideNBC CH 14NBCNBC CH 14NBCTV One National HDTV One

There are over 2k rows and several hundred unique values for Station Name. Most where the data is missing follow similar formats as the ones above. In essence I need a regex (or similar formula) that matches on a format:

IF [Station Name] = "CH ##" then concat [Outlet Name] +  [Station Name]

IF [Station Name]  CONTAINS "SD" then concat [Outlet Name] +  [Station Name]

jasperlch · Accepted Answer

Hi @GoldenDesign04

You could use the formula below:

if REGEX_Match([Station Name], "CH \d+") or EndsWith([Station Name]," SD") or EndsWith([Station Name]," HD")
then [Outlet Name] + " " + [Station Name]
else [Station Name]
endif

In the formula above "\d+" means one or more numeric characters.

Attached is a sample workflow.

Jasper