you want to search "A Abunayyan Trading Company" in Sheet1/ColumnA to return "Abunayyan"?

maybe you should set up a table which will include possible search strings and their desired equivalents.
then use index/match or vlookup.

it is hard to code "search for A Abunayyan Trading Company and return Abunayyan."

there must be a rule, logic, reasoning, algorithm, whatever you say.