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!