Dividir texto en palabras

En ocasiones anteriores aprendimos algunas técnicas introductorias para separar texto en palabras. El objetivo de esas notas era aprender a utilizar las funciones utilizadas, no tanto el dividir textos en palabras. Cuando lo único que nos interesa es hacer dicha división, sin necesidad de fórmulas, podemos utilizar el Asistente para convertir texto en columnas de Excel.

Los pasos son los siguientes:

Seleccionamos la celda o la columna con el texto que queremos dividir y vamos a Datos - Texto en columnas. Con esto se abrirá el referido Asistente, en el Paso 1 de 3.

Aquí, tendremos que especificar uno de dos tipos de datos que manejaremos: Delimitados o De ancho fijo. Son delimitados cuando tenemos claramente identificado el separador que utilizaremos para hacer la división. En nuestro caso (dividir en palabras), los datos son delimitados, ya que usaremos un separador específico: el espacio. Otros separadores pueden ser: el caracter de tabulación, punto y coma, coma, o cualquier otro que esté claramente definido. El segundo tipo de datos que podemos usar es De ancho fijo. En este tipo no hay separador, por lo que el criterio a usar será un número fijo de caracteres por columna. Especificar este tipo es útil cuando queremos separar por ejemplo, una serie de claves de producto, como la siguiente:

abc123wxyz1

en sus distintas subclaves, siendo la primera los primeros tres caracteres, la segunda los caracteres 4 al 6, la tercera los caracteres 7 al 10 y la cuarta el caracter 11.

Como ya hemos seleccionado Delimitados, damos clic en Siguiente para ir al Paso 2 de 3.


En este, tendremos que especificar nuestro separador. En el caso nuestro, seleccionamos Espacio. Podemos especificar también si queremos que los separadores consecutivos sean considerados como uno solo. Damos clic en siguiente.

Finalmente, en el último paso podemos establecer el formato de los datos columna por columna y la celda de destino. Si hay alguna columna que no deseamos que sea mostrada, la seleccionamos y seleccionamos No importar columna (saltar). Damos clic en Finalizar.


En general es más práctico utilizar este método que hacer la división con fórmulas. Pero para divisiones de precisión quirúrgica, las fórmulas son más exactas y controlables.

Ctrl + C Reloaded

Todo mundo sabe para que sirven Ctrl + C, Ctrl + X y Ctrl + V. Son los métodos abreviados para copiar, cortar y pegar respectivamente. Estos atajos están optimizados para ser usados con la mano izquierda. Esto es así porque, en su infinita sabiduría, los programadores de Microsoft dedujeron que a la mayoría de la gente, que es diestra, le fascina utilizar la mano izquierda.

Afortunadamente también hay métodos abreviados para copiar, cortar y pegar optimizados para la mano derecha. Son los siguientes:

Ctrl + Insert = Copiar

Shift + Del = Cortar

Shift + Ins = Pegar

Sabiamente, Microsoft decidió no difundir estos atajos. Por qué, quién sabe. Es uno de los grandes enigmas del siglo XX.

Siento decirles a los zurdos y a mis compañeros de trabajo que leer esta nota no les ha servido de nada. A los primeros porque les es más natural utilizar los métodos de siempre, y a los segundos porque la mayoría de nuestros equipos (que son lap tops) no tienen una tecla Ctrl del lado derecho, o tienen Ins y Del en la parte inferior. Pero para todos aquéllos que utilicen PC, o un teclado estándar completo, es más natural dar Ctrl + Insert que Ctrl + C. De hecho, con un poco de práctica, logra hacerse sin mirar el teclado.

CONTAR.SI (y SUMAR.SI) Reloaded

CONTAR.SI nos retorna el número de celdas (o valores) que cumplen con determinado criterio dentro de determinado rango. La sintaxis es:

CONTAR.SI(rango, criterio)

Generalmente solo es usada en ejemplos como los siguientes:

=CONTAR.SI(A2:A30, 100);

=CONTAR.SI(B2:B20, "alguntexto");

=CONTAR.SI(C2:C40, "texto a" & "texto b"); o

=CONTAR.SI(D2:D50, C1)

Sin embargo, el argumento criterio es mucho más flexible. Podemos especificar expresiones lógicas, wildcards, fórmulas, valores lógicos, fechas... Estos son algunos ejemplos que muestran la potencia de la función al usar criterios complejos:

=CONTAR.SI(A2:A50, ">0") Devuelve el número de celdas con valores mayores a 0.

=CONTAR.SI(A2:A50, "<>1") Devuelve el número de celdas con valores distintos de 1.

=CONTAR.SI(A2:A50, "<" & B1) Devuelve el número de celdas con valores menores a la celda B1.

=CONTAR.SI(A2:A50, HOY()) Cuenta las celdas con fecha igual a la actual.

=CONTAR.SI(A2:A50, ">" & FECHA(2007,10,01)) Devuelve el número de celdas con fecha mayor (más reciente) a 01/10/2007. Ya hemos visto el uso de la función FECHA.

=CONTAR.SI(A2:A50, ">" & PROMEDIO(A2:A50)) Número de celdas con valores superiores al promedio del rango.

=CONTAR.SI(A2:A50, "?????") Da el número de celdas que contienen exactamente cinco caracteres.

=CONTAR.SI(A2:A50, "abc*") Da el número de celdas que empiezan con "abc". No es sensible a mayúsculas

=CONTAR.SI(A2:A50, "*abc*") Da el número de celdas que contienen "abc". No es sensible a mayúsculas.

=CONTAR.SI(A2:A50, "??z*") Devuelve el número de celdas cuyo tercer caracter es "z".

=CONTAR.SI(A2:A50, FALSO) Cuenta las celdas que contienen el valor lógico FALSO.

=CONTAR.SI(A2:A50, "#!REF!") Cuenta el número de celdas que contienen el valor de error #!REF!

Desde luego, podemos combinar las técnicas de los ejemplos anteriores. También, podemos usar este tipo de criterios en otras funciones, como SUMAR.SI.

Esta nota pensaba titularla "En el pedir está el dar".

Convertir texto a valores

Al importar o exportar datos (algo que mis compañeros de trabajo extrañamente llaman "bajar reportes de Sales Force") casi siempre vemos que Excel da formato de texto a los datos numéricos. El porqué Excel hace esto es algo que escapa a las capacidades mentales del que firma el presente. Tal vez sea un error de diseño. Tal vez sea por la diferencia de formatos. Tal vez nadie lo sabe. Tal vez...

El punto es que podemos convertir el formato de números como texto a números de varias maneras. Quizá la menos conocida es la siguiente: Nos situamos en cualquier celda vacía y la copiamos con Ctrl + C (por si alguien desconocía el significado de Ctrl + C...). A continuación seleccionamos el rango de valores que queremos arreglar. Damos clic derecho y vamos a Pegado especial. Una vez aquí seleccionamos la opción Valores y, en Operación, seleccionamos Sumar.

Por último, damos Aceptar.

Ahora tenemos valores numéricos. Si los valores originales tenían ceros a la izquierda, vemos que han sido eliminados.

¿Cómo funciona? Al seleccionar una opción de operación (Sumar), estamos forzando a Excel a tratar (y convertir) los valores como números. Como copiamos una celda vacía, estamos sumando 0 a los valores. Recordemos que una celda vacía es equivalente a 0 para efectos de cálculo.

La opción Valores la seleccionamos preventivamente para no eliminar los formatos que previamente hubiéramos aplicado.

Dos historias felices

Primera Historia Feliz.

Había una vez una usuaria que se llamaba Herenia con el siguiente problema: Había escrito una fórmula en una celda pero al dar Enter ella solo veía su fórmula. Ella quería ver el resultado de su fórmula, no su fórmula en sí -una situación un tanto frecuente. Aquí el detalle fue que la celda rebelde tenía formato de texto, así que cambiamos su formato a número y volvimos a ingresar la fórmula. Y vivió feliz para siempre. Fin.

Segunda Historia Feliz.

Érase una vez una usuaria llamada Claudia que estaba tratando de elaborar una fórmula con SUMAR.SI y CONTAR.SI. Ella quería establecer dos criterios de suma y dos de cuenta para después hacer un promedio. Como ella sabía que SUMAR.SI y CONTAR.SI pueden aceptar fórmulas como argumentos, concatenó los dos criterios con "&", pero no lo lograba. Después de hacer un análisis exhaustivo, vimos que el criterio a utilizar y el establecido en la fórmula no eran iguales. En su fórmula puso "Citas" pero en sus celdas tenía "Citas " (con espacio). Así que cambiamos "Citas " por "Citas" y ya pudo hacer su fórmula (en realidad hicimos un promedio matricial, pero igual vale). Fin.

It´s friday...

Ahora los tres in inglish.

After much arguing farmer #2 states that he is prepared to settle the matter by the Viking method. He explains that the method involves kicking each other in turn between the legs until one gives up, and the other is the winner.

Farmer #1 agrees reluctantly.

Farmer #2 states that since they are on his land, he goes first.

Farmer #1 stands with legs apart and hands on hips while Farmer #2 takes an almighty swing with his foot and sends farmer #1 into the air. After ten minutes writhing on the ground farmer#1 eventually gets to his feet and prepares to take his turn. Farmer #2 turns and walks away saying:

-O.K. I give in! You keep the duck!

It´s friday...

Zebidiah Zacariah joined the army, before long there was a call to war and they all lined up to receive their kit. Now because the army did everything in alphabetical order Zebidiah Zacariah was always last in line.When he got to the head of the line for his rifle there were none left so the seargent gave him a broom and said:

-Point this at the enemy and yell bang bang gun!

Zebidiah wasn't to bright so he accepted his broom with glee. The same thing happened in the line for grenades and he was given lemons and told

-Hurl these at the enemy yelling "Boom Boom grenade!" and youll be right.

He went to war and the battling was fierce, he threw his lemons and bang banged on his gun for all he was worth. Soon he looked around and realized there was only him and a guy from the other side left alive, he pulled out his broom and yelled 'Bang Bang Gun!' but the guy kept on coming so he hauled off with his lemons yelling 'Boom Boom grenade! and still the guy kept on coming ...until he was almost right on top of Zebidiah and Zebidiah heard him say:

-Rumble Rumble Tank!

It´s friday...

The Taco Bell Chihuahua, a Doberman and a Bulldog are in a bar having a drink when the great-looking female Collie comes up to them and says:

-Whoever can say liver and cheese in a sentence can have me.

So the Doberman says:

-I love liver and cheese.

The Collie replies:

-That's not good enough.

The Bulldog says:

-I hate liver and cheese.

She says: -That's not creative enough.

Finally, the Chihuahua says:

-Liver alone... cheese mine!

Those Collie and Chihuahua puppies are really cute.

Crear listas personalizadas

Una de mis dinusuarias favoritas, Maira, me sale con esta pregunta: "Tengo una tabla de datos que quiero ordenar respecto a la columna 'Probabilidad', en el siguiente orden: que aparezcan primero los que tengan la etapa 'venta liberada', luego los que tengan 'datos completos', 'oportunidad perdida' y al final los que tengan 'oportunidad detectada'. En ese orden. ¿Cuál es la serie de pasos secuenciales que debo seguir para poder llevar a cabo correctamente esta tarea?" (sic).

Evidentemente, no podemos utilizar el comando estandar Ordenar, ya que este sigue un criterio alfabético y el orden deseado no es alfabético. Asimismo, podríamos usar una columna adicional que valide los datos de la columna y asigne, mediante un SI, unos, doses, treses o cuatros a todos los registros y después realizar la ordenación respecto a esta columna adicional, pero no es un proceso muy elegante que digamos. O si no nos gusta trabajar, podemos escribir manualmente los unos, doses, treses y cuatros en la columna y después ordenar. De esta manera perderemos completamente la elegancia y caeremos cínicamente en lo vulgar. Otra opción es ordenar manualmente cada una de las filas, lo cual nos rebajaría en la escala social al mismo nivel de los políticos.

La solución consiste en crear y utilizar una Lista Personalizada. Procedemos como sigue:

Vamos a Herramientas - Opciones (Alt, h, n). Seleccionamos la ficha Listas Personalizadas. Luego, de la lista Listas personalizadas seleccionamos nueva lista. Después, en la lista Entradas de lista escribimos los datos que queramos que Excel tome como criterio, en el orden deseado, separados con comas (o bien, un dato por fila) y sin comillas. En el caso nuestro escribiremos:

venta liberada, datos completos, oportunidad perdida, oportunidad inválida

Damos Aceptar. Hemos creado una lista personalizada.

Ahora, para realizar la ordenación usando esta lista como criterio, vamos a Ordenar como normalmente lo hacemos (Datos - Ordenar) y seleccionamos la columna a ordenar ("Probabilidad"). A continuación, damos clic en el botón Opciones, abajo a la izquierda, para ir al cuadro Opciones de ordenación:

En la lista despleglabe que aparece, buscamos y seleccionamos nuestra lista, y damos Aceptar para volver al cuadro Ordenar. Establecemos otros criterios de búsqueda, si los hay, y volvemos a dar clic en Aceptar (o Enter).

Voilà.

Dividir nombres completos en nombre y apellidos II

Continuamos de la nota anterior.

Ya sabemos como extraer el nombre y el apellido paterno de un nombre completo, suponiendo que sea de la forma nombre - apellido materno - apellido paterno. Veamos ahora como extraer el apellido materno. (Por cierto, mi nombre completo es Leonel Quezada Ramírez. Ramírez por partida de madre y Quezada por parte de algo que se movía en la cocina y que espantó a mi mamá).

Para realizarlo necesitamos haber extraido primero el nombre y el last name del nombre en cuestión, como hicimos en la nota anterior,

además de la función EXTRAE. En C2 haremos una extracción sin anestesia.

Pueden imaginar lo que hace la función EXTRAE. La sintaxis es la siguiente:

EXTRAE(texto, posición_inicial, núm_de_caracteres)

EXTRAE devuelve un cadena de texto que se encuetra dentro del argumento texto, que inicia en la posición indicada en posición_inicial y que tiene una longitud igual a núm_de_caracteres.

Siguiendo con el ejemplo, nuestro argumento texto es A2; posición_inicial es la posición en que se encuentra el primer espacio, más uno:

=HALLAR(" ", A2, 1) + 1

núm_de_caracteres debe ser igual al número de caracteres del apellido materno. ¿Cual es la longitud del apellido materno? Simplemente la longitud del nombre completo menos la longitud del nombre y del apellido paterno, más dos, por los espacios. Esta fórmula devuelve el apellido materno:

=EXTRAE(A2, HALLAR(" ", A2, 1) + 1, LARGO(A2) - (LARGO(B2) + LARGO(C2) + 2))

Es importe señalar que las técnicas utilizadas solo sirven para dividir nombres similares al del ejemplo. Si pretendemos usarlas con nombres con títulos (como Mr. o Lic.) o apellidos compuestos (de la Torre), la cosa se complica demasiado, aunque pueden utilizar lo visto como un buen punto de partida. Asimismo, si las utilizamos para dividir otros textos, las técnicas para obtener la segunda y la última palabra solo funcionan con textos de tres palabras. Para tratar con textos más largos, es necesario otro tipo de fórmulas. Además es necesario dominar, primero, las técnicas de esta nota y la anterior.

Dividir nombres completos en nombre y apellidos

En alguna ocasión un conocido mío tenía el siguiente problema: Había recibido por e - mail una lista con nombres de personas en Excel, pero con la particularidad de que se presentaban juntos nombre y apellidos en una misma celda. Aparecía primero el nombre y luego los apellidos de la persona. Él necesitaba separar nombres de apellidos para poder ordenarlos por apellido paterno, entre otras cosas. No podía utilizar DERECHA ni IZQUIERDA (que ya vimos anteriormente) porque obviamente cada nombre tenía una extensión distinta.

En realidad sí tenemos que utilizar DERECHA e IZQUIERDA, aunque en combinación con otras más: EXTRAE, LARGO, ENCONTRAR (o HALLAR) y opcionalmente, ESPACIOS, NOMPROPIO, MAYUSC y MINUSC. Excel es muy flexible al trabajar textos. Solo hay que saber pedirle las cosas.

Dividamos el siguiente nombre completo, el cual está en A2, en nombre, apellido paterno y apellido materno.

Alejandra Reyes Avendaño

Eliminemos también los espacios excedentes entre palabras. Estos espacios casi siempre se producen al importar datos. Para hacerlo usamos la función ESPACIOS, la cual elimina los espacios excedentes excepto los espacios individuales entre palabras.

=ESPACIOS(A2)

Recordamos de la misma nota anterior que ENCONTRAR nos devuelve la posición en la que se encuentra determinado texto (o caracter) dentro de otro texto. Usémosla para determinar la posición del primer espacio en blanco de nuestro nombre, con esta fórmula:

=ENCONTRAR(" ", A2, 1)

En nuestro caso el resultado es 10. Si restamos uno, obtenemos la longitud de "Alejandra" (9).

Por lo tanto, para extraer el nombre, utilizamos la función IZQUIERDA con la fórmula anterior como segundo argumento. Esta fórmula devuelve la primera palabra de un texto:

=IZQUIERDA(A2, ENCONTRAR(" ", A2,1)-1)

Ahora determinemos el último apellido. Esto es un poco más difícil ya que ENCONTRAR solo funciona de izquierda a derecha. Aquí el problema es encontrar la posición del segundo espacio dentro del nombre para poder utilizarlo con la función DERECHA. (En nuestro ejemplo el segundo espacio está en la posición 16). Utilizamos la función ENCONTRAR, pero especificando como caracter inicial de búsqueda la posición del primer espacio más uno: un ENCONTRAR doble (sinceramente espero que vayan entendiendo). En otras palabras, queremos la posición del carácter " " empezando a contar de la posición 11.

Esta fórmula devuelve 16:

=ENCONTRAR(" ", A2, ENCONTRAR(" ", A2, 1)+1)

Ahora usamos la función derecha con el segundo argumento igual a la longitud total del texto menos los primeros 16 carácteres. Esta fórmula extrae el último apellido de un nombre completo:

=DERECHA(A2, LARGO(A2)-ENCONTRAR(" ", A2, ENCONTRAR(" ", A2,1)+1))

Continuamos en la siguiente entrada.

Repetir encabezados al imprimir

Hoy veremos una cuestión básica de Excel, pero que igualmente puede ser de utilidad para algunos.

Buena parte de las veces que imprimimos listas de datos, sucede que estas tienen una extensión de más de una página. Si no hacemos ningún ajuste, solo la primera página mostrará los encabezados. Resulta poco práctico imprimir una lista sin encabezados, sobre todo si tenemos 1000 columnas (o filas) en nuestra lista.

Para lograr que Excel repita la fila o filas de encabezados en cada página impresa, hacemos el siguiente ajuste:

Vamos a Archivo - Configurar página - Ficha Hoja.

Una vez aquí, escribimos en Repetir filas en extremo superior, las filas que queramos que se repitan en cada hoja, por ejemplo $3:$4 (no necesariamente tienen que ser las primeras).

Alternativamente, podemos dar clic en el botón de selección de rango, situado dentro del input box, a la derecha, y seleccionamos las filas con el ratón. Al finalizar volvemos a dar clic en el botón de selección para regresar a Configurar Página.

Desde aquí también tenemos la opción de repetir una columna o columnas en el extremo izquierdo de cada hoja, en los casos en que trabajamos con demasiadas columnas.

Como observación, tenemos que hacer estos ajustes estando en la vista normal. Si lo intentamos desde la vista preliminar (y después haciendo clic en el botón Configurar...) estarán inhabilitadas ambas opciones.

Nota

Los fines de semana no podré publicar notas. Los días festivos, como hoy y mañana, tampoco.

Muchas gracias bye.