Oggi è una domanda folle. Hai una colonna di numeri di parte. Ci sono da 4 a 7 trattini nel numero di parte. Si desidera estrarre solo la parte del numero di parte dopo il primo trattino e fino all'ultimo trattino escluso. Questo è un duello in un episodio di Excel.
Guarda un video
- L'obiettivo è trovare il primo e l'ultimo trattino e mantenere tutto il resto
- La parte difficile qui è trovare l'ultimo trattino
- Metodo di fatturazione 1: riempimento flash
- Compilare manualmente i primi (inclusi alcuni con un numero diverso di trattini)
- Seleziona la cella vuota sotto quella
- Ctrl + E per riempire con flash
- Mike Metodo 2:
- Usa Power Query
- In Excel 2016, Power Query è nel gruppo Ottieni e trasforma in Excel 2016
- In Excel 2010 e 2013, scarica Power Query da Microsoft. Crea una nuova scheda Power Query nella barra multifunzione
- Converti i tuoi dati in una tabella usando Ctrl + T
- Usa i dati divisi in Power Query: prima per dividere sul trattino più a sinistra, quindi per dividere sul trattino più a destra
- Metodo di fatturazione 3:
- Funzione VBA che esegue l'iterazione dalla fine della cella all'indietro per trovare l'ultimo trattino
- Mike Metodo 4:
- Usa SOSTITUISCI per trovare la posizione dell'ennesimo trattino
- SOSTITUISCI è l'unica funzione di testo che ti consente di specificare un numero di istanza
- Per trovare il numero di istanza, utilizzare
=LEN(A2)-LEN(SUBSTITUTE)
Trascrizione del video
Bill: Hey. Ben tornato. È tempo per un altro podcast Dueling Excel. Sono Bill Jelen di MrExcel. (Sarò raggiunto da Mike Girvin di ExcelIsFun. Questo è il nostro - 00:03) episodio 185: estratto dal primo - all'ultimo -.
Tutto a posto. La domanda di oggi è stata inviata da Anvar su YouTube. Come posso estrarre tutto dal primo - all'ultimo - e controllare questi dati che ha qui. Ci sono un numero enorme di trattini, ovunque da 3, 5, 6, 7 trattini, va bene?
Quindi, il mio primo pensiero è, beh, ehi, è davvero facile trovare il primo -, giusto? = sinistra o = METÀ del TROVA di A2 e poi il -, +1 va bene, ma per arrivare all'ultimo -, mi farà male la testa, giusto, perché, beh, quanti trattini abbiamo? Potremmo prendere il SOSTITUTO di A2, sostituendo i trattini, e confrontare la lunghezza di quello, la lunghezza originale. Questo mi dice il numero di trattini, ma ora so quale - trovare, il 2 °, 3 °, 4 °, 5 °, ma uso TROVA?
Ero pronto per passare a VBA, giusto? Questa è la mia reazione istintiva. Ho detto, aspetta un secondo. Ho detto, Anvar, in quale versione di Excel sei? Dice, sono in Excel 2016. Ho detto, è bellissimo. Se utilizzi Excel 2013 o versioni successive, potremmo utilizzare questa nuova fantastica funzionalità chiamata riempimento flash. Con il riempimento flash, dobbiamo solo dargli uno schema, e gli darò uno schema sufficiente, quindi non è solo che ne prendo uno con due trattini e lo faccio un paio di volte. Voglio essere sicuro di avere alcuni trattini diversi in questo modo. Chad nel team di Excel sa cosa sto cercando. Chad è il ragazzo che ha scritto la logica per il riempimento flash. Quindi, ne ottengo circa 3 lì e poi CONTROL + E è la scorciatoia per usare DATA e poi FLASH FILL, e, abbastanza sicuro, sembra che abbia fatto la cosa giusta. Va bene, Mike.Vediamo cosa hai.
Mike: Grazie, MrExcel. Si. Il riempimento flash vince. Quella caratteristica proprio lì, il riempimento flash, è uno dei moderni strumenti di Excel che è semplicemente sorprendente. Se è un contratto una tantum e hai uno schema coerente, ehi, è così che lo farei.
Ehi, passiamo al foglio successivo. Ora, invece di utilizzare il riempimento flash, possiamo effettivamente utilizzare la query di alimentazione. Ora sto usando Excel 2016, quindi ho il gruppo GET & TRANSFORM. Questa è una domanda di potere. Nelle versioni precedenti, 2013 (fino a 10 - 2:30), in realtà è necessario scaricare il componente aggiuntivo power query gratuito.
Ora, per far funzionare la query di alimentazione, questo deve essere convertito in una tabella Excel. Ora, di nuovo, utilizzerei il riempimento flash se fosse un affare una tantum. Quando useresti la query di alimentazione? Bene, se avessi davvero big data o provenissi da una fonte esterna, questa sarebbe la strada da percorrere, o potresti anche apprezzare meglio che dover digitare 3 o 4 esempi per il riempimento flash perché, con la query di alimentazione, possiamo in particolare dì trova il primo e trova l'ultimo.
Ora lo convertirò in una tabella Excel. Ho una singola cella selezionata, celle vuote tutto intorno. Vado su INSERT, TABLE, o usi la tastiera, CONTROL + T. Posso fare clic su OK o INVIO. Voglio dare un nome a questa tabella, quindi salirò su STRUMENTI TABELLA, PROGETTAZIONE, su PROPRIETÀ. Lo chiamerò STARTKEYTABLE e INVIO. Ora posso tornare a DATA, portarlo in power query utilizzando il pulsante FROM TABLE. C'è la mia colonna. C'è il nome. Non voglio mantenere questo nome perché l'output verrà esportato in Excel e voglio assegnargli un nome diverso. Quindi, lo chiamerò CLEANEDKEYTABLE. Non ho bisogno di quel TIPO CAMBIATO. Sto solo guardando la fonte. Ora posso fare clic sulla colonna e, proprio in HOME, c'è il pulsante SPLIT. Posso dire SPLIT, BY DELIMITER. Sembra che sia già stato indovinato. IO'sto per dire LEFT-MOST. Fare clic su OK.
Ora, se guardo qui vedo TIPO MODIFICATO. Non ne ho bisogno, quindi mi sbarazzerò di quel passaggio. Ho solo SPLIT COLUMN BY DELIMITER. Ora, lo farò di nuovo ma, invece di usare il pulsante SPLIT qui sopra, fai clic con il pulsante destro del mouse su SPLIT COLUMN, BY DELIMITER, e guarda quello. Possiamo scegliere di dividerlo per il DELIMITATORE PIÙ DESTRO. Fare clic su OK. Ora, non ho bisogno di queste due colonne, quindi farò clic con il pulsante destro del mouse sulla colonna che voglio mantenere, RIMUOVI ALTRE COLONNE. In realtà ho intenzione di X questo TIPO CAMBIATO. Dirà SEI SICURO DI VOLER ELIMINARE QUESTO? Dirò, sì, CANCELLA. Ci sono i miei dati puliti.
Ora posso salire su CLOSE & LOAD. CHIUDI E CARICA A. Questa è la nuova finestra di dialogo IMPORTA. Era solito dire CARICA A ma voglio caricarlo su una tabella, su un FOGLIO DI LAVORO ESISTENTE. Fare clic sul pulsante di compressione. Selezionerò C1, aprirò, fare clic su OK e il gioco è fatto. Power query per pulire i nostri dati e ottenere solo i dati desiderati. Tutto a posto. Lo rimetterò a.
Bill: Ecco il punto, il DELIMITATORE PIÙ DESTRA nella COLONNA DIVISA PER DELIMITORE, una delle caratteristiche interessanti di Power query. È fantastico.
Tutto a posto. La mia reazione istintiva - VBA UDF (incomprensibile - 05:34) davvero facile da fare VBA. Passa a ALT + F11. INSERISCI un MODULO. In quel modulo, digita questo codice. Vado a (creare una - 05:43) nuova funzione, la chiamerò MIDPART e le passerò del testo, quindi quello che farò è andando dall'ultimo carattere in quella cella dalla lunghezza di MYTEXT indietro a 1, PASSO -1 e guarda quel carattere. Quindi, il MID di MYTEXT, quella variabile i, ci dice quale carattere stiamo guardando per la lunghezza di 1. È un -? Non appena trovo un -, prenderò la SINISTRA di MIO TESTO a partire dal carattere i - 1, quindi mi sbarazzo di tutto per quest'ultimo - fino in fondo, e poi, assicurati di non andare continua a cercare altri trattini, EXIT FOR mi farà uscire da questo ciclo (incomprensibile - 06:17),e da lì è la parte facile. Stiamo solo per prendere il MYTEXT, iniziare da MID di MYTEXT, (dove io uso - 06:26) usare la funzione TROVA per trovare il primo -, andare 1 in più e restituirlo indietro.
Quindi, torniamo indietro, ALT + Q, per tornare a Excel. = Scheda MIDPART di quello, e sembra che funzioni. Copia quello. Mike, ne hai un altro? (= MIDPart (A2))
Mike: Beh, ne ho un'altra, ma sarà una formula lunga, non breve come quella UDF. Va bene, passiamo al foglio successivo. Ora, se stiamo per fare una formula e abbiamo del testo e ci sono sempre un numero diverso di delimitatori, in qualche modo, ho bisogno di ottenere la posizione di quell'ultimo delimitatore.
Ora, questo richiederà alcuni passaggi, ma inizierò con la funzione SOSTITUISCI. Guarderò quel testo,, il vecchio testo che voglio trovare è ", quello -, e cosa voglio mettere al suo posto o sostituire? "". Ciò non inserirà nulla. Ora, se io) e CONTROL + INVIO, cosa succederà? (= SOSTITUISCI (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.
Bene, eccoti. Voglio ringraziare tutti per essere passati. Ci vediamo la prossima volta per un altro podcast Dueling Excel da ed ExcelIsFun.
Download file
Scarica il file di esempio qui: Duel185.xlsm
