getting a specific line of text out from a long sentence with dynamic length

[Maharshishah]

Hello,

I have a query. I have a column with a long text line. In that line there is a specific line to text which i want to get out and show in another column. the start and end of the that particular line of data is fixed but their position in the sentence can wary. can someone please help create a workflow for the same. below are a couple of example

as per below example the text will always start with Gamma and end with A;

So need to design something based on that.

Input	Output
this is text which can start randomly; Gamma test length 6739 A; more data to follow	Gamma test length 6739 A
there can be more words in the text and then; Gamma length of test vary 74683 A; additional data to follow	Gamma length of test vary 74683 A

[binuacs]

@Maharshishah if you know the start and ending of the text then you can use the regex_replace function to get the result

[Diederik_vanderharst]

@Maharshishah I suppose in your case woulnd't a text to columns just do the job, where you select ; as separator and set output columns to 2 or 3? As much as I see value in Regex, for this use case a simple text to columns is more transparent for whoever needs to take over some day. Should your text be interlaced with semi colons, then by all means go for regex!

[Maharshishah]

Hello @binuacs 

thank you for the solution. The code did not work exactly, but able to tweak it a little and got the solution. Thank you very much for pointing in the right direction.

[Maharshishah]

hello @Diederik_vanderharst 

No this will not work exactly as the text is very long and the position of that text varies from row to row. so it will be a lot of columns and there was no guarantee that the text will be in that fixed column. hence it wont work there.