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

 

[Muhammad_Usman]

Check this one !
Your data in A1

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-"," "),".",""),",","")

 

[JustynaMK]

Hi jimmy2times,

Apologies - this is not the most beautiful solution but works fine on my side...

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

 

[Muhammad_Usman]

Or you can use these formula!

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,$C$1," "),$C$2,""),$C$3,"")

$C$1 = -
$C$2 = .
$C$3 = ,

You can change character as you requirement! Further more you can add more character which you want to remove.

 

[JustynaMK]

Hello Muhammad,

I think what Jimmy required is to see the following output:

Company ABC
Company DEF
Company A Ltd
Company B Ltd
Company C

...so he wanted the formula to return the text before "-", "." and "," signs.

 

[Muhammad_Usman]

Hello Muhammad,

I think what Jimmy required is to see the following output:

Company ABC
Company DEF
Company A Ltd
Company B Ltd
Company C

...so he wanted the formula to return the text before "-", "." and "," signs.

OOOPS sorry brother I understand it wrongly !

 

[JustynaMK]

That's ok! Give it one more try - my function works but it's very lengthy and there must be an easier way to do that...

 

[jimmy2times]

Thank you - you are a genius!

PS if you do find an easier way please let me know!

Jimmy

 

[jimmy2times]

Thanks for looking at but not quite what is required?

Do you have a simpler version of the formula offered by justynaMk?

 

[TonyUK72]

Thanks for looking at but not quite what is required?

Do you have a simpler version of the formula offered by justynaMk?

A combined version works

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

It certainly compresses the formula