Left & Search

[smartbhau]

Hi Guys,

This is what my text looks like.

Example (2) PTY LTD (VIC)
Example (2) PTY LTD-STHN (VIC)
Example (2) PTY LTD-NTHN (QLD)
Example (2) PTY LTD-CNTRL (NSW)

I want to show data which is in front of second set of brackets - Example (2) PTY LTD

I am using formula =LEFT(A1,(SEARCH("(***)",A1,1)-1))

It is giving me result - Example

What am I doing wrong? is there any other way to do this?

Thanks
Mike

 

[jolivanes]

One way would be

LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"(",CHAR(1),2))-2)

 

[smartbhau]

Thanks joivanes

Works like a charm!! Appreciate your help.

One way would be

LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"(",CHAR(1),2))-2)

 

[jolivanes]

Thanks for the feedback.
It would be better to use following in case there is no space before the 2nd (

=TRIM(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"(",CHAR(1),2))-1))