Data Cleaning with the search function

[jimmy2times]

Hello I have a column in my tab called company name that needs data cleaning. The data looks like this
Company ABC-London
Company DEF-Manchester
Company A Ltd
Company B Ltd.
Company C,

I want to remove everything after the company name EG hyphen, comma, full stop,place.

The formula I am using is =left(A2(search(",",A2)-1). This works if I have a hyphen and the town. EG Company ABC-London but will return a #value error for the other examples provided.

Can the formula be adjusted to clean the other data entries without returning an error message? So the result should e
Company ABC
Company DEF
Company A Ltd
Company B Ltd
Company C

Any help would be much appreciated.

Kind Regards

 

[jimmy2times]

A combined version works

=IF(IFERROR(SEARCH("-",A1),FALSE),LEFT(A1,SEARCH("-",A1)-1),SUBSTITUTE(SUBSTITUTE(A1,".",""),",",""))

It certainly compresses the formula

Thanks Tony,

In plain English, what is the formula doing to get the result?

 

[Sunny Kow]

My try on this one

=IFERROR(LEFT(A1,FIND(".",SUBSTITUTE(SUBSTITUTE(A1,"-","."),",","."))-1),A1)

It just replace all the unwanted characters with a full stop and then find it's position.
Then use the LEFT function to extract till one character before the full stop.

 

[jimmy2times]

My try on this one

=IFERROR(LEFT(A1,FIND(".",SUBSTITUTE(SUBSTITUTE(A1,"-","."),",","."))-1),A1)

It just replace all the unwanted characters with a full stop and then find it's position.
Then use the LEFT function to extract till one character before the full stop.

Thanks Sunny, brilliant in its simplicity.

 

[Sunny Kow]

You are welcome