+ 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,

  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... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    35,817

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

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



  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... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    35,817

    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