Remove Punctuation

[MCosca]

I have a data set of accounts which is about 20 columns and 800 rows (could be more or less depending on the month). The data is extracted from another program/server, then saved to excel. In column "C" I have business names. I am trying to identify which business names are for the same account. The issue I come across is typically punctuation. For example one of the accounts shows in row 43 as XYZ Tile Co. Inc. but in row 387 (the companion) shows XYZ Tile Co Inc (no punctuation). There are several other accounts which fall into this same area.

There is no other common field except for the account name, and the goal is to have the accounts related on the sheet. An alpha sort is very simple and does help a lot. However, there are other formulas I'm using in order make further analysis, which would function better if the punctuation was removed, or at least identical.
Any thoughts?

 

[Beyond_avarice]

Quick and dirty: highlight your column "C" and select ctrl+H

Find what: . Enter the punctuation
Replace with: Leave Blank

Replace All

 

[MCosca]

not sure why I didn't think of that. thx, it may just work enough to get me the results I need

 

[Beyond_avarice]

You can also do this with Limited vs Ltd, Corp vs Corporation, "N " vs North, etc

I would generate a pivot table and identify what differences in naming conventions you have. Modify from there.

It would be better if your system generated a unique client/vendor account number; then the name would be irrelevant.