CONTAR NÚMERO DE CARACTERES EN UN RANGO UTILIZANDO FÓRMULAS

Hola a todos!

En el post de hoy voy a trabajar con fórmulas, no siempre vamos a utilizar macros cuando la solución la podemos hallar mediante funciones nativas de Excel.

Un lector me preguntaba ayer si podía ofrecerle una fórmula que contase el número total de caracteres contenidos en un rango de celdas. Aunque podéis pensar que una sencilla solución es usar la función LARGO aplicada a cada celda y luego sumar el total, la respuesta se puede lograr usando una única fórmula (pero matricialmente).

Pero para ilustrar el ejemplo, imaginad que queremos obtener el total de caracteres que contiene el siguiente fragmento de archivo XML obtenido como respuesta a una consulta realizada a Google Maps:

CONTAR NUMERO DE CARACTERES EN UN RANGO UTILIZANDO FORMULAS

Para obtener el total de caracteres de toda la columna A utilizaremos la siguiente fórmula:

{=SUMA(LARGO(A:A))}

El resultado obtenido va a tener en cuenta cualquier caracter, incluidos los espacios en blanco, las comas, los puntos, etc. En concreto, el resultado es de 1720 caracteres.

Con esto el lector ya tiene la respuesta a su consulta, sin embargo, imaginad que lo que necesita es conocer solo el número de caracteres alfanuméricos. Es decir sin tener en cuenta los espacios, puntos, comas …

Para poder realizar el mismo ejercicio, necesitamos incluir la función SUSTITUIR en nuestra función inicial y además anidando los elementos que queremos excluir en la cuenta final:

{=SUMA(LARGO(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(A:A;">";"");"<";"");"/";"");" ";"");"_";"");".";"")))}

Aplicando esta fórmula el resultado ahora es de 1176 caracteres, como podéis observar el número se ha reducido significativamente.

CONTAR NUMERO DE CARACTERES EN UN RANGO UTILIZANDO FORMULAS1

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Espero que os resulte de utilidad!

Descarga el archivo de ejemplo pulsando en: CONTAR NUMERO DE CARACTERES EN UN RANGO UTILIZANDO FORMULAS

 

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Anuncios

COMBINAR LA FUNCIÓN HALLAR Y LA FUNCIÓN LARGO

En esta entrada he querido combinar diferentes fórmulas para mostrar el uso combinado de la función “Hallar” y la función “Largo”. Estas funciones no las suelo utilizar mucho, pero he de reconocer que son de vital importancia en según que ocasión nos encontremos. Por ejemplo, hoy voy a realizar el mismo trabajo que en su momento ilustré con la entrada: SEPARAR NOMBRE Y APELLIDOS EN EXCEL. HERRAMIENTA TEXTO EN COLUMNAS, y para ello voy a necesitar urgentemente un listado de personas con nombre y apellidos para realizar el ejemplo. Como no los voy a escribir de uno en uno, voy a bajar la información del INE, ahí podéis encontrar información de nombres de niñas y niños, y una relación de hasta 25884 apellidos.

Para realizar este ejemplo voy a echar mano de la función “aleatorio ()”, así si alguno de vosotros aún trabaja en excel 2003 podrá ver cómo funciona la fórmula (iba a utilizar la función “aleatorio.entre”, pero ya la mostré en la entrada de la LOTERIA y solo funciona de 2007 en adelante).

Lo primero que voy a hacer es confeccionar varias columnas, en una pondré los 200 nombres (hombres y mujeres) y al lado los numeraré para otorgarles un ID, en la otra columna pondré los 25.884 apellidos y también los numeraré con un ID, así:

FUNCION HALLAR&LARGO_1

El siguiente paso será generar aleatoriamente con estos datos, nombres y apellidos para poder realizar el ejemplo. Para hacerlo debemos introducir la fórmula aleatorio(), esta función nos va a dar un número mayor o igual a 0 y menor que 1, para poder trabajar con este resultado debemos multiplicar el decimal por tantos registros como tenga el listado, es decir por 200 en un caso y por 25.884 por otro y luego redondear para eliminar los decimales. Así vamos crear nuestras tres columnas con nombre, primer apellido y segundo apellido, os dejo los pasos con el ejemplo del nombre y las fórmulas utilizadas, donde la última será un “buscarv” desde el ID volátil que acabamos de crear al ID fijo del principio:

FUNCION HALLAR&LARGO_2

Una vez que repitamos el mismo proceso para los apellidos habremos confeccionado nuestro propio generador de nombres y apellidos, y que además de servirnos para el ejemplo de hoy, nos será útil para crear bases de datos en las que trabajar  🙂

La información resultante será la siguiente, en azul está el nuevo nombre y apellidos que se generan de forma aleatoria (cada vez que interactuamos con los datos del libro los datos se actualizan).

FUNCION HALLAR&LARGO_3

Estos datos están en la hoja2 de nuestro libro. Para poder realizar el ejemplo los voy a llevar a la Hoja1 y lo haré con la función “concatenar” construyendo el nombre con la siguiente sintaxis:
=CONCATENAR(Hoja2!M2;" ";Hoja2!Q2;", ";Hoja2!I2) es decir: concatenar (primer apellido, segundo apellido, ", ", nombre)

Y así tendré mis 200 nombres para poder trabajar con ellos. En la siguiente imagen podéis ver el resultado de fórmula concatenar y en azul el resultado de la combinación de “hallar” con “largo” para extraer el nombre y apellidos.

FUNCION HALLAR&LARGO_4

Para extraer el nombre vamos a usar:
=EXTRAE(A2;HALLAR(",";A2)+1;LARGO(A2)) donde vamos a utilizar la función hallar para encontrar la longitud desde el inicio del nombre (incluyendo espacios vacíos) hasta la coma, luego utilizaremos la función largo para indicar la longitud total del nombre.

Finalmente con la función extraer, indicaremos que el nombre de A2, donde la posición inicial para extraer es HALLAR(",";A2)+1 (aquí debemos siempre sumar 1 para que no muestre la coma) y el número de caracteres que queremos extraer lo obtendremos con la función largo LARGO(A2) que nos indicará punto final de referencia a extraer.

Para extraer los apellidos vamos a usar:
=EXTRAE(A2;1;HALLAR(",";A2)-1) aquí no tendremos que usar la función largo () dado que ya sabemos donde se encuentra el final de los elementos a extraer, es decir, la longitud hasta la "," menos 1 carácter.

Como consejo final, os recomiendo que cuando trabajéis con este tipo de fórmulas, en el resultado final siempre utilicéis la función Espacios(), dado que evitará que queden espacios vacíos antes de comenzar el texto y al final, dejando solo los intermedios. En resumen, las fórmulas completas serían estas:

=ESPACIOS(EXTRAE(A2;HALLAR(",";A2)+1;LARGO(A2))) Extraer Nombre
=ESPACIOS(EXTRAE(A2;1;HALLAR(",";A2)-1)) Extraer Apellidos

FUNCION HALLAR&LARGO_5

Pues esto ha sido todo por hoy (que no es poco), hemos aprendido a generar una base de datos aleatoria de nombres y apellidos, a combinar las funciones “extraer”, “espacios”, “hallar” y “largo” y sobre todo, pasar un rato interesante.

Descarga el archivo de ejemplo pulsando en: COMBINAR FUNCION HALLAR & LARGO