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 #
- 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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…