Text Trimming Issue

[Harsha_01]

Hello Everyone,

I have facing Issue with trim the Text for custom and un even text. I want to trim the text in between brackets. But the problem is The text length is not Even length. Please see the Following Text. Any help would be very help to me.... Thanks

I have used substring, Pad right, R trim, Replace but no Luck

Source Text:

BNDCCAMXX (ABC BANK OF ITLY)
HSBCSGSGRRPOZZ (The Hongkong Banking)
SCBLINCCBDEL (Indian Bank)
CHASAU3 (JP Morgan Canara Bank)

Required Result Text:

BNDCCAMXX
HSBCSGSGRRPOZZ 
SCBLINCCBDEL 

CHASAU3

Thanks in Advance

Harsha

[BRRLL99 · Accepted solution]

IF you want to remove spaces from Column 1, you can use Data Cleansing Tool,

Column 2 and 3 can removed using select tool

If you got the expected result Please like and accept it as solution

[MilindG]

Okay, I have attached a file that shows you couple ways to do this.

[Harsha_01]

I tried but it's not working

[BRRLL99]

Please show ur workflow

[Christina_H]

A formula would be another option:

if Contains([text],'(') then trim(left([text],FindString([text], '('))) else [text] endif

[CarliE · Accepted solution]

Hi @Harsha_01 

Just use a regular expression to parse out the data! This looks like it get your desired outcome 

Here is your regular expression:

(.+)\s\(

Please mark this as a solution if this solved your issue.

[Harsha_01]

Hi Carlie,

Thanks for the Help your solutions is working like a charme, Thanks alot

Regards,

Harsha

[Harsha_01]

Hi'

For other data file this solution is working like star..... Thanks a lot for your kind and support 

Regards,

Harsha