Regarding String functions

[#1]

Hello Guys,

Your helps are always appreciated!

Actually I have my excel file with details as below in a single field

"Business Acumen: The understanding. [Manages payroll effectively]",

"Leadership: A process. [Leads with integrity]",

"Communication Effectiveness: The ability to inspire confidence. [Efficiently conducts meetings]", 

….,

Attribute1,

Attribute2;

But I am looking for an output as below

Business: Manages payroll effectively

Leadership: Leads with integrity

Communication Effectiveness: Efficiently conducts meetings

Kindly let me know

Thanks,

Kiru

[datanibbler]

Hi Kiru,

so, from your examples, you want

- the part in front of the colon

- and the part in brackets after the colon

right?

=> Look for the SUBSTRING() function to split out a segment of the text, using a separator (the separator in this
     instance would  be the colon)

=> and try the MID() function (to cut pieces out of a text) and the INDEX() function (to find out at which position within
     a text a certain character (look for the brackets) occurs.

HTH

Best regards,

DataNibbler

[alexandros17 · Accepted solution]

I should use:

If(Index(MyField, 'Business') > 0, 'Business' & TextBetween(MyField, '[', ']'),

If(Index(MyField, 'Leadership') > 0, 'Leadership' & TextBetween(MyField, '[', ']'),

If(Index(MyField, 'Communication') > 0, 'Communication Effectiveness' & TextBetween(MyField, '[', ']'),

     MyField

)

)

)

[#4]

Thank you so much Guys and I really appreciate your timely help!

Kiru