Extraer palabras de una frase en Excel

Me llegó esta consulta: ¿cómo puedo extraer de una frase la cuarta palabra sin importar la cantidad de palabras en la frase? Por ejemplo, si en una celda tenemos la frase: “Cuántas palabras hay en esta frase” , cómo podemos extraer la tercer palabra, “hay”.

Chip Pearson trae una solución basada en funciones matriciales que desarrolló Laurent Longre.

Por ejemplo, si ponemos la frase mencionada en la celda A2 y en B2 el número de orden de la palabra a extraer, esta fórmula dará el resultado esperado

=EXTRAE(A2,K.ESIMO.MENOR(SI(EXTRAE(" "&A2,FILA(INDIRECTO("1:"&LARGO(A2)+1)),1)=" ",FILA(INDIRECTO("1:"&LARGO(A2)+1))),B2),SUMA(K.ESIMO.MENOR(SI(EXTRAE(" "&A2&" ",FILA(INDIRECTO("1:"&LARGO(A2)+2)),1)=" ",FILA(INDIRECTO("1:"&LARGO(A2)+2))),B2+{0;1})*{-1;1})-1)

Podemos extender la cuestión a cómo extraer de una frase una cadena de palabras a partir de una posición determinada.

Por ejemplo , en la celda A3 ponemos la frase “ En esta frase hay muchas más palabras que en la anterior”; si queremos extraer la cadena formado por las tres palabras a partir de la cuarta palabra en la frase (“hay”), usamos esta fórmula donde en B3 ponemos el número de orden la palabra del comienzo y en C3 la cantidad de palabras a extraer

=EXTRAE(A3,K.ESIMO.MENOR(SI(EXTRAE(" "&A3,FILA(INDIRECTO("1:"&LARGO(A3)+1)),1)=" ",FILA(INDIRECTO("1:"&LARGO(A3)+1))),B3),SUMA(K.ESIMO.MENOR(SI(EXTRAE(" "&A3&" ",FILA(INDIRECTO("1:"&LARGO(A3)+2)),1)=" ",FILA(INDIRECTO("1:"&LARGO(A3)+2))),B3+C3*{0;1})*{-1;1})-1)

Recordemos que éstas son funciones matriciales, es decir que deben ser introducidas apretando simultáneamente Ctrl+Mayúsculas+Enter

Con todo el respeto debido a Chip Pearson y a Laurent Longre (que lo tienen bien merecido), estas soluciones tienen dos problemas. El primero y más importante es que las funciones matriciales son muy “pesadas” y si se usan con cierta intensidad en una hoja el cálculo de la hoja (cada vez que cambia un dato) toma mucho tiempo. El segundo problema es que si hay más de un espacio entre las palabras de la frase o hay espacios al principio de la frase se obtienen resultados erróneos.

Mi propuesta es usar funciones definidas por el usuario (UDF). En mi primer intento escribí esta función que extrae una palabra determinada de la frase. La función tiene dos argumentos: la celda que contiene la frase y la posición de la palabra a extraer.

El código de la función es:


Function extraer_palabra(frase As Range, palabra As Integer)

Dim arrFrase As Variant

arrFrase = Split(WorksheetFunction.Trim(frase), " ")

extraer_palabra = arrFrase(palabra - 1)


End Function

Como ven, estoy usando la función TRIM de Excel y no la de Vba. Esto se debe a que ambas funciones no se comportan de la misma manera y sólo la de Excel quita todos los espacios dejando sólo uno entre cada palabra.

Como en el caso anterior, vamos a ampliar nuestra función de manera que podamos extraer una cadena de palabras de una frase en una celda. La función, “ extraer_palabra2”, tiene tres argumentos: la celda que contiene la frase, la posición de la primer palabra de la cadena a extraer y la cantidad de palabras a extraer.

El código de la función es:

Function extraer_palabra2(frase As Range, _
palabra1 As Integer, cuantas As Integer)

Dim arrFrase As Variant, iX As Long, temp



arrFrase = Split(WorksheetFunction.Trim(frase), " ")

extraer_palabra2 = arrFrase(palabra1 - 1)

For iX = palabra1 + 1 To palabra1 + cuantas - 1

extraer_palabra2 = extraer_palabra2 & " " & arrFrase(iX - 1)

Next iX

End Function



Por ejemplo, queremos extraer una cadena de cuatro palabras a partir de la tercera: “hay en esta frase”

Obtenemos



Estas funciones podemos guardarlas en el cuaderno Personal.xls para que estén disponibles en todo momento.

Technorati Tags: MS Excel

Como definir un icono para agregar una hoja en Excel

Me considero una persona modesta. Es decir, alguien que se conforma con poco, con lo que hay. Tal vez por eso me sorprende encontrar de tanto en tanto, en particular en ciertos foros, gente que se queja de Excel.
Es verdad, hay ciertos aspectos de Excel que pueden volverse molestos, ciertas funcionalidades que faltan o cosas que no funcionan como quisiéramos. Pero por otra parte Excel nos ofrece suficientes herramientas para corregir, aunque se parcialmente, estas situaciones. Un ejemplo mostramos ayer en la nota sobre cómo agregar un icono para cancelar el autofiltro.

Otra funcionalidad que encuentro un tanto molesta en Excel es cuando agregamos una hoja a un cuaderno (en particular por mi mala costumbre de no planear el diseño del cuaderno de antemano y verme obligado a agregar hojas a medida que el proyecto avanza).

En Excel 2003 (y versiones anteriores hasta Excel 97, si no me equivoco) podemos agregar una hoja con uno de estos métodos:

# - abriendo el menú insertar en la pestaña de la hoja

# - con el icono del comando Insertar hoja de cálculo (que podemos arrastrar y ubicar en alguna de las barras de herramientas)



El problema con estos métodos es que no tenemos control de donde será ubicada la hoja. Lo natural sería que a la derecha de la última, pero no es esto lo que sucede. Por algún motivo Excel pone la nueva hoja a la izquierda de la hoja activa. Además, Excel pone un nombre por defecto. Mi idea es que Excel inserte la nueva hoja a la izquierda de la última del cuaderno y que nos invite a poner el nombre antes de crearla.

Para hacer esto usaremos una macro que luego ligaremos al icono de insertar hoja de cálculo. La macro la guardamos en el cuaderno Personal.xls, para que esté disponible para todo cuaderno de Excel con el cual estemos trabajando.

El código de nuestra macro es:

Sub agregar_hoja_con_nombre()
'02/03/2009 by Jorge Dunkelman

Dim shName As String

If ActiveWorkbook Is Nothing Then Exit Sub

shName = Application.InputBox(prompt:="Nombre de la hoja?", Title:="Nombre", _
Type:=2)

Select Case shName
Case Is = "False"
MsgBox "No se ha insertado una hoja"
Exit Sub
Case Is = ""
Sheets.Add after:=Sheets(Sheets.Count)
Case Else
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = shName
End Select

End Sub


Al correr la macro comprueba en primer lugar si hay un cuaderno abierto (de lo contrario no hay donde agregar la hoja!). Luego se abre una ventanilla donde se nos pide definir el nombre de la hoja. En caso de dejarlo en blanco se insertará una hoja con el nombre por defecto (Hoja4, Hoja5, etc.); en caso de apretar Cancelar aparecerá un mensaje diciendo que no se ha insertado la hoja y en caso de poner un valor éste aparecerá en la pestaña de la hoja.

La hoja siempre se inserta a la derecha de la última hoja del cuaderno sin tomar en cuenta cuál era la hoja activa en ese momento.

Estamos usando el método InputBox (y no la función InputBox de Vba), para poder distinguir cuando el usuario aprieta Cancelar de cuando deja el nombre en blanco.

Ahora sólo nos queda por crear el icono y agregarlo a alguna de las barras. Podemos crear un icono de la categoría macro (como vimos en la nota anterior) y luego cambiar la imagen copiándola del icono de insertar hoja que existe en Excel.

Para hacer esto abrimos el menú Herramientas-Personalizar y usamos Copiar imagen

Luego elegimos el icono que acabamos de crear y pegamos la imagen (y por supuesto, lo ligamos a la macro que guardamos en el cuaderno Personal.xls)

La técnica para crear el icono en Excel 2007 está explicada en esta nota.

Technorati Tags: MS Excel

Como crear un icono para quitar Autofiltro

En la nota sobre los comandos personalizados para Autofiltro, mostré como agregar ciertos iconos que nos permitan ahorrar tiempo a quienes usamos Autofiltro con frecuencia.

Uno de estos iconos nos permite agregar el autofiltro con un solo clic

Para poner autofiltro a una tabla apretamos este icono. Sería muy conveniente que al apretar nuevamente este icono, quitemos el autofiltro. Es decir, que funcione como un interruptor. Eso es lo que sucede en Excel 2007, pero no en las versiones anteriores. Si queremos quitar el autofiltro tenemos que ir al menú Datos y hacer clic a Filtro.

En esta nota mostraré como crear una macro para cancelar el autofiltro y cómo ligarla a un icono, que pondremos en la misma barra de herramientas del icono de autofiltro.
Empecemos por la macro, que es muy sencilla. En un módulo, preferentemente del cuaderno Personal.xls, ponemos este código

Sub filter_off()
ActiveSheet.AutoFilterMode = False
End Sub

Ahora lo ligaremos a un icono, que luego pondremos en la barra de herramientas correspodiente. Empezamos por abrir el menú Herramientas-Personalizar y en el formulario ir a la pestaña Comandos y elegir la opción Macros

Arrastramos el botón con el “smiley” y lo ubicamos al lado del icono de Autofiltro (el embudo con el símbolo “=” a la izquierda)



Señalamos el icono con el mouse y pulsamos el botón derecho para abrir el menú



Le asignamos la macro, en la ventanilla Nombre ponemos “Cerrar Autofiltro” y seleccionamos la opción “Sólo texto (siempre)”



El resultado es un icono de texto. Al apretar este icono, si en la hoja hay una tabla con Autofiltro, éste será quitado.



Pero sería muy bueno si en lugar del icono de texto apareciera uno similar al de Autofiltro, por ejemplo, un embudo con un signo “=” pero cruzado por una X, es decir, esto



Excel nos provee con las herramientas necesarias para crear el icono. Empezamos por abrir el menú Herramientas-Personalizar y seleccionar (un solo clic) el icono del embudo. Abrimos el menú y apretamos Copiar imagen del botón



Ahora seleccionamos el icono de texto “Cerrar Autofiltro” y en el menú del botón apretamos Pegar imagen



A esta altura del partido tenemos la imagen del embudo junto al texto. Para deshacernos del texto usamos la opción “Estilo predeterminado” y luego elegimos la opción “Modificar imagen del botón”



Esto abre la esta ventanilla, donde podemos modificar la imagen.

En nuestro caso seleccionamos el color rojo (o cualquier otro que encuentren conveniente) en el cuadro de colores (negro es la opción por defecto) y marcamos una X señalando con el mouse los cuadrados correspondientes a la diagonal del cuadro de la imagen



Una vez concluida la tarea apretamos Aceptar. Ahora tenemos dos iconos, uno para poner el Autofiltro y otro para quitarlo



Esta nota tiene un cierto tinte de nostalgia anticipada, ya que las barras de herramientas y los botones han desaparecido de Excel 2007. Supongo que en no mucho tiempo, esta nota será caduca, pero mientras tanto podemos seguir disfrutando de la funcionalidad de las barras de herramientas y la posibilidad de crear botones personalizados.

Technorati Tags: MS Excel