Forum Discussion
Solved
Recreating a Twitter Search URL in Excel
Hi experts!
I'm an Excel NOOB and have hit a brick wall here - hope somebody can help:
I have a list in Excel with people on Twitter who are important to my company. I have all kinds of data on them, so I can filter them in numerous ways - one of them being their Twitter handle.
Now I want to be able to recreate the Twitter Advanced Search function from within Excel, so I can make a search URL based on some of these filters and see tweets from the people in the filter (by getting a clickable link in Excel)
Here's an example of how the Twitter search URL is structured if you search for a search term and a hashtag from 3 specific Twitter accounts:
https://twitter.com/search?l=&q=SEARCH_TERM%20%23HASHTAG%20from%3ATWITTER-PROFILE%20OR%20from%3ATWITTER-PROFILE%20OR%20from%3ATWITTER-PROFILE&src=typd
Or with real data:
So how do I create a formula where I get the right URL for the Twitter search? Of course it needs to take any filters into consideration (like only showing the ones with "social" in the "Affiliation" collumn - see attached screen shot)
I hope there's an Excel Ninja out there who can helt me solve this.
Hi Niels,
The most complex part of your task is to combine <from> part of the search string - we have to combine Twitter handle column in one string based on criteria are columns filtered or not.
Afraid the easiest way is to use VBA. I took one of samples found on Web https://www.mrexcel.com/forum/excel-questions/637445-concatenate-text-visible-cells.html
(Press Alt+F11 to open VBA environmen, copy/paste function code in new module)
Public Function ConcatenateVisible(rng As Variant, seperator As String) For Each cll In rng If cll.EntireRow.Hidden = False Then _ ConcatenateVisible = ConcatenateVisible & cll.Value & seperator Next ConcatenateVisible = Left(ConcatenateVisible, Len(ConcatenateVisible) - Len(seperator)) End FunctionOn your table above function
=ConcatenateVisible(C2:C5," OR from:")
returns result like
frankjensenkbh OR from:idaauken OR from:uffeelbek
After that the all what we need is to combine site URL and query string on it, e.g.
https://twitter.com/search?q= to combine with Radikale %23dkpol from:idaauken OR from:nielstybjerg OR from:loveyourleads
All parts for that we have now, resulting string use in HYPERLINK function
=HYPERLINK("https://twitter.com/search?q=" & F2 & " %23" & F3 & " from:"&ConcatenateVisible(C2:C5," OR from:"), "Search Twitter")which returns needed hyperlink.
File is attached - it is in macro-enabled format
Definitely use the concatenate function or the & sign.
You can do something like this:
=URLpart1&$F$2&URLpart2&$F$3&URLpart3&C2&URLpart4
With the concatenate functions it would be like this:
=concatenate(URLpart1,$F$2,URLpart2,$F$3,URLpart3,C2,URLpart4)
As far as filtering goes, it would be easy to fill in the first formula, drag the fill handle down to apply that formula to all rows, then filter as needed.Zachary, latest part concatenates all invisible cells from column C, it looks like
" from:" & C2 & " OR from:" & C4 & " OR from:" & C5
assuming rows 2, 4 and 5 are visible.
If only row 4 is visible the part shall be
" from:" & C4
and so on
- Thanks for the comments, Sergei. I included absolute references and relative references based on the image included in Niels' example. Not sure what you mean by " from:" Can you please explain further?
Hi Niels,
The most complex part of your task is to combine <from> part of the search string - we have to combine Twitter handle column in one string based on criteria are columns filtered or not.
Afraid the easiest way is to use VBA. I took one of samples found on Web https://www.mrexcel.com/forum/excel-questions/637445-concatenate-text-visible-cells.html
(Press Alt+F11 to open VBA environmen, copy/paste function code in new module)
Public Function ConcatenateVisible(rng As Variant, seperator As String) For Each cll In rng If cll.EntireRow.Hidden = False Then _ ConcatenateVisible = ConcatenateVisible & cll.Value & seperator Next ConcatenateVisible = Left(ConcatenateVisible, Len(ConcatenateVisible) - Len(seperator)) End FunctionOn your table above function
=ConcatenateVisible(C2:C5," OR from:")
returns result like
frankjensenkbh OR from:idaauken OR from:uffeelbek
After that the all what we need is to combine site URL and query string on it, e.g.
https://twitter.com/search?q= to combine with Radikale %23dkpol from:idaauken OR from:nielstybjerg OR from:loveyourleads
All parts for that we have now, resulting string use in HYPERLINK function
=HYPERLINK("https://twitter.com/search?q=" & F2 & " %23" & F3 & " from:"&ConcatenateVisible(C2:C5," OR from:"), "Search Twitter")which returns needed hyperlink.
File is attached - it is in macro-enabled format
Hi Sergei
You helped me long time ago with this, and it's been working perfectly with a small list. But when you go beyond 10 people on the list, the formula is broken...
Any chance I could get you to take a look at it? It's when I change the formula from =HYPERLINK("https://twitter.com/search?q=" & F150 & " %23" & F151 & " from:"&ConcatenateVisible(C2:C10;" OR from:"); "Search Twitter") to C2:C11 or higher...
Hi Niels,
I see. That's not an error in formula, HYPERLINK() function works with strings which have not more than 255 characters. As soon as formula generates more, the HYPERLINK returns error.
Okay, i'll try to find the workaround. Remember that issue discussed.
Don't understand half of what you did there, but the attached file seems to be working perfectly! Thank you so much - you are a golden god! :-)
