Log in  \/ 
Register  \/ 


unanswered Left trim after finding multiple delinators

10 months 4 weeks ago #2072 by makitasr
I have a messy list of 100's of customers with multiple address some have commas, others periods and other still dashes.  I want to trim left from the special character and just end up with the business name.  See examples below.

XYZ Business, 123 Main Street
XYZ Business - 234 Main Street
XYZ Business, Inc,  345 Main Street
XYZ Business @ 456 Main Street
XYZ Business  567 Main Street
ABC Tool and Die, 123 First St
ABC Tool and Die 234 First St
ABC Tool and Die - 345 First Street
ABC Tool and Die, Inc, 456 First St
ABC Tool and Die @567 First St
When I'm I want a column that is just XYZ Business and ABC Tool and Die

I tried using =SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2)))>0 to find the characters but am not able to use that with =LEFT

Please Log in or Create an account to join the conversation.

Moderators: jaychivoExcelfansqiuhongkun