Data transformation

[RajatRehria]

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 Investments

Short-Term Investments held as collateral - cost

Securities on loan

Proceeds on securities

Receivable from Broker

[OllieClarke]

Hey @RajatRehria 

Try this:

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

Which will remove any brackets from the start of a string and the following space (if it's there)

Hope that helps,

Ollie

[OllieClarke]

@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

[RajatRehria]

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 Investments

Short-Term Investments held as collateral - cost

Securities on loan

Proceeds on securities

Receivable from Broker

can u pls help little more

[OllieClarke]

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

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

ollie