topic Re: Extract specific words from a text field in Water Cooler

Extract specific words from a text field

zwyne — Thu, 13 Dec 2018 13:16:20 GMT

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

Re: Extract specific words from a text field

sunny_talwar — Thu, 13 Dec 2018 13:25:03 GMT

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);

Re: Extract specific words from a text field

zwyne — Thu, 13 Dec 2018 13:34:07 GMT

Thank you Sunny, always a pleasure