Regex for conditions to concatenate columns

[GoldenDesign04]

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 Name	Outlet Name
East SD	TV Guide
West SD	TV Guide
CH 14	NBC
CH 11	NBC
National HD	TV One

Expected output

Station Name	Outlet Name
TV Guide East SD	TV Guide
TV Guide West SD	TV Guide
NBC CH 14	NBC
NBC CH 14	NBC
TV One National HD	TV 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 solution]

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

[KK12]

Based on your example, it seems you want to concatenate each row.

Is it?