[Solved] Extract hashtags from a string in Excel

by


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.

enter image description here

solved Extract hashtags from a string in Excel