+ Reply to Thread
Results 1 to 12 of 12

Formula to pull #word from a long text string

  1. #1
    Registered User
    Join Date
    11-28-2014
    Location
    Massachusetts
    MS-Off Ver
    2014
    Posts
    4

    Formula to pull #word from a long text string

    Hi, I am trying to do some analysis around Twitter usage. I have a big data set which shows the actual tweet text strings for the last 2 months by a group of users. I am trying to pull just the words that are hash tagged.

    For example this is a tweet:

    "I love my new product. Thank you #BestPresent for the upgrade."

    I was to be able to pull just the #BestPresent from this text string into a new column. Please note there may be several different #words within a text string and I Want to be able to pull all of these.



    Thank you for your help!!

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Formula to pull #word from a long text string

    How about:

    Please Login or Register  to view this content.
    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formula to pull #word from a long text string

    =mid(a1,find("#",a1),len(a1)-find(" ",a1,find("#",a1)))
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  4. #4
    Registered User
    Join Date
    11-28-2014
    Location
    Massachusetts
    MS-Off Ver
    2014
    Posts
    4

    Re: Formula to pull #word from a long text string

    ConneXionLost - thank you for your reply. That worked for the first #word in the string but not all.

    Daffodil11 - this works perfectly! Thank you very much for your help!!

  5. #5
    Registered User
    Join Date
    11-28-2014
    Location
    Massachusetts
    MS-Off Ver
    2014
    Posts
    4

    Re: Formula to pull #word from a long text string

    Hi - Daffodil11 I noticed this does not work exactly. This is giving me everything after the #word. I am looking to get only the #words within the string.

    Your suggestion for this example:

    Looking for a campaign template? A full #mktg plan? Our new site has all the #tools & services you need

    Returns:

    #mktg plan? Our new site has all the #tools & services you need

    However, my desired result would be: #mktg #tools

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formula to pull #word from a long text string

    Derp!

    Sorry, I cut the reference from another workbook. I didn't have enough sugar yet!

    =MID(A1,FIND("#",A1),FIND(" ",A1,FIND("#",A1))-FIND("#",A1))

    Let me think for a moment of another way to pull the 2nd reference. Do you want all #words in the same cell, or one per column?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Formula to pull #word from a long text string

    How many #words can there be? Is it (effectively) limitless?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formula to pull #word from a long text string

    I found an adorable little UDF in a few different places on the web that can do it.

    Hit Alt+F11 to go to VB editor. Go to Insert -> Module, and paste the code below.

    Go to Tools, References, Micosoft VB Regular Expression 5.5 thingy and turn it on. Close the window.

    UDF use: getHashTags(range). Use multicell array for multiple matches.

    So, if your cell is in A1 and you want to pull up to 5 hashtags, highlight B1:F1, put = getHashTags(A1) and then hit Ctrl+Shift+Enter.

    Drag down as far as needed.

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

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Formula to pull #word from a long text string

    Or a formula to pull the first 3... That said, I'd go with the code solution...
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-28-2014
    Location
    Massachusetts
    MS-Off Ver
    2014
    Posts
    4

    Re: Formula to pull #word from a long text string

    Hi daffodil11

    Thank you again for all of your help. I have followed your steps above and used the code. However, This is only pulling 3 #words. If I drag the =getHashtags to 5 columns it will only populate the first 3 and then give me an #N/A for the last 2. I am thinking it must be something in the code?

    Thanks again

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formula to pull #word from a long text string

    Yup. Just change those 3's to 10's.

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

  12. #12
    Registered User
    Join Date
    05-10-2013
    Location
    Pomáz, Hungary
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Formula to pull #word from a long text string

    Another version in B1 (then copy down and across as needed):

    Please Login or Register  to view this content.
    If the period and the comma are to be removed, this formula should be wrapped into Substitute functions.

+ 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. Advanced Split Function: Need to pull text string in quotes, within a text string
    By Zamboni in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-27-2013, 04:38 PM
  2. [SOLVED] Formula to pull first word out of string of text
    By kenadams378 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-24-2013, 05:24 AM
  3. Want excel formula/macro to pull external text string from WORD and display in excel
    By crnadeau4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 12:49 PM
  4. Need a formula to remove an email address from a long string of text.
    By Luke Andrews in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-02-2013, 08:03 PM
  5. what formula to extract text from a long string?
    By SEMMatt in forum Excel General
    Replies: 5
    Last Post: 10-07-2012, 10:23 PM

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