Extraer palabras de una frase en Excel

lunes, marzo 09, 2009

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:

19 comments:

carlos 09 marzo, 2009 23:02  

Que tal Maestro, solo te escribo para decirle lo muy agradecido que le estoy, ahora ya voy a poder dormir a gusto, pero creo que tu sabes a que me refiero verdad, cuando algo no te sale no puedes ni dormir pensando en como resolver el problema, y siendote sincero no te queria pedir ayuda porque lo queria resolver yo mismo, pero tve que tragar mi ego jaja, pero pues ya llevaba como casi 2 semanas con este problema y pues no me salia con ninguna formula y la verdad no se mucho macros, asi que tambien aprovecho para rpeguntarte si me reocmiendas alguna pagina para aprender acerca de Macros.

Pues solo te vuelvo a decir gracias por la atencion que tuviste para conmigo, y felicitarte por tu EXCELENTE aporte.

Saludos!!!

Anónimo,  10 marzo, 2009 13:01  

¿Y si 'hay' no ocupa siempre la tercera posición?
¿Y si hay más de un 'hay' en la frase?
¿Y si no hay ninguno?

Supongo que daría para otro post, pero amplio esta duda suponiendo que en esa frase tenemos el código 'hay' en una sucesión de caracteres a mitad de la palabra (XXXhayXXXX), y que ésta varía de posición en cada frase (o celda de Excel), incluso puede no aparecer.

¿Se podría hacer con la ayuda de una macro? Por ejemplo mostrando en una nueva pestaña las coincidencias que haya encontrado.
Perfectamente podrías ser un histórico o 'log' muy básico que podria ser manejado desde excel.

Anónimo,  10 marzo, 2009 15:03  

No es perfecto, pero me sirve:

Sub completar_por_palabras()
Dim arrFrase As Variant
Dim intFilas As Integer, intPalabras As Integer

Range("A1").Select
While CStr(ActiveCell.Offset.Value) <> ""

arrFrase = Split(WorksheetFunction.Trim(ActiveCell.Offset.Value), " ")
ActiveCell.Offset(0, 1).Value = UBound(arrFrase) + 1
For intPalabras = 1 To UBound(arrFrase) + 1
ActiveCell.Offset(0, intPalabras + 1).Value = arrFrase(intPalabras - 1)
Next

intFilas = intFilas + 1
ActiveCell.Offset(1, 0).Select
Wend

MsgBox (intFilas & " filas")

End Sub

Jorge L. Dunkelman 10 marzo, 2009 17:55  

Estimado
la nota trata sobre cómo extraer la palabra que sencuentra en una posición determinada en la frase, no la palabra específica.
Como una introducción a tus preguntas puedes leer esta nota.

Edwin Reyes 11 marzo, 2009 04:28  

Excelente aporte Jorge.....

Usted sabra que siempre estoy pendiente de sus post, he aprendido mucho de Excel por su excelente blog.

Como dijo "Carlos", nos puede indicar algun lugar, manual donde podamos aprender y estudiar MACROS, le agradeceria nuevamente entre tantas veces.

Saludos y bendiciones

Anónimo,  11 marzo, 2009 10:30  

Hola Jorge,

gracias por las notas. Las leeré tranquilamente más adelante.

Y cierto es que la premisa es distinta. En mi caso buscaba ninguna, una o varias palabras dentro de una frase. Y sólo me interesan esas palabras. Así que si las encuentro, o si encuentro una sucesión concreta, lo guardaré en una matriz para utilizarlas luego y eliminar duplicados.

Es un problema que había dejado aparcado porque iba a hacer algo en Visual, pero esto me simplifica lo que quería hacer.

Te dejo un comentario en la nota que me has puesto.

Anónimo,  18 diciembre, 2009 22:22  

Una hipermegafórmula pero funciona bien para lo que pide el que quiere extraer la n-esima palabra. La celda A1 tiene la frase y en la A2 se coloca el número de la palabra que se desea extraer, la fórmula se coloca en la celda B2 en donde dará el resultado.

=SI(A2=1,EXTRAE(A1,1,HALLAR(" ",A1,1)),SI(ESERROR(EXTRAE(SUSTITUIR(SUSTITUIR(A1&" "," ","&",A2-1)," ","#",A2-1),HALLAR("&",SUSTITUIR(SUSTITUIR(A1&" "," ","&",A2-1)," ","#",A2-1),1)+1,(HALLAR("#",SUSTITUIR(SUSTITUIR(A1&" "," ","&",A2-1)," ","#",A2-1),1)-HALLAR("&",SUSTITUIR(SUSTITUIR(A1&" "," ","&",A2-1)," ","#",A2-1),1))-1)),"",EXTRAE(SUSTITUIR(SUSTITUIR(A1&" "," ","&",A2-1)," ","#",A2-1),HALLAR("&",SUSTITUIR(SUSTITUIR(A1&" "," ","&",A2-1)," ","#",A2-1),1)+1,(HALLAR("#",SUSTITUIR(SUSTITUIR(A1&" "," ","&",A2-1)," ","#",A2-1),1)-HALLAR("&",SUSTITUIR(SUSTITUIR(A1&" "," ","&",A2-1)," ","#",A2-1),1))-1)))

Anónimo,  07 diciembre, 2014 03:38  

Apreciable Jorge: Yo necesito extraer las dos ultimas palabras de una celda, ¿cómo se haría?. De antemano Gracias

Jorge Dunkelman 07 diciembre, 2014 20:19  

Puedes usar la segunda fórmula donde la primer variable (de qué palavra en la frase empezar) la podemos calcular con esta fórmula:

=LARGO(A3)-LARGO(SUSTITUIR(A3," ",""))

es decir, y siguiendo con el ejemplo, en la celda B3 ponemos la fórmula mencionada y en C3 ponemos 2 (el número de palabras a extraer).

Jorge Dunkelman 07 diciembre, 2014 20:28  

Y por supuesto también la segunda UDF.

Anónimo,  08 septiembre, 2015 18:14  

HOLA QUERIA AYUDA EN LO SIGUIENTE.

TENGO UNA CELDA QUE CONTIENE UN TEXTO. EN OTRA TENGO UNA LISTA DE PALABRAS Y QUIERO HACER UNA NUEVA COLUMNA DONDE ME PONGA VERDADERO O FALSO SI HAY ALGUNA PALABRA EN EL TEXTO QUE SEA DE LA LISTA DE PALABRAS.

ESPERO QUE SE HAYA ENTENDIDO .

POR EJ..

EN A1 = " HOLA COMO ESTAS SOY ARMANDO Y TENGO 4 DAIKIRIS EN ISLA Y OCEANO".

EN LA LISTA TENGO LAS SIGUIENTES PALABRAS

ISLA
CANOA
DAIKIRIS

Jorge Dunkelman 09 septiembre, 2015 07:12  

Estimado, para esa tarea Excel nos ofrece dos funciones: HALLAR() y ENCONTRAR(). Para que el resultado sea VERDADERO o FALSO tendrás que combinarlas con ESNUMERO(). Fijate en la ayuda del asistente de funciones de Excel.

Libro de quejas y matices 12 septiembre, 2015 02:39  

hola. Gracias por la respuesta. Voy a probarlo. pero tengo un listado largo de siglas y por eso pensaba en una Function.

Talk About 10 febrero, 2016 02:17  

Gracias amigos, yo estoy buscando como sacar las dos ultimas palabras.

Jorge Dunkelman 16 febrero, 2016 18:53  

Publicaré un post sobre el tema en breve.

Raúl Olivares,  30 junio, 2017 02:44  

Habrá la forma de buscar y contar una palabra específica, escrita dentro de un Comentario?

Jorge Dunkelman 30 junio, 2017 22:59  

Solamente usando una macro. Ésta tendría que extraer todos los textos de los comentarios y realizar la busquqeda.

Jorge Bejarano 05 septiembre, 2017 02:29  

Jorge buenas tardes, gusto de saludarte. Deseo saber si es posible ya sea con formulas o macro, UDF, obtener palabras de un número determinado de caracteres, por ejemplo si deseo de 7, me de como resultado "cuantas", y cualquier otra mas de 7 caracteres. O si por ejemplo deseo de 3 caracteres, me de como resultado "hay", y así sucesivamente para cualquier otro número de caracteres. Gracias por la atención- Saludos Jorge desde Perú

Jorge Dunkelman 14 septiembre, 2017 16:56  

Se puede hacer pero ¿cuál sería la lógica a aplicar cuando hay más de una palabra con la misma cantidad de letras?
Creo que sería mejor que definas el objetivo para ver que otras posibilidades hay.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP