Hoy es una pregunta loca. Tiene una columna de números de pieza. Hay entre 4 y 7 guiones en el número de pieza. Desea extraer solo la parte del número de pieza después del primer guión y hasta el último guión, pero sin incluirlo. Este es un episodio de duelo de Excel.
Ver video
- El objetivo es encontrar el primer y último guión y mantener todo en el medio
- La parte difícil aquí es encontrar el último guión.
- Método de factura 1: Relleno instantáneo
- Complete manualmente los primeros (incluidos algunos con diferentes números de guiones)
- Seleccione la celda en blanco debajo de eso
- Ctrl + E para flash de relleno
- Método 2 de Mike:
- Usar Power Query
- En Excel 2016, Power Query está en el grupo Obtener y transformar en Excel 2016
- En Excel 2010 y 2013, descargue Power Query de Microsoft. Crea una nueva pestaña Power Query en la cinta
- Convierta sus datos en una tabla usando Ctrl + T
- Use datos divididos en Power Query: primero para dividir en el guión más a la izquierda, luego dividir en el guión más a la derecha
- Método de facturación 3:
- Función VBA que itera desde el final de la celda hacia atrás para encontrar el último guión
- Método 4 de Mike:
- Use SUBSTITUTE para encontrar la ubicación del enésimo guión
- SUSTITUIR es la única función de texto que le permite especificar un número de instancia
- Para encontrar qué número de instancia, use
=LEN(A2)-LEN(SUBSTITUTE)
Transcripción del video
Bill: Oye. Dar una buena acogida. Es hora de otro podcast de Dueling Excel. Soy Bill Jelen de MrExcel. (Me acompañará Mike Girvin de ExcelIsFun. Este es nuestro - 00:03) episodio 185: extracto desde el primero - hasta el último -.
Bien. Anvar envía la pregunta de hoy en YouTube. ¿Cómo puedo extraer todo, desde el primero, hasta el último, y ver estos datos que tiene aquí? Hay una gran cantidad de guiones, desde 3, 5, 6, 7 guiones, ¿de acuerdo?
Entonces, mi primer pensamiento es, bueno, oye, es muy fácil encontrar el primero, ¿verdad? = izquierda o = MID del FIND de A2 y luego -, +1 bien, pero para llegar al último -, eso va a hacer que me duela la cabeza, correcto, porque, bueno, ¿cuántos guiones tenemos? Podríamos tomar el SUSTITUTO de A2, reemplazando los guiones, y comparar la longitud de eso, la longitud original. Eso me dice el número de guiones, pero ahora sé cuál: encontrar el segundo, tercer, cuarto, quinto, pero ¿uso FIND?
Estaba listo para ir a VBA, ¿verdad? Esa es mi reacción instintiva. Dije, espera un segundo. Le dije: Anvar, ¿en qué versión de Excel estás? Él dice, estoy en Excel 2016. Dije, eso es hermoso. Si está en Excel 2013 o más reciente, podríamos usar esta gran característica nueva llamada relleno flash. Con flash fill, solo tenemos que darle un patrón, y voy a darle un patrón suficiente para que no sea solo que tome uno con dos guiones y lo haga un par de veces. Quiero asegurarme de tener algunos guiones diferentes de esa manera. Chad, del equipo de Excel, sabe lo que estoy buscando. Chad es el tipo que escribió la lógica del relleno rápido. Entonces, obtengo alrededor de 3 de ellos allí y luego CONTROL + E es el atajo para usar DATA y luego FLASH FILL y, efectivamente, parece que hizo lo correcto. Muy bien, Mike.Veamos que tienes.
Mike: Gracias, MrExcel. Si. El relleno flash gana. Esa función, flash fill, es una de las herramientas modernas de Excel que es simplemente asombrosa. Si es un trato único y tienes un patrón constante, bueno, así es como lo haría yo.
Oye, pasemos a la siguiente hoja. Ahora, en lugar de usar flash fill, podemos usar Power query. Ahora, estoy usando Excel 2016, así que tengo el grupo GET & TRANSFORM. Esa es la consulta de poder. En versiones anteriores, 2013 (de 10 a 2:30), en realidad debe descargar el complemento de consulta de energía gratuito.
Ahora, para que funcione la consulta de energía, esto debe convertirse a una tabla de Excel. Ahora, de nuevo, usaría flash fill si este fuera un trato único. ¿Cuándo usarías Power query? Bueno, si tuvieras un gran volumen de datos o vinieras de una fuente externa, este sería el camino a seguir, o incluso podría gustarte más que tener que escribir 3 o 4 ejemplos para flash fill porque, con Power query, podemos diga específicamente encontrar el primero y encontrar el último.
Ahora, voy a convertir esto en una tabla de Excel. Tengo una sola celda seleccionada, celdas vacías en todos los sentidos. Voy a INSERTAR, TABLA, o usas el teclado, CONTROL + T. Puedo hacer clic en Aceptar o ENTRAR. Quiero nombrar esta tabla, así que iré a HERRAMIENTAS DE MESA, DISEÑO, a PROPIEDADES. Voy a llamar a esto STARTKEYTABLE y ENTER. Ahora puedo volver a DATA, llevarlo a Power query usando el botón FROM TABLE. Ahí está mi columna. Ahí está el nombre. No quiero mantener este nombre porque la salida se exportará a Excel y quiero darle un nombre diferente. Entonces, lo llamaré CLEANEDKEYTABLE. No necesito ese TIPO CAMBIADO. Solo estoy mirando la fuente. Ahora puedo hacer clic en la columna y, justo en INICIO, está el botón DIVIDIR. Puedo decir SPLIT, BY DELIMITER. Parece que ya lo adivinó. YO'Voy a decir LEFT-MOST. Haga clic en Aceptar.
Ahora, si miro hacia aquí, veo TIPO CAMBIADO. No necesito eso, así que voy a deshacerme de ese paso. Solo tengo COLUMNA DIVIDIDA POR DELIMITADOR. Ahora, voy a hacer esto de nuevo pero, en lugar de usar el botón DIVIDIR aquí arriba, haga clic derecho hacia abajo hasta DIVIDIR COLUMNA, POR DELIMITADOR, y mire eso. Podemos optar por dividirlo por el DELIMITADOR MÁS DERECHO. Haga clic en Aceptar. Ahora, no necesito estas dos columnas, así que voy a hacer clic derecho en la columna que quiero mantener, ELIMINAR OTRAS COLUMNAS. De hecho, voy a X este TIPO CAMBIADO. Dirá ¿ESTÁ SEGURO DE QUE DESEA BORRAR ESTO? Voy a decir, sí, BORRAR. Ahí están mis datos limpios.
Ahora puedo subir a CLOSE & LOAD. CERRAR Y CARGAR A. Este es el nuevo cuadro de diálogo IMPORTAR. Solía decir LOAD TO pero quiero cargarlo en una tabla, en una HOJA DE TRABAJO EXISTENTE. Haga clic en el botón contraer. Voy a seleccionar C1, deshacer el colapso, hacer clic en Aceptar y listo. Power query para limpiar nuestros datos y obtener solo los datos que queremos. Bien. Se lo devolveré.
Bill: Ahí está el punto, DELIMITADOR MÁS A LA DERECHA en la COLUMNA DIVIDIDA POR DELIMITADOR, una de las características interesantes de la consulta de energía. Eso es genial.
Bien. Mi reacción instintiva: VBA UDF (ininteligible - 05:34) realmente fácil de hacer VBA. Cambia a ALT + F11. INTRODUZCA UN MÓDULO. En ese módulo, escriba este código. Voy a (crear una - 05:43) función completamente nueva, la voy a llamar MIDPART, y le voy a pasar algo de texto, y luego lo que voy a hacer es ir desde el último carácter en esa celda desde la longitud de MYTEXT hasta 1, PASO -1 y mirar ese carácter. Entonces, el MID de MYTEXT, esa variable i, nos dice qué carácter estamos buscando para una longitud de 1. ¿Es un -? Tan pronto como encuentre un -, voy a tomar la IZQUIERDA de MYTEXT comenzando en el carácter i - 1, así que me deshago de todo para el último - hasta el final, y luego, me aseguro de no ir sigue buscando más guiones, EXIT FOR me sacará de este bucle (ininteligible - 06:17),y de ahí viene la parte fácil. Solo vamos a tomar MYTEXT, comenzar en el MID de MYTEXT, (donde uso el - 06:26) usar la función FIND para encontrar el primero -, ir 1 más que eso y devolverlo.
Entonces, regresemos, ALT + Q, para regresar a Excel. = Pestaña MIDPART de eso, y parece que está funcionando. Copia eso. Mike, ¿tienes otro? (= MIDPart (A2))
Mike: Bueno, tengo otra, pero será una fórmula larga, no tan corta como esa UDF. Muy bien, pasemos a la siguiente hoja. Ahora, si vamos a hacer una fórmula y tenemos algo de texto y siempre hay un número diferente de delimitadores, de alguna manera, necesito obtener la posición del último delimitador.
Ahora, esto tomará algunos pasos, pero comenzaré con la función SUSTITUIR. Voy a revisar ese texto, el texto antiguo que quiero encontrar está en ”, ese -, y ¿qué quiero poner en su lugar o sustituirlo? “”. Eso no pondrá nada. Ahora, si yo) y CONTROL + ENTER, ¿qué va a hacer eso? (= SUSTITUIR (A2, “-”, “”))
Well, now I can take the length of this and subtract it from the length of this item. That will tell me how many delimiters there are. F2, and right at the beginning, I'm going to type the length of that. That will give me the full length - the length of that dashless text, ), CONTROL+ENTER, double click, and send it down. that tells me how many delimiters there are for this text. There are 6. (=LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))
Now I'm going to use that sixth now inside of substitute to put a different character right at the sixth listing of the delimiter, F2, and if I type SUBSTITUTE, what we want to notice is this function has an instance number. If you look at other text functions like search and find, they don't have an instance number. Substitute is the only one I can think of that actually lets you specifically say which instance of a delimiter you want to deal with. Here's the text, ,. Old text is in “ a -, and I need to pick for the new text some character that will never be in this text ring. I'm going to choose, like, or something like that, , and that's where instance number comes in, ), CONTROL+ENTER, and there it is. If I double click and send it down, it's always putting that in the position of the last delimiter. (=SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))
Now I need to figure out, in each one of these, what position it is in. F2. I'm going to use the SEARCH function. SEARCH. I type S and tab. Now, search and find are the same except for search is not case-sensitive. In this case, either one would be fine because the text I'm looking for is in “, that ^, ”, , within that text. By the way, the reason that I use search instead of find is because S tab gets me search but F I tab will get me find. So, it's like one character less when typing it out. CONTROL+ENTER, double click and send it down, and now it tells me, in the 27th position is that last delimiter. (=SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))))
Now, I'm going to take this approach for these text items. I'm now going to use the left function and get everything from the very beginning all the way up to that position. That will get rid of that last little bit. Now, actually, search tells us 27 which is right there and we only want to go to 26. So, F2, and, at the end, I'm going to - 1, CONTROL+ENTER, double click and send it down. Now, I can use the left function. F2. LEFT. There it is, left of that, ,. That's how many characters. ), CONTROL+ENTER, double click and send it down. So, now, we have gotten rid of the last little bit after the last delimiter in every cell. (=LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1))
Now all I need to do is replace the first four characters, first four characters, first three characters. Now, I can use the search function on the original text because it can find the - which is three and I'll tell replace, please go, from the first character, three characters in and replace it with nothing, F2, and right at the beginning, I'm going to type REPLACE. There's the old text. Now watch this. I want to give myself a little bit more breathing room. I'm just going to artificially pick a space, ALT+ENTER. That's kind of like we do in DAX. Now I just have more breathing room. That's the old text, ,. The starting number, I need to always start at the first position so I simply type 1, , and I need to find that first - which represents number of characters. So, S tab, “-” , through… within that text, that search will find 4, 4, 3. That will work. ) and then , new text “”. That will put nothing in those first characters. ). I have the entire column highlighted so I can populate this edited formula with CONTROL+ENTER, and there we go. All the way down, we’re extracting everything between the first and the last -. (=REPLACE(LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1),1,SEARCH(“-”,A2),“”))
Now, the only reason we want to be crazy like that with formulas is if we wanted the formula result to instantly update whenever we changed anything, so if I type -00, instantly it updates. Power query and flash fill will not automatically update, alright? Send it back to.
Bill: Well, that was one heck of a formula. Like, substitute was the trick. I had used substitute in the first step but didn't see that it had the instance number. Alright, so, we have four different methods here today. My first method is flash fill. Select first few, select the blank box below that, and then CONTROL+E to flash fill. Mike's method, use power query. I love that, especially the split data letting you use the leftmost - and then the rightmost -. My live seminars always talk about this one feature. Should be a finalist for the Nobel Prize for the best excel feature. It wouldn't win but it would be in one of the top five, I'm sure. My method number three, VBA function, a UDF user-defined function, that iterates from the end of the cell, and then, Mike's method, the awesome formula method. Use substitute to find the location of the nth - and then pass that answer back into substitute that tells you which instance number to look from. Brilliant.
Bueno, allá vas. Quiero agradecerles a todos por pasar. Nos vemos la próxima vez para otro podcast de Dueling Excel de y ExcelIsFun.
Descargar archivo
Descarga el archivo de muestra aquí: Duel185.xlsm
