+ Reply to Thread
Results 1 to 5 of 5

Help needed with VBA code to extract Phrases/ords from text string

  1. #1
    Forum Contributor
    Join Date
    06-17-2014
    Location
    london
    MS-Off Ver
    365
    Posts
    392

    Help needed with VBA code to extract Phrases/ords from text string

    Dear All
    I was wondering whether anyone can help e with wrting a VBA code to do the follwoing:

    I have attached a simple spreadsheet to demonstrate what I need to do if at all possible.

    I the attached file I have list of Departments and comments which were made by customers who visited those departments (Columns A:B)
    In Column J I have added some words or phrases which I need to search for in Col B.

    What I need to do is to write a code to look for the words or phrases in Column J and extracts those words/phrases from Column B. I then need to create a pivot table which will give eme a count of those words for each department. For example the word 'rude' appears twice in Col B for department CCC and word 'pleased' appear twice for Dept AAA and ' very unpleasent' appears once for once DDD and so on. so I wii have a pivot table look slike

    AAA pleased 1
    CCC Rude 2
    DDD Very unpleasent 1

    Over time I will add to the list of words/phrases in Col J.

    In my actual spreadsheet I have created a Pivot report from Pivot table which list the Comments per depart,emt (I have over 20000 rows).

    Is this something that easily can be done as I am very novice with VBA.

    If anyone can help me I will apprecaite it as this will really help me in my current work.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Help needed with VBA code to extract Phrases/ords from text string

    I assume a few things. Like putting data that is found in columns D and E, Dept and Comments. I did noticed that in Cell B4 the word Pleased is spelled Please so it didn't show up in the count. I use UCASE to make it not care about case. I generated a pivot table for you also....HTH....


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Contributor
    Join Date
    06-17-2014
    Location
    london
    MS-Off Ver
    365
    Posts
    392

    Re: Help needed with VBA code to extract Phrases/ords from text string

    Dear Judgeh59

    Thanks so much for this. This is exactly what I need however I am confused with the data in Col D & E. Can the code just look at the A & B and see if the words in Col J are found. Not sure abut you assumption for D & E. In the actual spreasdsheets the comments and Dept name are submitted by customers and I need to look at key phrases within those comments if this makes sense.

    Thanks again

  4. #4
    Forum Contributor
    Join Date
    06-17-2014
    Location
    london
    MS-Off Ver
    365
    Posts
    392

    Re: Help needed with VBA code to extract Phrases/ords from text string

    I just went through your code again and relaised how its done.

    This is fantastic and has saved me so much time. Thank you so much for this as it does exactly what I need.

  5. #5
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Help needed with VBA code to extract Phrases/ords from text string

    no problem....glad i could help and thanks for the points.....have a great day...

+ 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] VBA code required to Extract the content between 2 words/Phrases
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-13-2015, 01:20 AM
  2. VBA code needed: Posting three most appearing text string in a culomn
    By ehmail84 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-20-2014, 05:52 AM
  3. Help Needed: Extract text string using very specific criteria
    By Sajo90 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-18-2014, 09:19 PM
  4. Extract code from text string
    By dionysusftw in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2014, 02:30 PM
  5. Code to Extract certain text from a string
    By bcorm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2013, 01:57 PM
  6. [SOLVED] How to Extract A Number from String if Text within the String Equals XYZ
    By tyrsfury in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2012, 03:30 PM
  7. Extract small string of text from larger string
    By mark_jam3s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2010, 05:36 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