英文:
Count only words, ignoring special characters in cell
问题
我有一个包含星期几的缩写和下划线的表格，它们由空格分隔，就像这样：
星期一 ___ 星期三 ___ ___ ___
尝试了一些公式，比如=LEN(A1)-NÚM.CARAT(SUBSTITUTE(A1;" ";""))+1，但不能只返回星期几。
我只需要计算这些天，忽略下划线和空格。在Excel 2016中，是否可以仅使用公式来实现这一点？
英文:
I have a table with columns contents some days of week abbreviate and underscores, separated by spaces, like this:
Mon ___ Wed ___ ___ ___
Tried some formulas like =LEN(A1)-NÚM.CARAT(SUBSTITUTE(A1;" ";""))+1, but doesn't returns only days.
I need count only this days, ignoring underscores and spaces. Is this possible using only formulas in excel 2016?
答案1
得分: 2
你可以通过将下划线替换为空格，然后使用trim去除重复空格，然后使用你已经有的相同逻辑来实现这个功能：
=IF(A1="",
0,
LEN(TRIM(SUBSTITUTE(A1,"_"," ")))
-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,"_"," "))," ",""))+1)
英文:
You could do this by replacing (substituting) the underscores with spaces and then use trim to remove duplicate spaces and then use the same logic you already had:
=IF(A1="",
0,
LEN(TRIM(SUBSTITUTE(A1,"_"," ")))
-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,"_"," "))," ",""))+1)
答案2
得分: 2
也许你也可以使用这个方法，使用<kbd>FILTERXML( )</kbd>函数：
• 在单元格<kbd>B1</kbd>中使用的公式：
=COUNT(--(LEN(FILTERXML("<m><b>"&SUBSTITUTE(A1,"_","</b><b>")&"</b></m>","//b"))=3))
或者，使用<kbd>FILTERXML( )</kbd>中的<kbd>Nodes</kbd>：
• 在单元格<kbd>B1</kbd>中使用的公式：
=COUNTA(FILTERXML("<m><b>"&SUBSTITUTE(A1,"_","</b><b>")&"</b></m>","//b[count(node())>0]"))
或者，
=SUM(--(FILTERXML("<m><b>"&SUBSTITUTE(A1,"_","</b><b>")&"</b></m>","//b[node()]")<>""))
要了解更多关于<kbd>FILTERXML( )</kbd>及其在Excel中的用法，请参阅**JvdV**先生的详细分析，可以在这里找到。
英文:
Perhaps you can use this as well, using <kbd>FILTERXML( )</kbd> Function:
• Formula used in cell <kbd>B1</kbd>
=COUNT(--(LEN(FILTERXML("<m><b>"&SUBSTITUTE(A1,"_","</b><b>")&"</b></m>","//b"))=3))
Or, using the <kbd>Nodes</kbd> in <kbd>FILTERXML( )</kbd>
• Formula used in cell <kbd>B1</kbd>
=COUNTA(FILTERXML("<m><b>"&SUBSTITUTE(A1,"_","</b><b>")&"</b></m>","//b[count(node())>0]"))
Or,
=SUM(--(FILTERXML("<m><b>"&SUBSTITUTE(A1,"_","</b><b>")&"</b></m>","//b[node()]")<>""))
To learn more on <kbd>FILTERXML( )</kbd> and its usages in Excel, a thorough analysis by JvdV Sir, can be found here.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论