If cell "contains" text, insert word into another cell ?

Hi guys, I'm hoping someone can help with the following excel problem - or at least tell me it's not possible!

I have a database of customers and company names which I'm trying to sort through and arrange for a marketing campaign.

Presently, as the database comes to me, their naming details are all contained in column "A" separated with comma's- for example:

JONES,TONY
BROWN,JOE S
COMPANY,THE INDUSTRIAL COMPUTING
SHOP,THE OUTSIZE
TRUST,TWO TREES
WILLIAMS,MARK T R & JOHN W

Etc.

I need to have the customers "first name" in column "A" and "last name" in column "B". If the name is not a person (ie: a Trust or a company), I need it to simply insert "Sir or Madam" in Column "A" and leave column "B" blank.

A lot to ask I know, but I have several thousand entries and it'll take me days to go through each one to do this manually.

Any help would be appreciated.

Thankyou.

Not an easy one troy728. Messy. Start with text to columns, comma and space delimiters. The obvious ones will take up 2 cells and not require attention. Hopefully, depending on your data, you've knocked out quite a few. The rest look as though some heavy lifting is required. Use your filter to display those records with more than 3 columns, create helper columns to add "Sir or Madam" to a cell presumably in column A.

Good luck.

Pete

HI Pete, so still quite a lot of manual work required eh? I guessed as much. I managed to get the text to columns filtering done, but there's still a heck of a lot of companies, Trusts etc in the filtering results. I'd hoped to use a formula which said "if the word "Trust" or "Company" or "Inc" appears in cell A6 (for example) then insert the words "Sir/Madam" in cell H6 ???

I guess that's probably beyond the ability of Excel ???

Thanks for your comments. Appreciate the response

Hi, just for anyone who might be interested in the method I used:- Mainly using the "Conditional Formatting" in Excels toolbar.

• First I used "Text to Columns" to separate the names within column A (this provided me with another 5 horizontal cells from B-F with LastName, FirstName, Text_1, Text_2, & Text_3 - or whatever text/initials/letters were left over after the name etc).
  
• I then created a filter using Conditional formatting, which identified text ("TRUST") in any cell and gave it a coloured background.
  
• I replicated the Conditional formatting for every word I wanted to identify (COMPANY, INC, LLC), ending up with 4 Conditional Formatting entries.
  
• Next I sorted the sheet by "cell colour" of column A
  
• This put all the entries I needed to edit, together, and I simply copied the words "Sir/Madam" from the very topmost entry, downwards until I reached the end of the entries I needed to change.

At first I suspected it would be a long task, but after I created the filtering it took me less than 2 hours in total.

Hope this may be of use to someone else in the future.