在单元格中计数单词,不要计数HTML标签 [英] Count words in cell, don't count HTML tags
问题描述
我在Excel单元格中有HTML代码。
如何计数不在
>
或者,如何计算>
和
我想得到一些字, t HTML标签,类,属性等我有一个A1代码,如:
< div class =xstyle =padding:0px;> Lorem< strong> ipsum< / strong> < I> BLA< I> /; < a href =#title =xmixtarget =_ blank> opana< / a>测试< span>字< / span> BLA< / DIV>
在B1中计算后,我想得到7,作为一些干净的单词 Lorem ipsum bla opana test word bla
我喜欢一个挑战,所以这是一个公式解决问题。这个版本只接受<>和空格作为分隔符:这是很难处理逗号,全停等等。
算法是
如果当前字符为<将所有字符提取到下一个>
如果当前字符为,则将所有字符提取到下一个非空格
如果当前字符是任何其他字符,请将所有字符提取到下一个空格或
然后计算以字母开头的字符串数。
公式是
= IF(LEFT(MID($ A $ 1,SUMPRODUCT(LEN($ C $ 1:C1))+ 1,LEN($ A $ 1)),1)=<,
MID($ A $ 1,SUMPRODUCT(LEN($ C $ 1:C1))+ 1,LEN($ A $ 1)),1,FIND(>,MID($ A $ 1,SUMPRODUCT $($ A $ 1,SUMPRODUCT(LEN($ C $ 1:C1))$ 1,LEN($ A $ 1))&>)),
IF(LEFT )+ 1,LEN($ A $ 1)),1)=,
MID($ A $ 1,SUMPRODUCT(LEN($ C $ 1:C1))+ 1,LEN($ A $ 1) ),1,FIND(LEFT(TRIM(MID($ A $ 1,SUMPRODUCT(LEN($ C $ 1:C1))+ 1,LEN($ A $ 1)))),MID($ A $ 1,SUMPRODUCT(LEN( $ C $ 1:C1))+ 2
,LEN($ A $ 1))),
MID($ A $ 1,SUMPRODUCT(LEN($ C $ 1:C1))+ 1,LEN($ A $ 1)),1,AGGREGATE(15,6,SEARCH({, },MID($ A $ 1,SUMPRODUCT(LEN($ C $ 1:C1))+ 2,LEN($ A $ 1))&),1)))))
,并得到计数只是
= COUNTIF(C2:C100,> = a)
第二个帮助列(列D)不是必需的,但作为支票包含在内。 >
I have HTML code in an Excel cell.
How to count all words which aren't between <
and >
Alternatively, how can I count all words between >
and <
I want to get a number of words, which aren't HTML tags, classes, attributes etc. I Have i.e. in A1 code like:
<div class="x" style="padding:0px;">Lorem <strong>ipsum</strong> <i>bla</i> <a href="#" title="xmix" target="_blank">opana</a> test <span>word</span> bla</div>
After calculation in B1 i want to get 7, as a number of clean words Lorem ipsum bla opana test word bla
Well I like a challenge so this is a formula solution to the problem. This version only accepts <, > and space as delimiters: it would not be too difficult having got this far to deal with commas, full stops etc.
The algorithm is
If current character is <, extract all characters up to next >
If current character is " ", extract all characters up to next non-space
If current character is any other character, extract all characters up to next space or <.
Then count number of strings starting with an alpha character.
The formula is
=IF(LEFT(MID($A$1,SUMPRODUCT(LEN($C$1:C1))+1,LEN($A$1)),1)="<",
MID(MID($A$1,SUMPRODUCT(LEN($C$1:C1))+1,LEN($A$1)),1,FIND(">",MID($A$1,SUMPRODUCT(LEN($C$1:C1))+1,LEN($A$1))&">")),
IF(LEFT(MID($A$1,SUMPRODUCT(LEN($C$1:C1))+1,LEN($A$1)),1)=" ",
MID(MID($A$1,SUMPRODUCT(LEN($C$1:C1))+1,LEN($A$1)),1,FIND(LEFT(TRIM(MID($A$1,SUMPRODUCT(LEN($C$1:C1))+1,LEN($A$1)))),MID($A$1,SUMPRODUCT(LEN($C$1:C1))+2
,LEN($A$1)))), MID(MID($A$1,SUMPRODUCT(LEN($C$1:C1))+1,LEN($A$1)),1,AGGREGATE(15,6,SEARCH({" ","<"},MID($A$1,SUMPRODUCT(LEN($C$1:C1))+2,LEN($A$1))&" "),1))))
and to get the counts just
=COUNTIF(C2:C100,">=a")
The second helper column (column D) is not necessary but is included as a check.
这篇关于在单元格中计数单词,不要计数HTML标签的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!