Stripping Out Un-Needed Cell Contents and Reordering What Remains

[NW_JL]

Hi, I'm new to the forum and will appreciate any help you can give to help me automate clean-up in a QuickBooks export file I have to use:

Most cells in Col A contain individuals names displayed as FirstName LastName. Some line items (about 50% of all lines) begin with "CompanyName, City:" or just "CompanyName:" preceding FirstName LastName. Text is random length when present but always before ":". I need to strip out everything from the ":" to the left and then work with what's left to get a name list displayed as Last, First.

Of course, some of the names also come with a MiddleInitial which must be retained as well, just to add to the joy of my Sunday morning!

Ultimately, I need to end up with a column of names reordered as Last, First MiddleInitial -- with no Company or City info.

Now: Company, City:First Last or Company:First Last or Company, City: First Last MiddleInitial

Need: Last, First MiddleInitial

I've been testing some LEFT and LEN combinations but haven't yet been successful -- can you point me in the right direction, please?

Thanks so much!

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic