Extract specific words from a text field

[zwyne]

Hello everyone, 

PFA

I am trying to extract words from a string with variable length,

String	Expected Result
BAGL - BSAA Business Bank - CA	Business Bank
BAGL - BSAA CIB - CA	CIB
BAGL - BSAA EDCON - CA	EDCON
BAGL - BSAA PEP Stores - CA	PEP Stores
BAGL - BSAA Retail - CA	Retail
BAGL - BSAA WFS - CA	WFS
BAGL - BSAA WIMI - CA	WIMI
BAGL - BSAA WIMI ROA - CA	WIMI ROA
BAGL - Botswana - CA	Botswana
BAGL - Ghana - CA	Ghana
BAGL - Kenya - CA	Kenya
BAGL - Mauritius - CA	Mozambique

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD String,
	 Right(SubField(SubField(String, ' - CA', 1), ' - ', 2), Len(SubField(SubField(String, ' - CA', 1), ' - ', 2)) - Index(SubField(SubField(String, ' - CA', 1), ' - ', 2), ' ', 1)) as [Expected Result]
FROM
[..\..\Downloads\Sample Data.xlsx]
(ooxml, embedded labels, table is Document_LB04);

[zwyne]

Thank you Sunny, always a pleasure