Extracting Word from a text String

[KevinJ]

I have this data. I want to extract it to OPEX, CAPEX, GREENLANE and use it as a column. Is that possible?

it will be a measure or dimension? Is that Possible?

Initial Table: 

Bacolod LBE OPEX

Cebu LBE GRN LANE

Terminal Ops VISAYAS GRN LANE

Surigao LBE OPEX

Terminal Ops MINDANAO OPEX

Manila LBE GRN LANE

Manila LBE GRN LANE

Bacolod LBE OPEX

Zamboanga LBE OPEX

Cebu LBE GRN LANE

Terminal Ops MINDANAO OPEX

RESULTS:

Bacolod LBE OPEX	OPEX
Cebu LBE GRN LANE	GRN LANE
Terminal Ops VISAYAS GRN LANE	GRN LANE
Surigao LBE OPEX	OPEX
Terminal Ops MINDANAO OPEX	OPEX
Manila LBE GRN LANE	GRN LANE
Manila LBE GRN LANE	GRN LANE
Bacolod LBE OPEX	OPEX
Zamboanga LBE OPEX	OPEX
Cebu LBE GRN LANE	GRN LANE
Terminal Ops MINDANAO OPEX	OPEX

[LRuCelver]

If you only have the three words you need to extract and they always appear at the end of the texts, you can use a few nested IFs to create the 2nd column. This can be done as both a measure or a dimension.

If(Right(TEXTFIELD, 4) = 'OPEX', 'OPEX',
If(Right(TEXTFIELD, 5) = 'CAPEX', 'CAPEX',
If(Right(TEXTFIELD, 9) = 'GREENLANE', 'GREENLANE',
'-' // Else. Delete this row or replace with Null() if this should be Null.
)))

If you need a more dynamic solution, creating a dimension might be the easier solution as long as the words always appear at the end. If they don't, it'll be a lot more complicated.

[marcus_sommer · Accepted solution]

I suggest to do the job within the data-model, maybe with:

pick(wildmatch(MyField, '*OPEX*', '*CAPEX*', '*GRNLANE*'),
   'OPEX', 'CAPEX', 'GREENLANE') as MyFieldExtract

[Anil_Babu_Samineni · Accepted solution]

@KevinJ Try this way?

If(Right(FieldName, 4)='OPEX', 'OPEX', SubField(FieldName, ' ', -2) & ' ' & SubField(FieldName, ' ', -1))