modify formula to convert text

Please give this a try...

=PROPER(IF(ISNUMBER(SEARCH("(",B3)),REPLACE(B3,SEARCH("(",B3),255,""),B3))

Select allOpen in new window

In the attached, the formulas are placed in yellow cells.
textconvert.xlsx

Hi Neeraj,

One small problem, the 'apostrophe is different, Would you mind correcting.  Thanks
Piece D'Or
Piece D`Or

Here it is:
=PROPER(IF(IFERROR(FIND(" ";MID(B4;1;FIND("'";B4;1)+2);FIND("'";B4;1)+2)>0;1)=TRUE;MID(SUBSTITUTE(B4;"'";"");1;FIND("(";B4;1)-2);MID(SUBSTITUTE(B4;"'";"`");1;FIND("(";B4;1)-1)))

Or in this workbook to have the cells themselves change, select the cells you want to change, and  type Ctrl+Shift+P.
29122433.xlsm

With a slight change the cells could be converted any time you changed something on the sheet.

Neeraj, the formula is now perfect for my requirements. The formula is for some temporary checking when joining two tables.
Thanks
Ian

You're welcome Ian! Glad it worked as desired. :)

Regards,
Neeraj