Count only words, ignoring special characters in cell

Question

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?

Answer 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)

Answer 2

Perhaps you can use this as well, using FILTERXML( ) Function:

---

• Formula used in cell B1

=COUNT(--(LEN(FILTERXML("<m><b>"&SUBSTITUTE(A1,"_","</b><b>")&"</b></m>","//b"))=3))

---

Or, using the Nodes in FILTERXML( )

---

• Formula used in cell B1

=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 FILTERXML( ) and its usages in Excel, a thorough analysis by JvdV Sir, can be found here.

---