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: MS Excel
Jorge,
ResponderBorrarEsta 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
Hola Enrique,
ResponderBorrarno 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.
Gracias, Jorge,
ResponderBorrarHe 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
Hola Enrique
ResponderBorrarExcel 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.
Jorge,
ResponderBorrarGracias, 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
Hola!!!
ResponderBorrarMe 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," ",""))))))
Hola, muy buena observación.
ResponderBorrarOtra 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
Buenas tardes
ResponderBorrarDe 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
Hola David
ResponderBorrartendrías que usar esta fórmula:
=IZQUIERDA(A1;LARGO(A1)-2)
Muy buenas tardes,
ResponderBorrarLe 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
Hola David
ResponderBorrarhe tratado el tema en la nota sobre base de datos de imágenes en Excel.
Estimado Jorge, te agradezco tu valiosa ayuda, he descargado ya el archivo y estoy por empezar a trabajar en el.
ResponderBorrarotra vez gracias
David Cepeda R.
Saludos
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.
ResponderBorrarUlices
ResponderBorrarse 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?
Jorge amigo, excelente foro, espero puedas ayudarme...mi problema es el siguiente:
ResponderBorrarTengo 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
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.
ResponderBorrarBuenas,
ResponderBorrarA 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
Para añadir filas tendrías que usar macros. No se puede cambiar la configuración de la hoja con fórmulas.
ResponderBorrarHola Jorge buenas tardes, este ejemplo esta muy bueno, solo q a mi me pasa que en la celda tengo datos como:
ResponderBorrarCALKING 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
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"
ResponderBorrarHola Irma,
ResponderBorraren 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.
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
ResponderBorrarDr Merengue
ResponderBorrarse 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.
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 ??
ResponderBorrarEl último enlace al final de la nota te permite descargar el archivo con el ejemplo. Acabo de probarlo y veo que funciona.
ResponderBorrarFijate 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.
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".
ResponderBorrarMuchas gracias,
Henry Rios
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):
ResponderBorrar=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)))))
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
ResponderBorrarLamentablemente no tengo el archivo (el sitio Esnips dejo de funcionar). Si lo encuentro en alguno de mis back-ups, lo subiré de nuevo.
ResponderBorrarHola.
ResponderBorrarHe aplicado tus fórmulas y me han sido de gran utilidad.
Muchas gracias por el aporte. Tienes un gran blog.
Saludos.
Buenas tardes
ResponderBorrarQuisiera 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
Hola JuanP, por favor fijate en el enlace Ayuda (en la parte superior de la plantilla).
ResponderBorrar