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
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.
ResponderBorrarPues solo te vuelvo a decir gracias por la atencion que tuviste para conmigo, y felicitarte por tu EXCELENTE aporte.
Saludos!!!
¿Y si 'hay' no ocupa siempre la tercera posición?
ResponderBorrar¿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.
No es perfecto, pero me sirve:
ResponderBorrarSub 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
Estimado
ResponderBorrarla 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.
Excelente aporte Jorge.....
ResponderBorrarUsted 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
Hola Jorge,
ResponderBorrargracias 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.
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.
ResponderBorrar=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)))
Apreciable Jorge: Yo necesito extraer las dos ultimas palabras de una celda, ¿cómo se haría?. De antemano Gracias
ResponderBorrarPuedes usar la segunda fórmula donde la primer variable (de qué palavra en la frase empezar) la podemos calcular con esta fórmula:
ResponderBorrar=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).
Y por supuesto también la segunda UDF.
ResponderBorrarHOLA QUERIA AYUDA EN LO SIGUIENTE.
ResponderBorrarTENGO 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
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.
ResponderBorrarhola. Gracias por la respuesta. Voy a probarlo. pero tengo un listado largo de siglas y por eso pensaba en una Function.
ResponderBorrarGracias amigos, yo estoy buscando como sacar las dos ultimas palabras.
ResponderBorrarPublicaré un post sobre el tema en breve.
ResponderBorrarHabrá la forma de buscar y contar una palabra específica, escrita dentro de un Comentario?
ResponderBorrarSolamente usando una macro. Ésta tendría que extraer todos los textos de los comentarios y realizar la busquqeda.
ResponderBorrarJorge 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ú
ResponderBorrarSe 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?
ResponderBorrarCreo que sería mejor que definas el objetivo para ver que otras posibilidades hay.