+ Reply to Thread
Results 1 to 22 of 22

Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

  1. #1
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Good day everyone,

    Coming to the experts for assistance. I must say that I am not an Excel expert and my programming skills are limited so I would appreciate working code over pseudo code. Thank you in advance.

    I am looking for a parser that would find specific words in an Excel worksheet cell using a list of words or dictionary for the most common spellings/mispellings of the word. Once the word(s) is found, a copy of the word will be placed under different Excel columns. Each column is a different category.

    The code would read a text file (dictionary file) and compare the word(s) against Column C (Event Description). When the word or words are found, the word or words are copied to Columns D, E, F, G or H after logic in the code determines the category the word falls under; or simply by specifically comparing the words under different headings in the text file (FRUITS, VEGETABLES, PRODUCE, CARB SIMPLE, CARB COMPLEX.

    One column, like Column E (Vegetables) could have all the vegetables found in Column C (Event Description) separated by commas. A different way would be to have each vegetable word found copied to a separate column; for example, corn would be placed under the column Vegetable 1, carrots would be placed under the column Vegetable 2 and peas would be placed under the column Vegetable 3.

    For example:
    Column A (Name): Mary
    Column B (Last name): Jones
    Column C (Event Description): This morning for breakfast I had oatmeal with milk, sugar and bananas. For lunch, I ate a bowl of vegetables which included corn, carrots and peas.
    Column D (Fruits): bananas
    Column E (Vegetables): corn, carrots, peas
    Column F (Produce): milk
    Column G (Carb simple): sugar
    Column H (Carb complex): oatmeal

    Thank you,

    Edwin
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    For simple sheet.
    text file is missing "carrot", so it doesn't pick up.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Hello Jindon,

    I am forever grateful for your assistance.

    Thank you very much.

    Edwin

  4. #4
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Good day from Atlanta, Jindon

    Is it possible to modify the code so that if there are multiple occurrences of the same word, i.e. “banana”, in Column C (Event Description); only 1 occurrence of the word “banana” is placed in Column D (Fruits)? It avoids populating Column D with “banana, banana, banana” etc.

    Thank you!

    Edwin

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Then you need to define "standard" word to compare with.

    I took the first word right after the category heading from the text file, so you will need to change them accordingly, if needed.
    And the text file needs to be formatted like attached.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jindon; 10-14-2020 at 08:37 AM. Reason: Code has been replaced and text file attached.

  6. #6
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Thank you very much for your time and generosity, Jindon.

    Edwin
    Last edited by ATL10SPRO; 10-18-2020 at 09:52 AM.

  7. #7
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Hello Jindon,
    I hope everything is well.
    The problem I am having with the code is that it will only match the first word under each heading in the dictionary file. For example, let’s say that I add another sentence in column C “Event Description”. FROM “This morning for breakfast I had oatmeal with milk, sugar and bananas. For lunch, I ate a bowl of vegetables which included corn, carrots and peas” TO “This morning for breakfast I had oatmeal with milk, sugar and bananas. I like bannanas very much. For lunch, I ate a bowl of vegetables which included corn, carrots and peas”. As you can see, I added “I like bannanas very much”. “bannanas” misspelled. I want to find misspelled words as well that match the list of words under the headers of the dictionary file because many times users enter misspelled words.

    The code will not find the misspelled word “bannanas” and placed it under column D “Fruits” together with the first instance of the word banana it found. The cell only shows “bananas”. But, I need the cell to contain all matches.- “bananas, bannanas” and any other misspelled word in column C “Event Description” which matches the list of words under each header in the dictionary file.

    For multiple, exact spelling, of the same word (correct spelling and misspelled) found in column C “Event Description” matching any of the words under the headers in the dictionary file, only one instance of the word is placed in the associated column in the worksheet. Using the additional sentence I added to column C “Event Description” but with bannanas correctly spelled as in “This morning for breakfast I had oatmeal with milk, sugar and bananas. I like bananas very much……”. The code would place in column D “Fruits” only one instance of the word “bananas” followed by the misspelled word “bannanas”. Like, “bananas, bannanas”.

    I have uploaded the revised Excel Worksheet and dictionary file to illustrate what I am looking for. The file names have the suffix -v2.
    I am very appreciative and grateful for your assistance. Do you have a PayPal account, where I can deposit a little contribution for all your assistance?

    Thank you,

    Edwin
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    See if this is how you wanted.

    If any matching word found, regardless of correctly or wrongly spelled, it shows correct one and the wrong one(s).
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Thank you very much, Jindon.

    I greatly appreciate all your assistance.

    Edwin

  10. #10
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Hello Jindon,

    I just realized that when the macro runs, the last name in the Excel Worksheet is replaced with ", vegetables". I am not sure why.

    Thank you,

    Edwin

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Quote Originally Posted by ATL10SPRO View Post
    I just realized that when the macro runs, the last name in the Excel Worksheet is replaced with ", vegetables".
    What do you mean "the last name in the Excel Worksheet"?

  12. #12
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Column B in the worksheet has the Last name of the user and the Name of the user is in column A. In the worksheet, the name is Mary and the Last name is Jones. Thank you.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    OK, I'm out at the moment, so I will look at it later.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    OK, found the problem...
    try change to below and test it hard...
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Thank you very much, Jindon.

    It works perfectly. I just wish I could have all correctly and incorrectly spelled words listed in the dictionary file under just one header as in the file attached: Dictionary-File-for-Parser-WISH.txt

    As per your instructions for the code to work, I am using the dictionary file formatted as in the attached file: Dictionary-File-for-Parser-WORKS.txt

    Thank you again for all your help. It is very much appreciated.

    Edwin
    Attached Files Attached Files

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Only possible if you add a special mark in front of each correct word.
    I used a pipe,
    eg

    FRUITS
    |Apple
    APPLE
    Aaple
    Aple
    Applee
    |Banana
    BANANA
    Bannana
    Bannanas
    Bannanna
    BAnNana
    bAnANA

    VEGETABLES
    |Corn
    CORN
    CoorN
    CoRn
    |Peas
    PEas
    PEAS
    peAs
    peaS
    |Zuchini
    zucchinni
    zucchini
    zuucchini


    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Thank you very much Jindon. I will give it a try.

    What happens if the pipe is only placed in front of the correctly spelled word in the dictionary (|Apple) but the word is mispelled in column C "Event Description" in the spreadsheet? or the word is spelled with a different case (APPLE) in column C "Event Description" in the spreadsheet that does not match (|Apple) in the dictionary file, will the code still work?

    Thank you again for all your help.

    Edwin

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Run the code first and report back with the details if you find something that is not correct.

    If you do, upload a workbook with more practical sample data and the exact result that you want.

  19. #19
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    Hello Jindon,

    The spreadsheet attached (Excel-Parser-Example-test.xls) contains the words "aple", "zucchini" and "Zucchinni" in red bold text in cell C3 under the column “Event Description”. The word “aple” is misspelled. When the latest version of the macro runs together with the new format in the dictionary file (Dictionary-File-for-Parser-v1.0.txt) using a pipe (|) in front of the word, you will see that cell D3 under column “FRUITS” and cell D4 under column “VEGETABLES” is populated with: “Apple, aple” and “Zuchini, zucchini, Zucchinni” but there are no “Apple” or “Zuchini” in the cell C3, “Event Description”.

    I want to see in cells D3 (“FRUITS”) and cell D4 (“VEGETABLES”) only the words in cell C3 (“Event Description”) that exactly match the word or words in the dictionary file listed under the same category “FRUITS”, “VEGETABLES”, “PRODUCE”, “CARB SIMPLE” and “CARB COMPLEX”.

    Thank you,

    Edwin
    Attached Files Attached Files

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    If you just want the word(s) that appear in the list without correct word then "Dictionary-File-for-Parser-WISH.txt" is fine.
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    10-08-2020
    Location
    Atlanta
    MS-Off Ver
    16
    Posts
    12

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    That is it. PERFECT!

    Thank you very much, Jindon.

    Edwin

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,088

    Re: Parser to Find Words in a Cell and Copy the Word(s) to pre-defined Category(cies)

    You are welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] Find the words from my word list in a text cell
    By gozparlak in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-19-2017, 01:53 PM
  2. [SOLVED] Find whole word in a cell from a list of words
    By SperoTsindos in forum Excel General
    Replies: 10
    Last Post: 04-21-2017, 12:28 AM
  3. [SOLVED] Formula - If find 3 possible words within a cell, return the word found?
    By nobodyukno in forum Excel General
    Replies: 4
    Last Post: 03-17-2017, 01:30 PM
  4. Replies: 5
    Last Post: 01-14-2016, 12:04 AM
  5. [SOLVED] how to find defined word then record value of cell one row down
    By semajjames in forum Excel General
    Replies: 7
    Last Post: 04-11-2015, 08:01 PM
  6. [SOLVED] looking to find 1 of 2 words in a cell in column B and return the word found in the same
    By Rob69mfj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-27-2014, 01:17 PM
  7. [SOLVED] Find and return a word, in a list of words, that is in a single cell.
    By nicholas.jacka in forum Excel General
    Replies: 2
    Last Post: 03-07-2012, 05:13 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