Extract value in row + 1 and then replace null in preeceding column

[hellyars]

Question.

Reference START POINT and DESIRED OUTCOME below.

I used a Regex Tool to extract CAGE and the corresponding 5 digit code.

If Reg2 is not null I want to extract the Company Name from Row +1 and enter it into Reg 2; add Company to Reg 1.

START POINT...

DownloadData	Reg1	Reg2
yada yada yada	null	null
<td class="KeyCellCtLnk" title="TACO BELL" onclick="fnShowLookup('CAGE', this.innerHTML);" ontouchstart="ehST(this,event);" ontouchend="ehET(this);">45152</td>	CAGE	45152
<td class="KeyCellLt DesktopOnly">TACO BELL</td>	Null	Null
blah blah blah	Null	Null
blah blah blah	Null	Null

DESIRED OUTPUT....

DownloadData	Reg1	Reg2
yada yada yada	Null	Null
<td class="KeyCellCtLnk" title="TACO BELL" onclick="fnShowLookup('CAGE', this.innerHTML);" ontouchstart="ehST(this,event);" ontouchend="ehET(this);">45152</td>	CAGE	45152
<td class="KeyCellLt DesktopOnly">TACO BELL</td>	Company	TACO BELL
blah blah blah	Null	Null

[Kamran1991]

It seems a little confusing. Please elaborate it in a better way and try to share a dummy data in excel.

[mceleavey · Accepted solution]

Hi @hellyars ,

You can probably build these into the same Regex tool, but I think the first alters the structure of the second, so I built it in two for ease.

Reg2 - >(.*?)<

Reg1 - ['](.*?)[']

if isnull([Reg1]) and !isnull([Reg2]) then "Company" else [Reg1] endif

I hope this helps,

M

[hellyars]

Why? That is the real data.