Remove everything before and after a specific word

[ssripat3]

Hello community,

I am currently getting a response from a API and I use Text to Columns to convert the data into the below format.

DownloadHeaders	JSON_Name	JSON_ValueString
HTTP/1.1 200 Connected HTTP/1.1 200 OK Cache-Control: private, max-age=0 Transfer-Encoding: chunked Content-Type: application/json;odata=verbose;charset=utf-8 Content-Encoding: gzip Expires: Sun, 02 Apr 2023 14:13:58 GMT	d.Name	ABC.csv
HTTP/1.1 200 OK Cache-Control: private, max-age=0 Transfer-Encoding: chunked Content-Type: application/json;odata=verbose;charset=utf-8 Content-Encoding: gzip Expires: Sun, 02 Apr 2023 14:14:03 GMT Last-Modified: Mon, 17 Apr 2023 14:14:03 GMT	d.Name	DEF.csv
HTTP/1.1 200 OK Cache-Control: private, max-age=0 Transfer-Encoding: chunked Content-Type: application/json;odata=verbose;charset=utf-8 Content-Encoding: gzip Expires: Sun, 02 Apr 2023 14:14:08 GMT Last-Modified: Mon, 17 Apr 2023 14:14:08 GMT	d.Name	GHI.csv

DownloadHeaders	JSON_Name	JSON_ValueString
HTTP/1.1 200 OK	d.Name	ABC.csv
HTTP/1.1 200 OK	d.Name	DEF.csv
HTTP/1.1 200 OK	d.Name	GHI.csv

I would like to get rid of anything other than HTTP/1.1 200 OK from the DownloadHeaders column. The objective here is to send an email with the filenames only when DownloadHeaders is HTTP/1.1 200 OK is present.

Thank you

[ShankerV · Accepted solution]

Hi @ssripat3 

One way of doing this.

(.{0,})(HTTP/1.1 200 OK)(.+)

Input was:

Many thanks

Shanker V

[PhilipMannering]

If the objective is to send emails when you have a 200 OK, then just filter with,

Contains([DownloadHeaders], '200 OK')

[cjaneczko]

Another way. 

IF Contains([DownloadHeaders], 'HTTP/1.1 200 OK') THEN 'HTTP/1.1 200 OK' ELSE '' ENDIF

[ssripat3]

Thank you for the solution @ShankerV