Extract hashtags from a string in Excel

Question

How can I extract multiple hashtags from a string in Excel? I played around with MID and SEARCH formulas but couldn't come up with anything good.

**Example input:**
Daniel Craig primed for action on SKYFALL (2012) and SPECTRE (2015). #007 #JamesBond #DanielCraig

**Example output:**
#007
#JamesBond
#DanielCraig

Answer 1

If you have Excel 2013+ with the FILTERXML function you can:

• convert the string into an XML, using the spaces for the different nodes
  • "<t><s>" & SUBSTITUTE(A$1," ","</s><s>") & "</s></t>"
• use an Xpath to extract the nodes containing the #
  • "//s[contains(.,'#')]
• in the formula, [" & ROWS($1:1) & "]") becomes a position argument in the xpath so it will sequentially return the first, second, ...nth node that matches the condition.
• The IFERROR is to blank out the result if you fill down more than there are hashtags.

---

=IFERROR(FILTERXML("<t><s>" & SUBSTITUTE(A$1," ","</s><s>") & "</s></t>","//s[contains(.,'#')][" & ROWS($1:1) & "]"),"")

In the example, I placed the formula in A3 and filled down five rows.