Extracting word from text

[juliakhaa]

Hi all!
Dear users of this community, please help me with one question.
I have a field from which I need to extract a word that starts with ZNO followed by some set of numbers (string type), for example, ZNO123573.
That is, let's say the field has the following text: "Closing the account ZNO127463 will be executed on 20.04.23" at the output I want to get "ZNO127463".

The word can be anywhere in the text, even on the next line

[N30fyte · Accepted solution]

='ZNO' & TextBetween ('closing the account ZNO1235153 will be executed on 20.04.23','ZNO',' ')

Should get you started, but you'll need to apply it to the field rather than the quoted text.

NB the third parameter is a single space.

[juliakhaa]

I understand, but how to make sure that the text after this word does not remain in the field, I need only one word. Because I get "ZNO1235153 will be executed on 20.04.23"

[N30fyte]

That's what I get if I don't  include a space between the quote marks for the third parameter. The following expression (single space marked) gives me ZN01235153:

[juliakhaa]

I got it! Thank you so much! 

Sorry, can you suggest for the last time, let's say in the lines where there is no word ZNO, I would like to be blank lines or dashes, not the word ZNO, how can it be done in your code?

Thank you, I’m a fan of yours

[N30fyte]

If you can give a complete example (content of text field and intended result) then I'll try.

No promises though... 😉

[juliakhaa]

In addition to the example I gave above, let's say there is a field with the text "Opening / closing / maintaining an account" or "The account will be closed 24.04.23" I would like in such cases, where there is no word ZNO(any numbers) was a dash or a blank field

[N30fyte]

So if ZNO***** appears in the string, then ZNO****** should be displayed, otherwise a dash?

I set up a table with just one field, called text_field (original or what? 😆)

In my load script I load just two rows inline:

znotable:
load * inline [
text_field
'This text includes ZNO12356 and other information'
'This text does not include the dreaded string'];

My table has two columns: a dimension with the original text_field, and a measure (labelled 'Filtered') with the following expression:

= if (index (text_field,'ZNO') = 0 //If the string 'ZNO' does not occur in the text field
, // then
'-' // a dash
, // otherwise
'ZNO' & TextBetween (text_field,'ZNO',' ')//our original expression
)

(Don't forget the space 😉) 

This gives me the following table:

 

Hope that's what you need...

[juliakhaa]

Yes, that's it, you're very good, thank you very much.

Ps. Still a fan of yours at❤️

[N30fyte]

Just realised that the above doesn't work properly if 'ZNO****' is at the end of the string. Still thinking...