Ayer comenté la manera de trabajar con un listado de información en la que en una única celda habían varios campos; vimos cómo con la herramienta texto en columna obteníamos un resultado.
Hoy llegaremos a un resultado similar empleando funciones de Excel.
Recordemos nuestro listado de partida:
En primer lugar construiremos tres columnas auxiliares con la función HALLAR; ya que nos aprovecharemos de la homogeneidad de los datos. Es importante fijarnos que dentro de cada celda, los distintos campos son claramente identificables.
El primer campo se ve que es el nombre de la película; el segundo campo es el Director (siempre empieza por 'Dirigido por'); el tercer campo nos habla del idioma de la película (siempre comienza por 'Idioma') y el último campo sería el de la 'duración'.
Si combinamos este conocimiento con la función HALLAR sabremos cuáles son los diferentes caracteres ordenados en los que comienza cada uno de los campos; si aplico la función =HALLAR("dirigida";$A2), el valor que obtenga me indicará que comenzando a contar caracteres desde la izquierda, el campo 'Dirigida' empieza en el valor 11. De igual forma para los otros campos =HALLAR("idioma";$A2) y =HALLAR("duracion";$A2):
Sólo nos queda extraer los caracteres que necesitamos del listado original, acción que ejecutaremos con las funciones EXTRAE, DERECHA e IZQUIERDA.
Para extraer el título de la película (es decir, el primer campo), anidando la función IZQUIERDA de la siguiente manera:
=ESPACIOS(IZQUIERDA($A2;$B2-1))
con la función IZQUIERDA($A2;$B2-1) consigo que de todos los caracteres de la celda A2 sólo me quede con los B2-1 primeros (11-1 = 10 primeros); anidando este texto resultante con la función ESPACIOS consigo eliminar el exceso posible de espacios en blanco.
Para obtener en otra columna el segundo campo 'Dirigida por', empleo la función EXTRAE anidada sobre la comentada ESPACIOS:
=SUSTITUIR((ESPACIOS(MAYUSC(EXTRAE($A2;$B2;($C2-$B2)))));"DIRIGIDA POR";"")
en este caso, ya que se observa en el listado original cierta discrepancia entre registros en mayúsculas y minúsculas, aplicando la función MAYUSC, convertimos siempre todos los caracteres en mayúsculas, para sobre este resultado de texto en mayúsculas, eliminarle los espacios repetidos.
Como sólo me interesa en nomnre del director, anidamos todo esto con la función SUSTITUIR que reempleza el texto "DIRIGIDA POR" por "", i.e., por nada.
De igual forma actuamos con el tercer campo de 'Idioma':
=SUSTITUIR(ESPACIOS(EXTRAE($A2;$C2;($D2-$C2)));"IDIOMA ";"")
Y por último con el campo duración:
=SUSTITUIR(DERECHA($A2;LARGO($A2)-$D2+1);"DURACION ";"")
Vemos el resultado:
Hoy llegaremos a un resultado similar empleando funciones de Excel.
Recordemos nuestro listado de partida:
En primer lugar construiremos tres columnas auxiliares con la función HALLAR; ya que nos aprovecharemos de la homogeneidad de los datos. Es importante fijarnos que dentro de cada celda, los distintos campos son claramente identificables.
El primer campo se ve que es el nombre de la película; el segundo campo es el Director (siempre empieza por 'Dirigido por'); el tercer campo nos habla del idioma de la película (siempre comienza por 'Idioma') y el último campo sería el de la 'duración'.
Si combinamos este conocimiento con la función HALLAR sabremos cuáles son los diferentes caracteres ordenados en los que comienza cada uno de los campos; si aplico la función =HALLAR("dirigida";$A2), el valor que obtenga me indicará que comenzando a contar caracteres desde la izquierda, el campo 'Dirigida' empieza en el valor 11. De igual forma para los otros campos =HALLAR("idioma";$A2) y =HALLAR("duracion";$A2):
Sólo nos queda extraer los caracteres que necesitamos del listado original, acción que ejecutaremos con las funciones EXTRAE, DERECHA e IZQUIERDA.
Para extraer el título de la película (es decir, el primer campo), anidando la función IZQUIERDA de la siguiente manera:
=ESPACIOS(IZQUIERDA($A2;$B2-1))
con la función IZQUIERDA($A2;$B2-1) consigo que de todos los caracteres de la celda A2 sólo me quede con los B2-1 primeros (11-1 = 10 primeros); anidando este texto resultante con la función ESPACIOS consigo eliminar el exceso posible de espacios en blanco.
Para obtener en otra columna el segundo campo 'Dirigida por', empleo la función EXTRAE anidada sobre la comentada ESPACIOS:
=SUSTITUIR((ESPACIOS(MAYUSC(EXTRAE($A2;$B2;($C2-$B2)))));"DIRIGIDA POR";"")
en este caso, ya que se observa en el listado original cierta discrepancia entre registros en mayúsculas y minúsculas, aplicando la función MAYUSC, convertimos siempre todos los caracteres en mayúsculas, para sobre este resultado de texto en mayúsculas, eliminarle los espacios repetidos.
Como sólo me interesa en nomnre del director, anidamos todo esto con la función SUSTITUIR que reempleza el texto "DIRIGIDA POR" por "", i.e., por nada.
De igual forma actuamos con el tercer campo de 'Idioma':
=SUSTITUIR(ESPACIOS(EXTRAE($A2;$C2;($D2-$C2)));"IDIOMA ";"")
Y por último con el campo duración:
=SUSTITUIR(DERECHA($A2;LARGO($A2)-$D2+1);"DURACION ";"")
Vemos el resultado:
Quizá no sea el sitio mas indicado para realizar esta pregunta, pero no encontré un lugar mejor.
ResponderEliminarHe realizado una hoja de calculo con el excel (buscándome la vidilla, porque no tengo ni idea de como funciona) para controlar el numero de clientes que tengo en el negocio. Los dias en filas, los diferentes tipos de producto (unos 11) en columnas. Todo esto con sus correspondientes totales y todo el rollo, pero hay algo que no se como hacer. ¿Cómo hago para que los datos de esa hoja, se borren para ir rellenando conforme pasan las semanas y por supuesto poder acceder a las semanas anteriores cuando quiera? En plan desplegable con acceso a la fecha que yo quiera en la misma tabla. No se si me he explicado bien.
Muchísimas gracias por anticipado.
David
Hola David,
ResponderEliminarno acabo de ver claro qué pretendes, pero suena a que quieres realizar algún tipo de filtro sobre tu base de datos. Echa un vistazo a
http://excelforo.blogspot.com/search/label/Filtros
quizá haya algo que te ayude a desenredar el lío que tienes.
Un filtro te permite ocultar a la vista o mostrar aquellos registros que desees, atendiendo a una serie de condiciones dadas por tí.
Según lo que he entendido es lo que quieres.
Saludos
Yo tengo otra pregunta, como hago en Excel para extraer todos los caracteres que se encuentran a la izquierda de la posicion 9 contando de derecha a izquierda
EliminarHola,
Eliminarbueno lo primero es indicar de manera entendible para Excel cual es esa posición 9 de derecha a izquierda; lo que calcularíamos con la fórmula (=LARGO(celda)-9+1); asi que para extrar los caracteres por la izquierda escribiríamos:
=IZQUIERDA(celda;LARGO(celda)-9+1)
Slds