Extraer el ultimo elemento de un texto en una celda en Excel

martes, marzo 20, 2007

Ayer un compañero de trabajo me trajo el siguiente problema. Había recibido una lista productos de nuestra empresa como esta




Mi compañero quería poner los números de catálogo en una columna y las descripciones de los productos en otra.
Como pueden ver el problema consiste en que el número de catálogo es la última palabra en la celda y el número de palabras por celda es variable. Esto hace que no podamos emplear Datos—Texto en Columnas.

La solución consiste en hacer algunas manipulaciones con funciones de Texto. Las funciones que usaremos en este caso son: DERECHA, ENCONTRAR, SUSTITUIR y LARGO.

Nuestro objetivo es construir una fórmula DERECHA(A1, número de caracteres). El problema es establecer el número de caracteres para cada celda.

El primer paso consiste en identificar el separador de las palabras; en nuestro caso es el espacio (que en fórmulas de Excel representamos " "). El número de caracteres será el número total de caracteres en la celda menos el número de caracteres hasta la última palabra, es decir hasta el último espacio.

Tomemos el texto de la celda A1



Usamos la formula SUSTITUIR(A1," ","") que da como resultado

DARKBLUENIPPLEWITHOUTTRAY022052801

Sencillamente hemos eliminado los espacios reemplazando " " por "".


Este resultado lo usamos como argumento en la función LARGO

LARGO(SUSTITUIR(A1," ","")) que da como resultado 34

Ahora usamos la fórmula

SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ","")))

Que da como resultado

DARK BLUE NIPPLE WITHOUT TRAY*022052801

Es decir, hemos puesto un asterisco * entre la anteúltima y la última palabra.
El truco consiste en que LARGO(A1) calcula el total de caracteres en la celda incluyendo los espacios en blanco (39); la fórmula LARGO(SUSTITUIR(A1," ","")) hace el cálculo sin los espacios en blanco (34). La diferencia (5) es la posición del último espacio.

Ahora podemos encontrar la posición de * usando
ENCONTRAR("*",SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ",""))))

Que da como resultado 30

Ahora sabemos que el largo del texto es 39, y que nuestro separador de la última palabra se encuentra en la posición 30. Por lo tanto el largo de la última palabra es 9.

Así que usamos la fórmula

DERECHA(A1,LARGO(A1)-ENCONTRAR("*",SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ","")))))

Que nos da el resultado esperado.

Esta solución fue propuesta por Ken Wright y mencionada por David McRitchie

Para separar la descripción usamos la función IZQUIERDA de esta manera

=IZQUIERDA(A1,LARGO(A1)-LARGO(B1))

cuando en B1 hemos puesto la fórmula que nos da el número de catálogo.


Technorati Tags:

32 comments:

Anónimo,  28 abril, 2007 10:36  

Jorge,
Esta aportación me parece excelente, pero por favor me puedes explicar ¿como logras que aparezcan las formulas en las columnas B y C al insertar un número en la columna A de tu ejemplo?
Agradeceré tus comentarios.
Saludos de México.
Gracias
Enrique Carrillo
enrique.carrillo.z@gmail.com

Jorge L. Dunkelman 28 abril, 2007 18:55  

Hola Enrique,
no sé si interpreto bien tu pregunta, pero en las columnas B y C están las fórmulas que dan los resultados que ves en las celdas.
Puedes decargar el archivo del ejemplo para ver como funcionan estas fórmulas.

Anónimo,  30 abril, 2007 08:32  

Gracias, Jorge,
He descargado y estudiado las formulas y de antemano te comento que ya las estoy aplicando y agradezco tu colaboración, lo que me parece muy interesante y espero me pueda explicar es lo siguiente:
En el rango de celdas A25:C25 las celdas se muestran vacías, sin embargo al insertar un código alfanumérico en la celda A25, aparecen automáticamente las formulas en las celdas B25 y C25.
Mi pregunta es: ¿como se logra este efecto en las celdas aledañas a la C25?
Espero no importunarte con mi pregunta,
Estoy a tus órdenes para cualquier comentario.
Gracias!
Saludos cordiales,
Enrique Carrillo
Enrique.carrillo.z@gmail.com

Jorge L. Dunkelman 30 abril, 2007 19:56  

Hola Enrique
Excel tiene una funcionalidad que llama "extender formato de lista y fórmulas" que es la responsable de la aparición automática de fórmulas. Esto sucede bajo ciertas condiciones y es similar a los que sucede con al funcionalidad Autocompletar valores de celda.
Hoy he publicado una entrada sobre el tema.

Anónimo,  01 mayo, 2007 07:06  

Jorge,
Gracias, ya entendí como Excel asume que después de 4 ó 5 eventos, el usuario cotinuará utilizando la misma fórmula y/o formato.
De nuevo, Gracias.
Saludos.
Enrique Carrillo

Anónimo,  07 junio, 2007 00:57  

Hola!!!
Me parece muy interesante esta formula, pero si el producto tiene espacio al final no genera ningun resultado...
Y para ello llegue a esto:

=SI(DERECHA(A1,1)=" ",DERECHA(A1,LARGO(A1)-ENCONTRAR("*",SUSTITUIR(A1," ","*",-1+LARGO(A1)-LARGO(SUSTITUIR(A1," ",""))))),DERECHA(A1,LARGO(A1)-ENCONTRAR("*",SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ",""))))))

Jorge L. Dunkelman 07 junio, 2007 06:47  

Hola, muy buena observación.
Otra variante sería usar la función ESPACIOS para quitar todos los espacios en blanco que no sean espacios entre palabras:

=DERECHA(ESPACIOS(A1);LARGO(ESPACIOS(A1))-ENCONTRAR("*";SUSTITUIR(ESPACIOS(A1);" ";"*";LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(ESPACIOS(A1);" ";"")))))

De esta manera cubrimos también el caso que haya más de un espacio al final.

Gracias por el aporte

Anónimo,  22 septiembre, 2007 00:20  

Buenas tardes
De antemano les agradezco a su atencion a mi pregunta, que es algo similar a la extraccion de datos de la que hablan,

pero lo que yo necesito es extraer la informacion de una de una celda excepto los ultimos dos digitos, les agradeceria si me pudieran comentar sobre alguna opcion

ejemplo
los datos que aparecen en la celda son

BBCS42RA

y yo neecesito solo

BBCS42

el detalle es que la cantidad de digitos en las celdas varian, pueden ser asi:
BS21RA
B09RA
B33CA
DD30RA
DB15RA
DP30RA
SB27RA
BBCS42RA
BBC42CA
BLC36EL
PE84CA
P1884RA
WBC3630CA
W0942DE

les gradezco sus finas atenciones

Atte David Cepeda R.

Saludos Cordiales

Jorge L. Dunkelman 04 octubre, 2007 18:49  

Hola David

tendrías que usar esta fórmula:

=IZQUIERDA(A1;LARGO(A1)-2)

Anónimo,  05 octubre, 2007 00:01  

Muy buenas tardes,

Le pido de nuevo su ayuda, y de antemano le agradezco su valioso tiempo y aprovecho para darle las gracias a mi pregunta anterior.

Estoy hacien un hoja de excel para cotizar algunos muebles y mi pregunta es que si es posible ligar el contenido de una celda para que me presente una imagen,

es decir, yo escribo el numero de articulo en una celda y que en la celda siguiente me aparezca la imagen que corresponda a ese articulo

Deseandole que tenga un excelente dia, le gradezco me ayude con esta cuestion.

Saludos Cordiales

David Cepeda R

Anónimo,  06 octubre, 2007 17:50  

Estimado Jorge, te agradezco tu valiosa ayuda, he descargado ya el archivo y estoy por empezar a trabajar en el.

otra vez gracias

David Cepeda R.

Saludos

Ulices 18 enero, 2008 16:33  

Buenos dias, la siguiente es para formular una pregunta, con relacion a una formula en excel, tengo el siguiente problema, tengo una listado de nombres y en el mismo se encuentran dos nombres por celdas, mi pregunta es como hago para separar los dos nombres en celdas distintas si los mismos no son de las misma longitud.

Jorge L. Dunkelman 18 enero, 2008 18:04  

Ulices

se trata de dos nombres o dos palabras? Si fueran dos palabra no habría ningún problema. Lo más eficiente sería con Datos--Texto en Columnas.
Pero sospecho que se trata de dos nombres. Cada nombre puede estar compuesto por varias palabras. Por ejemplo mi nombre serían dos palabras, pero Juan Carlos López serían tres.
Si todos los nombres e componen de la misma cantidad de palabras, sería fácil. También ayudaría si el segundo nombre propio apareciera abreviado, por ejemplo Juan C. López. Así tendríamos un criterio para lograr la separación.
En fin, cuál es la situación en tu lista?

El DrMerengue 05 febrero, 2008 23:38  

Jorge amigo, excelente foro, espero puedas ayudarme...mi problema es el siguiente:

Tengo 5000 registros de un solo campo que contienen caracteristicas de llantas, medidas, tamaños de aro, tipo, presentacion y me han pedido que desglose ese campo en varios campos usando cada caracterstica de las llantas...en fin, pero estas 5000 llantas se filtran por marca, sin embargo no todas las marcas tienen los mismos datos, y si los tienen no estan en el mismo orden, ni tienen el mismo tamaño, es decir en una marca el diseño puede estar primero y en otra marca el diseño podria estar en el medio, al final o donde sea...bueno he podido solucionar algo usando extrae, buscar, hallar y anidando,...pero lo que no he podido hacer es buscar en una celda un determinado texto que yo elija o que sean 2 o 3 a la vez ( algo asi como un if..... "si en este campo X1 existe el texto "T1" o "T2" escribirlo en la celda Tal) y colocar ese texto en una determinada celda; los textos a buscar no se rpeiten mas de una vez en cada campo....espero puedas ayudarme

Jorge L. Dunkelman 06 febrero, 2008 22:25  

Don Merengue, el comentario no está relacionado con el contenido de la nota. Pero puedes mandarme tu consulta al mail, preferentemente con el archivo del problema.

Anónimo,  29 abril, 2008 12:18  

Buenas,
A mi me gustaria separa datos de una celda en filas distintas sin machacar el valor de la fila, es decir, añadiendo filas nuevas. ¿Podrian ayudarme?

Gracias por este foro, he aprendido muchisimo.
Un saludo

Jorge L. Dunkelman 29 abril, 2008 18:42  

Para añadir filas tendrías que usar macros. No se puede cambiar la configuración de la hoja con fórmulas.

irma 01 julio, 2008 23:58  

Hola Jorge buenas tardes, este ejemplo esta muy bueno, solo q a mi me pasa que en la celda tengo datos como:
CALKING WIRE 5*2.5 D 505349P0002
SEAL STRIP HSTR=12.7DSTR= 993.0RH 2 GMD
si saco la ultima palabra de la segunda celda no hay problema, me devuelve el GMD pero en la primer celda me devuelve *2.5 D 505349P0002 siendo que solo me interesaria obtener 505349P0002, podrias ayudarme????

De antemano mil gracias

Irma

Fundación Mario Hiriart 02 julio, 2008 16:25  

Excelente sitio... no sabe como le agradezco las instrucciones que de ahí saqué para resolver un problema que llevaba tiempo sin poder sin solucionar. Realmente como dicen los jóvenes "la rompe"

Jorge L. Dunkelman 02 julio, 2008 17:42  

Hola Irma,
en la fórmula, en donde aparece el símbolo"*", reemplázalo por "#".
En lugar de

=DERECHA(A23,LARGO(A23)-ENCONTRAR("*",SUSTITUIR(A23," ","*",LARGO(A23)-LARGO(SUSTITUIR(A23," ","")))))

debe ser, en tu caso

=DERECHA(A23,LARGO(A23)-ENCONTRAR("#",SUSTITUIR(A23," ","#",LARGO(A23)-LARGO(SUSTITUIR(A23," ","")))))

Sucede que en el texto del cual quieres extraer el elemento, ya aparece *, que es el símbolo que usa la fórmula para saber donde "cortar" el texto.

El DrMerengue 03 julio, 2008 16:55  

Amigos necesito su ayuda con un archivo en excel, lo que pasa es que este archivo pesa mas de 13 megas y contiene miles de campos con enlaces, queria saber si hay una manera de quitarles el enlace a todos esos campos, vale decir que hay mas de 10 hojas y en ellas tb hay campos con enlaces...por favor, espero su ayuda...como siempre este foro me ha slavado en muchas oportunidades...gracias

Jorge L. Dunkelman 03 julio, 2008 17:35  

Dr Merengue
se puede usar el menú Edición--Vínculos y allí usar la opción Romper Vínculos. Esto hara que los valores que resultan de fórmulas que contienen los vínculos se conviertan en valores constantes.

El DrMerengue 03 julio, 2008 18:14  

Amigo Jorge gracias por responder, fui a la opcion la cual me contabas y esta desactivada, selecciono un campo que tiene hipervinculo y sigue desactivada...a que se debria ??

Jorge L. Dunkelman 03 julio, 2008 23:14  

El último enlace al final de la nota te permite descargar el archivo con el ejemplo. Acabo de probarlo y veo que funciona.
Fijate en las definiciones de tu navegador (en especial el bloqueo de pop-ups).
De todas maneras, puedes copiar y adaptar las fórmulas que aparecen en la nota.

Henry 05 julio, 2009 23:57  

Excelente solución!! Ya había utilizado la función sustituir pero no había necesitado hasta ahora el parámetro "núm_de_ocurrencia".
Muchas gracias,

Henry Rios

Bxkjiiiiiiii !!!!!!! 07 diciembre, 2010 23:01  

Otra manera es esta colocas esta formula en la celda b1 colocas DARK BLUE NIPPLE WITHOUT TRAY 022052801 tomando en cuenta este ejemplo en particular en A1 y listo te da la ultima palabra que es el clave que necesitas (funciona de maravilla lo he probado 1000 veces y funciona de maravilla):
=DERECHA(A1,LARGO(SI(LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1=1,EXTRAE(A1,1,HALLAR(" ",A1,1)),SI(ESERROR(EXTRAE(SUSTITUIR(SUSTITUIR(A1&" "," ","&",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1)," ","#",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1),HALLAR("&",SUSTITUIR(SUSTITUIR(A1&" "," ","&",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1)," ","#",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1),1)+1,(HALLAR("#",SUSTITUIR(SUSTITUIR(A1&" "," ","&",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1)," ","#",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1),1)-HALLAR("&",SUSTITUIR(SUSTITUIR(A1&" "," ","&",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1)," ","#",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1),1))-1)),"",EXTRAE(SUSTITUIR(SUSTITUIR(A1&" "," ","&",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1)," ","#",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1),HALLAR("&",SUSTITUIR(SUSTITUIR(A1&" "," ","&",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1)," ","#",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1),1)+1,(HALLAR("#",SUSTITUIR(SUSTITUIR(A1&" "," ","&",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1)," ","#",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1),1)-HALLAR("&",SUSTITUIR(SUSTITUIR(A1&" "," ","&",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1)," ","#",LARGO(ESPACIOS(A1))-LARGO(SUSTITUIR(A1," ",""))+1-1),1))-1)))))

Anónimo,  08 junio, 2013 00:54  

me gustaria descargar el archivo del ejercicio, para poder entender bien el tema... pero no se encuentra disponible la descarga.. me podrias regalar otro enlace de descarga?... gracias

Jorge Dunkelman 10 junio, 2013 21:56  

Lamentablemente no tengo el archivo (el sitio Esnips dejo de funcionar). Si lo encuentro en alguno de mis back-ups, lo subiré de nuevo.

Jorge Lozano Mateos 11 septiembre, 2013 20:29  

Hola.
He aplicado tus fórmulas y me han sido de gran utilidad.
Muchas gracias por el aporte. Tienes un gran blog.
Saludos.

JUANP 15 julio, 2014 20:18  

Buenas tardes
Quisiera saber si es posible me puedan colaborar con alguna formula que me ayude a organizar estas direcciones que tan solo son un ejemplo. Gracias

DIRECCION
CL 21 # 1-71  GERMANIA
CL 53 # 3-06  AP803 ED NOVUM CHAPINERO
CL 64 A   57-23 AT 301 TV. 11 ET IV MOD N
CL 73 A # 79 D - 57 SUR BOSA NARANJOS 7762263 31253848 7762263
CL.  31 B   21 B 15 SUR - QUIROGA
KR 3 # 6B-90  BELEN CENTRAL
KR 30 # 60-25  APTO. 301 - NICOLAS DE FEDERMAN
KR 79 # 10D-59  BL. 11 APTO. 204 - PARQUES DE CASTILLA 3
KR. 17   31 - 40 APT 205

Jorge Dunkelman 16 julio, 2014 07:21  

Hola JuanP, por favor fijate en el enlace Ayuda (en la parte superior de la plantilla).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP