Address List Sort Problem

[Ormond]

I have a list of addresses that I need to sort by the addressee's last name or the name of a business. My problem is that some of the persons have titles or are Juniors or Seniors. Should I search and replace with null Mr. & Mrs., Dr.,Jr.Sr. with a macro? (I am not good with macros.)
How do I perform the sort on the last name or first word of a business name?

I would prefer to place the sorted list in a new worksheet. I have approx. 4,000 records each in its own row. I use Excel 2003. A sample zipped spreadsheet is attached.

Thank you for your help!!!

Ormond

[Nik]

I'm not sure how Jr. or Sr. would be a problem as they would be not be at the beginning of the name.
However, I think I would do a search (not and replace) and as each Mr. and Mrs. or Dr. appears, either move their title to a new column or move their title to the end after a comma e.g.

Johnson, Mr. and Mrs.
Roland, Dr.

[Ormond]

How would I automate that? Can you help me with a macro?

I appreciate the help!!!

[Ormond]

1410 Mr & Mrs Dennis D Kaney;226 Triton Rd;Ormond Beach;FL;32724
1418 Mrs Brendan Sayers Jr;500 N Oleander Avenue;DeLand; FL;32724
1419 Microsoft Corporation; 1 Gates Way; Redmond; WA 11152

The first column is account number. Columns B through F contain the name, address, city, state and zip code.

Sort Order would be by name. Desired results for the three rows would be Kaney, Microsoft, Sayers.

Thanks for the help!