I am typically savy with Excel, but am a bit stumped, and I am hoping someone on this forum can lend some insights and guidance.
I am using the Text to Columns feature to separate values into separate columns - each value is delimited by a comma. Company Name, Address 1, Address 2, City.... and so forth. Easy Enough, right?
However, here is my issue: some, but not all companies have a ,Inc. (or something similar with a comma) in the company name. When that happens, a different column is made and all the data is not lined up properly - obviously, the ,Inc. is part of the Company name.
I can fix and clean up the data using filters, etc. This is fine for small imports, but is there an option in 2003 or formulal that I can use that takes things like ,Inc. into consideration?
My initial thought is to a replace before I do text to columns procedure (e.g. replace ,Inc. with just Inc.) and then a replace after the procedure. The issue there is that there could be be many variations - ,Inc. ,LLP ,PC etc.
Any insights into a more seamless solution would be wonderfully appreciated.
Best Wishes,
Don
I am using the Text to Columns feature to separate values into separate columns - each value is delimited by a comma. Company Name, Address 1, Address 2, City.... and so forth. Easy Enough, right?

However, here is my issue: some, but not all companies have a ,Inc. (or something similar with a comma) in the company name. When that happens, a different column is made and all the data is not lined up properly - obviously, the ,Inc. is part of the Company name.
I can fix and clean up the data using filters, etc. This is fine for small imports, but is there an option in 2003 or formulal that I can use that takes things like ,Inc. into consideration?
My initial thought is to a replace before I do text to columns procedure (e.g. replace ,Inc. with just Inc.) and then a replace after the procedure. The issue there is that there could be be many variations - ,Inc. ,LLP ,PC etc.
Any insights into a more seamless solution would be wonderfully appreciated.
Best Wishes,
Don