Delimiter on Text to Columns Issue

[Luck]

My final question (for the time being) retaining to text to columns (lol)! I want to move all the companies in my K column into separate columns based on a comma delimiter. Normally, this would be a simple issue, yet there exists some companies in the form:
"Company, LLC"
"Company, Inc."
"Company, LP"
"Company, The"
and "Company, L.P."

Therefore, the text to column operator would place the sequence "Company1, Inc, Company2" into 3 columns instead of 2.

It is of importance to note that not all companies containing these titles have a comma (thus one may not simply remove all commas prior to these titles). i.e. both "Company, The" and "The Company" exist.

I'm sure there is a simple script that may be ran preform this task, but my VBA is poor at the moment.

Thanks for any input in advance!

[TMS]

OK, let's be boring and simplistic.

I'd do a Replace All and replace ", Inc" with " ### Inc" ... that's comma space Inc with space hash hash hash space Inc. The reason for the spaces and the hashes is that it's unlikely that you'll have that combination in the live data.

Then you do your text to columns with your comma delimiter.

Finally, you can do a Replace All and replace " ### " with ", " ... that's space hash hash hash space with comma space ... and you should be back where you started with regards to the company name.

If this is a regular task, you could (macros) record the Replace Alls and the Text to Columns as you undertake them. See the code below.

There were 84", Inc"s in the sample provided (in column K)

Regards

Please Login or Register  to view this content.

You'd need to repeat the initial Replace All piece of code for any other variations like ", LLC"

So, repeat the block of code but change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.