El tutorial explica cómo contar palabras en Excel usando la función LEN en combinación con otras funciones de Excel y proporciona fórmulas que distinguen entre mayúsculas y minúsculas para contar palabras/texto totales o específicos en una celda o rango.
Microsoft Excel tiene un puñado de funciones útiles que pueden contar casi cualquier cosa: la función COUNT para contar celdas con números, COUNTA para contar celdas que no están vacías, COUNTIF y COUNTIFS para contar celdas condicionalmente y LEN para calcular la longitud de una cadena de texto .
Desafortunadamente, Excel no ofrece ninguna herramienta integrada de conteo de palabras. Afortunadamente, al combinar funciones de servidor, puede crear fórmulas más complejas para realizar casi cualquier tarea. Y usaremos este enfoque para contar palabras en Excel.
Cómo contar el número total de palabras en una celda
Para contar las palabras en una celda, use la siguiente combinación de las funciones LEN, SUSTITUTE y TRIM:
LONG(RECORTAR(celúla))-LEN(REEMPLAZADO(celúla,» «,»»))+1
Dónde celúla es la dirección de la celda donde desea contar las palabras.
Por ejemplo, para contar las palabras en la celda A2, use esta fórmula:
=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1
Y luego, puede copiar la fórmula para contar las palabras en otras celdas de la columna A:
Cómo funciona esta fórmula de conteo de palabras
Primero, use la función SUSTITUIR para eliminar todos los espacios de la celda, reemplazándolos con una cadena de texto vacía («») para que la función LEN devuelva la longitud de la cadena sin espacios:
LEN(SUBSTITUTE(A2," ",""))
Después de eso, reste la longitud de la cadena sin espacios de la longitud total de la cadena y agregue 1 al número final de palabras, ya que el número de palabras en una celda es igual al número de espacios más 1.
Además, use la función RECORTAR para eliminar espacios adicionales de la celda, si corresponde. A veces, una hoja de trabajo puede contener muchos espacios invisibles, como dos o más espacios entre palabras o espacios ingresados accidentalmente al principio o al final del texto (es decir, espacios iniciales y finales). Y todos esos espacios adicionales pueden reducir el número de palabras. Para protegerse contra esto, antes de calcular la longitud total de la cadena, usamos la función TRIM para eliminar todos los espacios en exceso, excepto los espacios individuales entre palabras.
Fórmula mejorada que maneja adecuadamente las celdas vacías
La fórmula anterior para contar palabras en Excel podría llamarse perfecta si no fuera por un inconveniente: devuelve 1 para celdas vacías. Para solucionar esto, puede agregar una instrucción IF para verificar si hay celdas vacías:
=IF(A2="", 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)
Como puede ver en la captura de pantalla anterior, la fórmula devuelve cero para las celdas vacías y el número correcto de palabras para las celdas que no están vacías.
Cómo contar palabras específicas en una celda
Para contar la cantidad de veces que aparece una palabra, texto o subcadena en particular en una celda, use la siguiente fórmula:
=(LEN(cell)-LEN(SUBSTITUTE(cell, word,"")))/LEN(word)
Por ejemplo, calculemos el número de «mes» ocurrencias en la celda A2:
=(LEN(A2)-LEN(SUBSTITUTE(A2, "moon","")))/LEN("moon")
En lugar de ingresar la palabra que se contará directamente en la fórmula, puede ingresarla en una celda y hacer referencia a esa celda en su fórmula. Como resultado, obtendrá una fórmula más versátil para contar palabras en Excel.
Consejo. Si planea copiar la fórmula en varias celdas, asegúrese de establecer la referencia a la celda que contiene la palabra para contar con un signo $. P.ej:=(LEN(A2)-LEN(SUBSTITUTE(A2, $B$1,"")))/LEN($B$1)
Cómo esta fórmula cuenta las apariciones de un texto en particular en una celda
- La función SUSTITUIR elimina la palabra especificada del texto original.
En este ejemplo, eliminamos la palabra ingresada en la celda B1 del texto original ubicado en A2:SUBSTITUTE(A2, $B$1,"") - La función LEN luego calcula la longitud de la cadena de texto sin la palabra especificada.
En este ejemplo,LEN(SUBSTITUTE(A2, $B$1,""))devuelve la longitud del texto en la celda A2 después de eliminar todos los caracteres contenidos en todas las apariciones de la palabra «mes«. - Después de eso, el número anterior se resta de la longitud total de la cadena de texto original:
(LEN(A2)-LEN(SUBSTITUTE(A2, $B$1,"")))El resultado de esta operación es el número de caracteres contenidos en todas las apariciones de la palabra objetivo, que es 12 en este ejemplo (3 apariciones de la palabra «mes«, 4 caracteres cada uno).
- Finalmente, el número anterior se divide por la longitud de la palabra. En otras palabras, divida el número de caracteres contenidos en todas las apariciones de la palabra objetivo por el número de caracteres contenidos en una sola aparición de esa palabra. En este ejemplo, 12 se divide por 4 y obtenemos 3 como resultado.
Además de contar la cantidad de palabras específicas en una celda, puede usar esta fórmula para contar ocurrencias de cualquier texto (subcadena). Por ejemplo, puedes contar cuántas veces el texto «elegir» aparece en la celda A2:
Fórmula que distingue entre mayúsculas y minúsculas para contar palabras específicas en una celda
Como probablemente sepa, SUSTITUCIÓN de Excel es una función que distingue entre mayúsculas y minúsculas y, por lo tanto, la fórmula de recuento de palabras basada en SUSTITUCIÓN distingue entre mayúsculas y minúsculas de forma predeterminada:
Fórmula que no distingue entre mayúsculas y minúsculas para contar palabras específicas en una celda
Si necesita contar las apariciones en mayúsculas y minúsculas de una palabra determinada, use la función SUPERIOR o INFERIOR de SUSTITUIR para convertir el texto original y el texto que desea contar en la misma letra.
=(LARGO(celúla)-LEN(REEMPLAZADO(ARRIBA(celúla),SUPERIOR(texto)»))))/LARGO(texto)
O
=(LARGO(celúla)-LEN(REEMPLAZADO(EN CASO(celúla),MÁS BAJO(texto)»))))/LARGO(texto)
Por ejemplo, para contar el número de ocurrencias de la palabra en B1 en la celda A2 sin distinguir entre mayúsculas y minúsculas, use esta fórmula:
=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($B$1),"")))/LEN($B$1)
Como se muestra en la siguiente captura de pantalla, la fórmula devuelve la misma cantidad de palabras, ya sea que la palabra se ingrese en MAYÚSCULAS (celda B1), minúsculas (celda D1) o letras (celda C1):
Contar el número total de palabras en un rango
Para averiguar cuántas palabras contiene un intervalo dado, tome la fórmula que contar el número total de palabras en una celda e incrustarlo en la función SUMPRODUCT o SUM:
=CANTIDADPRODUCTO(LARGO(RECORTE(rango))-LEN(REEMPLAZADO(rango,» «,»»))+1)
O
=SUMA(TONTO(RECORTAR(rango))-LEN(REEMPLAZADO(rango,» «,»»))+1)
SUMPRODUCT es una de las pocas funciones de Excel que puede manejar matrices y completar la fórmula normalmente presionando la tecla Intro.
Para que la función SUMA calcule matrices, debe usarse en una fórmula de matriz, que se completa presionando Ctrl+Mayús+Intro en lugar del trazo Intro habitual.
Por ejemplo, para contar todas las palabras en el rango A2:A4, use una de las siguientes fórmulas:
=SUMPRODUCT(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1)
=SUM(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1)
Contar palabras específicas en un rango
Si desea contar cuántas veces aparece una palabra o texto en particular en un rango de celdas, use un enfoque similar: lleve la fórmula a contar ciertas palabras en una celday combínalo con la función SUM o SUMPRODUS:
=CANTIDAD DEL PRODUCTO((LARGO(rango)-LEN(REEMPLAZADO(rango, palabra,»»)))/LARGO(palabra))
O
=SUMA((LARGO(rango)-LEN(REEMPLAZADO(rango, palabra,»»)))/LARGO(palabra))
Por favor, no olvide presionar Ctrl+Mayús+Intro para completar correctamente la fórmula de la matriz SUM.
Por ejemplo, para contar todas las ocurrencias de la palabra ingresada en la celda C1 en el rango A2:A4, use esta fórmula:
=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4, C1,"")))/LEN(C1))
Como recordará, REEMPLAZADO es un distingue mayúsculas y minúsculas y, por lo tanto, la fórmula anterior distingue entre texto en mayúsculas y minúsculas:
Para hacer la fórmula mayúsculas y minúsculasutilice la función ARRIBA o ABAJO:
=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE((UPPER(A2:A4)),UPPER(C1),"")))/LEN(C1))
O
=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE((LOWER(A2:A4)),LOWER(C1),"")))/LEN(C1))
Así es como se cuentan las palabras en Excel. Para comprender mejor y posiblemente aplicar ingeniería inversa a las fórmulas, le invitamos a descargar ejemplo de libro de trabajo Excel Count Words.
Si ninguna de las fórmulas discutidas en este tutorial resolvió su problema, consulte la siguiente lista de recursos que muestran otras soluciones para contar celdas, texto y caracteres individuales en Excel.
