+ Reply to Thread
Results 1 to 14 of 14

How to automatically replace certain words with other words?

  1. #1
    Registered User
    Join Date
    10-25-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    15

    How to automatically replace certain words with other words?

    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.

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to automatically replace certain words with other words?

    I am not going to do it for you, but what would have to be done is to create an algorithm similar to the ones web search engines use. You can do it using the worksheet_change event and use a Select Case to build the algorithm.

    Example
    Please Login or Register  to view this content.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Registered User
    Join Date
    10-25-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    15

    Re: How to automatically replace certain words with other words?

    Thank you JLGWhiz. How would I specify it to just one column instead of the whole worksheet?

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to automatically replace certain words with other words?

    Quote Originally Posted by Dieterl View Post
    Thank you JLGWhiz. How would I specify it to just one column instead of the whole worksheet?
    I use column A in the example, you can change it to whatever you need.
    Example:
    Please Login or Register  to view this content.
    Also note that this code assumes that the entry in the cell will only be the name and not embedded in other text.
    Last edited by JLGWhiz; 11-09-2018 at 05:19 PM.

  5. #5
    Registered User
    Join Date
    10-25-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    15

    Re: How to automatically replace certain words with other words?

    Thank you! This code works great except for some reason it is still applying to all cells rather than just column L (in my case). Here is my code. Any idea?

    Please Login or Register  to view this content.
    Last edited by Dieterl; 11-11-2018 at 07:26 PM.

  6. #6
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to automatically replace certain words with other words?

    You need to edit your Post #5 to put code tags around the code. To do that, simply use the mouse pointer to highlight the code, then click the HashTag or Pound (#) symbol on the tool bar.
    My error on the code should be:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-25-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    15

    Re: How to automatically replace certain words with other words?

    Thanks so much. This worked great. Edited my post code as well, thanks for the instructions.

  8. #8
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: How to automatically replace certain words with other words?

    Quote Originally Posted by Dieterl View Post
    Hello,

    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?
    Just an idea:
    You have 200+ manufactures name, it's long list.
    You may put the list in 2 columns (1.the right name 2. all the variations).
    Modified JLGWhiz code by populating the list into an array, and use the array in the 'Select Case' .
    The benefit:
    The code is shorter.
    Every time you need to change the list, just change the list, you don't need to change the code.

  9. #9
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to automatically replace certain words with other words?

    Quote Originally Posted by Dieterl View Post
    Thanks so much. This worked great. Edited my post code as well, thanks for the instructions.
    You're welcome. Don't know what your level of expertise is with VBA but if @Akuini could expand a little on how to set up the array, you might want to consider that apprach.
    Regards, JLG

  10. #10
    Registered User
    Join Date
    10-25-2017
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    15

    Re: How to automatically replace certain words with other words?

    My experience with VBA is sadly googling/copying/pasting and continuing strings from there. If Akuini could paste an example of the code I could take it from there. For column 2 with the variations would I just separate by comma?

    Thanks to you both!

  11. #11
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to automatically replace certain words with other words?

    If your original problem is solved, don't forget to mark the thread as solved.

  12. #12
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: How to automatically replace certain words with other words?

    Quote Originally Posted by Dieterl View Post
    My experience with VBA is sadly googling/copying/pasting and continuing strings from there. If Akuini could paste an example of the code I could take it from there. For column 2 with the variations would I just separate by comma?

    Thanks to you both!
    OK, here's an example.
    I put the macro on sheet1 & specifically targeting col L
    I put the list in sheet 'List' (you may want to hide the sheet then)
    I use 2 colon '::' to separate the words in col B, I assume the user won't insert '::' in the column L (in sheet1), if that is not the case then you need to change that.

    Please Login or Register  to view this content.

    The file:
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    10-01-2018
    Location
    Virginia Beach, VA
    MS-Off Ver
    365
    Posts
    129

    Re: How to automatically replace certain words with other words?

    Another option would be not allowing users to enter variations... if you set up a list of all manufacturers names you want allowed, and then use that list to populate a combobox, and lock inputs to only allow matches, then your users would only be able to enter "The Coca-Cola Company", or whichever variant you chose to put on the accepted entry list. That way, you don't need to try and anticipate every incorrect version of a manufacturer's name your users might come up with for over 200 manufacturers... the resulting code using the above suggested method would be ridiculously long and unwieldy.


    To see what I mean, check out the sample file I've attached.


    Click on "new Entry" to open the userform. On this example, I don't have the comboboxes locked down to only allow existing options to be entered, but you'll get the idea.


    The "Cutter" field options are populated from the "cutters" worksheet. The dropdown gives you a list of the cutters available, or you can simply start typing a name and it will fill from the list. If it was locked down, you wouldn't be able to enter anything not already on the list. (I know the "locking down" is possible, I just haven't yet searched out the best way to do it and incorporated it into my file yet... I'm a google/copy/paste guru like you, LOL!)


    Another option (perhaps not feasible in your case, depending on how your company is set up...) would be to assign each manufacturer a unique "Manufacturer number", and have your users enter that instead from a reference list. That would be similar to my "part number" field, which pulls the part numbers from one column on the "inventory" worksheet, and as the part number field is entered, the Part Description field is populated using a vlookup function as confirmation that the correct number has been entered.


    With either approach, you'd only need to define your list of "correct" manufacturer's names once, and then you wouldn't have to worry about leaving the manufacturer's names open to "interpretation".
    If either of those approaches sounds feasible, I could try and help you tailor my code to better fit your application if you can provide a sample file.
    Attached Files Attached Files

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: How to automatically replace certain words with other words?

    1) To Thisworkbook code module
    Please Login or Register  to view this content.
    2) To Sheet1 code module
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jindon; 11-13-2018 at 12:55 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 15
    Last Post: 10-12-2014, 08:53 AM
  2. Replies: 5
    Last Post: 09-02-2014, 03:49 PM
  3. if cells contain "words" refer to other worksheet and replace the words
    By adehond in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 11:22 AM
  4. Replies: 3
    Last Post: 11-28-2012, 04:00 AM
  5. Replies: 4
    Last Post: 04-27-2012, 06:38 AM
  6. Replies: 1
    Last Post: 04-04-2012, 07:15 PM
  7. [SOLVED] How to Replace multiple words to replace using excell
    By ramsun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-10-2006, 08:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1