Hello,
I manage a sheet at work where we type or paste manufacturer names into cells. We currently have a problem where our data is messy because people will type in the same manufacturer name multiple different ways (ex: "The Coca-Cola Company" as "Coca Cola", Coca-Cola", "Coca-Cola Co."). We eventually use this data in a backend system to look up content. This is a problem because all of our "The Coca-Cola Company" content isn't in one central place because it's spread out across all the different name variations.
What I'm trying to do is create a way in our excel sheet where when something is typed or pasted it will automatically change it to the right spelling. What I've tried so far and failed at:
1. Autocorrect Options: Doesn't work when pasting, only when typing. This also seems to only work on my computer and across all workbooks I work in then. I only want it to apply to this worksheet across the shared drive.
2. Conditional Formatting: I tried to apply a formula to a cell with SUBSTITUTE and IF but it doesn't seem to doing anything. It's possible I'm writing it incorrectly.
I need to apply this to about 200 different Manufacturer names with each manufacturer name having multiple incorrect variations tied to it. Is there an efficient way to do this?
Ideal Outcome: When typing "Coca-Cola", "Coca Cola", "Coca-Cola Co." they all autocorrect to "The Coca-Cola Company". When typing "J&J", "Johnson and Johnson", "JNJ", they all autocorrect to "Johnson & Johnson". etc. for 200+ manufacturer names. Lastly, I need this only applied to 1 column and not the entire sheet.
Thanks in advance.
Bookmarks