如何从文本列中拉取标签? [英] How can I pull hashtags out of a text column?
问题描述
也许只有3个空白列称为标题1,2,3,并将它们拉入每列。
将它们从描述列中删除它并不是很重要。
描述示例:
#0034 #lost客户端丢失的文件 - 拉出0034并丢失
工作者质量差#SusanB #quality - 拉出SusanB和质量
#0840客户投诉 - 拉出0840
很多ipsum - 拉出没有
让我们说A列是Description列,在A2中你有第一个带有标签的单元格
在B2中输入:
= MID(A2;(FIND(#; A2))+ 1;(FIND(; MID(A2;(FIND(#; A2))+ 1; LEN(A2) (FIND(#; A2))))+(FIND(#; A2))) - (FIND(#; A2)) - 1)
/ pre>
在C2中输入:
= MID(A2; (FIND( #; MID(A2; (FIND(#; A2))+ 1; LEN(A2) - (FIND(#; A2))))+(FIND(#; A2)))+ 1;(FIND ; MID(A2;(FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( #; A2)))+ 1; LEN(A2) - (FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND(# ; A2))))+(FIND( # ; A2)))))+(FIND( # ; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( #; A2)))) - (FIND( #; MID(A2;(FIND( #; A2))+ 1 ; LEN(A2) - (FIND(#; A2))))+(FIND(#; A2))) - 1)
在D2中输入:
= MID(A2;(FIND ; MID(A2;(FIND( # ; MID(A2;(FIND( # ; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND ( #; A2)))+ 1; LEN(A2) - (FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( # ; A2))))+(FIND( # ; A2)))))+(FIND( # ; MID(A2;(FIND( #; A2))+ 1; LEN(A2 ) - (FIND(#; A2))))+(FIND(#; A2))))+ 1;(FIND(; MID ;(FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( #; A2)))+ 1; LEN(A2) - (FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2) )))+(FIND( #; A2)))))+(FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( #; A2) )))+ 1; LEN(A2) - (FIND( #; MID(A2;(FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( #; A2)))+ 1; LEN(A2) - (FIND( #; MID(A2;(FIND( # ; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( #; A2)))))+(FIND( #; MID(A2; (FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( #; A2))))))+(FIND( # ; MID(A2;(FIND( # ; MID(A2;(FIND( # ; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(查找( #; A2)))+ 1; LEN(A2) - (FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( #; A2)))))+(FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN( A2) - (FIND( #; A2))))+(FIND( #; A2))))) - (FIND( #; MID(A2;(FIND( #; MID( A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( #; A2)))+ 1; LEN(A2 ) - (FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+(FIND( # ; A2)))))+(FIND( #; MID(A2;(FIND( #; A2))+ 1; LEN(A2) - (FIND( #; A2))))+ (FIND(#; A2)))) - 1)
I have an Excel sheet in which there is a "description" column. The values in this column often contain anywhere from 0-3 tags, all starting with the # symbol. Is there a way to pull all of these tags out in to columns?
Perhaps just have 3 blank columns called hashtag 1, 2, 3 and pull them in to each column.
It isn't even important that it remove them from the description column while pulling them out.
Example of descriptions:
"#0034 #lost client lost file" - pull out 0034 and lost "worker has bad quality #SusanB #quality" - pull out SusanB and quality "#0840 client complaint" - pull out 0840 "lots of ipsum" - pull out nothing
解决方案Lets say Column A is Description column, and in A2 you have the first cell with hashtags
In B2 enter:=MID(A2;(FIND("#";A2))+1;(FIND(" ";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))-(FIND("#";A2))-1)
In C2 enter:
=MID(A2;(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))+1;(FIND(" ";MID(A2;(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))+1;LEN(A2)-(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))))+(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2))))-(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))-1)
In D2 enter:
=MID(A2;(FIND("#";MID(A2;(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))+1;LEN(A2)-(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))))+(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2))))+1;(FIND(" ";MID(A2;(FIND("#";MID(A2;(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))+1;LEN(A2)-(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))))+(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2))))+1;LEN(A2)-(FIND("#";MID(A2;(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))+1;LEN(A2)-(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))))+(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2))))))+(FIND("#";MID(A2;(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))+1;LEN(A2)-(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))))+(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))))-(FIND("#";MID(A2;(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))+1;LEN(A2)-(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2)))))+(FIND("#";MID(A2;(FIND("#";A2))+1;LEN(A2)-(FIND("#";A2))))+(FIND("#";A2))))-1)
这篇关于如何从文本列中拉取标签?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!