Data transformation

Question

Hi team i have below kind of data:

(a)   Equities - cost (b)   Short-Term Investments(c)   Short-Term Investments held as collateral - cost(d)   Securities on loan(e)   Proceeds on securities(^)   Receivable from Broker

my desired output is below: As i want to remove the brackets from any line items which starts with ( ) brackets using RegEx formula, kindly help

Equities - cost Short-Term InvestmentsShort-Term Investments held as collateral - costSecurities on loanProceeds on securitiesReceivable from Broker

OllieClarke · Answer

Hi @RajatRehria ah it looks like it might be a tab. Try:

REGEX_REPLACE([Field],’^\(.*?)\s*’,’’)

ollie

RajatRehria · Answer

Hi @OllieClarke many thanks for the first RegEx solution, however the Regex formula is workflow but its keeping the initial whitespace intact, i tried Trim formula as well but its not working. This is what i am getting after using the Regex formula:

Equities - cost    Short-Term Investments   Short-Term Investments held as collateral - cost   Securities on loan   Proceeds on securities

i want this:

Equities - cost Short-Term InvestmentsShort-Term Investments held as collateral - costSecurities on loanProceeds on securitiesReceivable from Broker

can u pls help little more

OllieClarke · Answer

@RajatRehria for a non-regex solution (which will probably be quicker, try this:

TRIM(SUBSTRING([Field],FINDSTRING(([Field],')'))

Which takes the substring from the first instance of ) and then removes any leading/trailing white space

Ollie