Fórmula genérica
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Resumen
Si necesita contar cuántas veces aparece una palabra específica (o cualquier subcadena) dentro de una celda, puede usar una fórmula que use SUBSTITUTE y LEN. En la forma genérica de la fórmula anterior, "texto" representa una celda que contiene texto y "palabra" representa la palabra o subcadena que se cuenta. En el ejemplo, usamos esta fórmula:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Explicación
B4 es la celda en la que estamos contando palabras y C4 contiene la subcadena (palabra o cualquier subcadena) que está contando.
SUBSTITUTE elimina la subcadena del texto original y LEN calcula la longitud del texto sin la subcadena. Luego, este número se resta de la longitud del texto original. El resultado es el número de caracteres eliminados por SUBSTITUTE.
Finalmente, la cantidad de caracteres eliminados se divide por la longitud de la subcadena. Por lo tanto, si una subcadena tiene 5 caracteres de longitud y faltan 10 caracteres después de que se eliminó del texto original, sabemos que la subcadena apareció dos veces en el texto original.
Maletín de manipulación
SUBSTITUTE es una función que distingue entre mayúsculas y minúsculas, por lo que coincidirá con las mayúsculas y minúsculas al ejecutar una sustitución. Si necesita contar las apariciones en mayúsculas y minúsculas de una palabra o subcadena, use la función MAYÚS dentro de SUBSTITUTO para convertir el texto a mayúsculas antes de ejecutar la sustitución:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Debido a que esta fórmula convierte la subcadena y el texto a mayúsculas antes de realizar la sustitución, funcionará igualmente bien con el texto en cualquier caso.
Manejo de problemas
Contar palabras en Excel es complicado porque Excel no admite expresiones regulares. Como resultado, es difícil apuntar a las palabras que desea contar exactamente, mientras ignora las subcadenas y otras coincidencias parciales (es decir, busque "fox" pero no "foxes"). La puntuación y las variaciones entre mayúsculas y minúsculas hacen que este problema sea bastante desafiante.
Una solución es utilizar otra fórmula en una columna auxiliar para "normalizar el texto" como primer paso. Luego, use la fórmula de esta página para contar las palabras envueltas en caracteres de espacio para obtener un recuento preciso (es decir, puede buscar "zorro" en el texto normalizado.
Nota: este enfoque solo es tan bueno como el texto normalizado que pueda crear, y es posible que deba ajustar la fórmula de normalización muchas veces para obtener el resultado que necesita.
