Remove Text Before and After

[bsolove]

I have a string field that looks like this KJDFKLJHD2381KJAS2018COMPANYNAMEREPORTLJKSDNAEHD.

I want to isolate Company 1 in this example, but the records may have different company names.  How do I remove everything from the left up through 2018 and everything to the right up through REPORT?

[Thableaus]

Hi @bsolove 

Does 2018 change? Is the year dynamic?

If you could show more examples of sample data, it would be good.

Cheers,

[bsolove]

No, 2018 and REPORT would be consistent between each record, but the characters before 2018 and after REPORT may change.

[JessieC · Accepted solution]

@bsolove 

You can use the RegEx tool to parse it out: \w+2018(\w+)REPORT\w+

If you're unfamiliar with RegEx, I find this website very useful in creating the Regular Expression - https://regex101.com/

[Thableaus]

@bsolove 

Here's a way using FindString and Substring functions:

Substring([Field1],FindString([Field1], "2018"),FindString([Field1], "REPORT") - FindString([Field1], "2018"))

This way you isolate 2018COMPANYNAME.

You can then use - Replace(Field, "2018", ""), to get rid of 2018.

Cheers,

[Thableaus]

@bsolove 

This also works directly:

Substring([Field1], FindString([Field1], "2018") + Length("2018"), FindString([Field1], "REPORT") - FindString([Field1], "2018") - Length("2018"))

Sometimes when you have a well defined scenario, playing with string functions may not be that bad!