Tänään on hullu kysymys. Sinulla on sarake osanumeroista. Osanumerossa on missä tahansa 4-7 viivaa. Haluat purkaa vain osanumeron osan ensimmäisen viivan jälkeen ja viimeiseen viivaan asti, mutta ei sitä. Tämä on kaksintaistelu Excel-jakso.
Katso video
- Tavoitteena on löytää ensimmäinen ja viimeinen viiva ja pitää kaikki välissä
- Vaikeinta tässä on löytää viimeinen viiva
- Bill-menetelmä 1: Flash-täyttö
- Täytä ensimmäiset muutamat manuaalisesti (mukaan lukien jotkut eri viivamäärillä)
- Valitse sen alapuolella oleva tyhjä solu
- Ctrl + E Flash-täyttöön
- Mike-menetelmä 2:
- Käytä virtakyselyä
- Excel 2016: ssa Power Query on Excel 2016: n Hae ja muunna -ryhmässä
- Lataa Excel 2010 ja 2013 Power Query Microsoftilta. Se luo uuden Power Query -välilehden nauhaan
- Muunna tietosi taulukkoon painamalla Ctrl + T
- Käytä Jaettu data Power Query -sovelluksessa - ensin jakamaan vasemmassa reunassa ja sitten jakamaan oikeassa reunassa
- Laskutapa 3:
- VBA-toiminto, joka toistaa solun päästä taaksepäin viimeisen viivan löytämiseksi
- Mike-menetelmä 4:
- Käytä SUBSTITUTE löytääksesi N: n viivan sijainnin
- SUBSTITUTE on ainoa tekstitoiminto, jonka avulla voit määrittää ilmentymän numeron
- Löydät ilmentymän numeron käyttämällä
=LEN(A2)-LEN(SUBSTITUTE)
Videon transkriptio
Bill: Hei. Tervetuloa takaisin. On aika uudelle Dueling Excel-podcastille. Olen Bill Jelen MrExcelistä. (Minuun liittyy Mike Girvin ExcelIsFunista. Tämä on meidän - 00:03) jakso 185: ote ensimmäisestä - viimeiseen -.
Hyvä on. Tämän päivän kysymyksen lähetti Anvar YouTubessa. Kuinka voin poimia kaiken ensimmäisestä - viimeiseen - ja tarkistaa nämä hänen täällä olevat tiedot. Viivoja on valtava määrä, missä tahansa 3, 5, 6, 7 viivaa, okei?
Joten ensimmäinen ajatukseni on, no, hei, on todella helppo löytää ensimmäinen - eikö? = vasemmalle tai = A2: n LÄHETETTY keskiarvo ja sitten -, +1 kunnossa, mutta päästäkseen viimeiseen - se saa pään satuttamaan, oikea, koska no, kuinka monta viivaa meillä on? Voisimme ottaa A2: n VAIHTEEN korvaamalla viivat ja verrata sen pituutta alkuperäiseen pituuteen. Se kertoo minulle viivojen lukumäärän, mutta nyt tiedän, mikä - löytää, toinen, kolmas, neljäs, viides, mutta käytänkö Etsit?
Olin valmis menemään VBA: lle, eikö? Se on minun polvi-ääliöreaktioni. Sanoin, odota hetki. Sanoin, Anvar, missä Excel-versiossa olet? Hän sanoo, että olen Excel 2016. Sanoin, että on kaunista. Jos olet Excel 2013: ssa tai uudemmassa, voimme käyttää tätä upeaa uutta ominaisuutta nimeltä flash fill. Flash-täytöllä meidän on vain annettava sille kuvio, ja aion antaa sille tarpeeksi kuviota, joten ei ole vain, että otan yhden kahdella viivalla ja teen sen pari kertaa. Haluan varmistaa, että minulla on muutama erilainen viiva tällä tavalla. Tšad Excel-tiimissä tietää mitä etsin. Tšad on kaveri, joka kirjoitti flash-täytteen logiikan. Joten saan noin 3 heistä sinne ja sitten CONTROL + E on pikakuvake DATA: n ja sitten FLASH FILL: n käyttämiseen, ja tosiaan näyttää siltä, että se teki oikein. Selvä, Mike.Katsotaanpa, mitä sinulla on.
Mike: Kiitos, MrExcel. Joo. Flash-täyttö voittaa. Tämä ominaisuus, flash-täyttö, on yksi moderneista Excel-työkaluista, joka on yksinkertaisesti hämmästyttävä. Jos se on kertaluonteinen sopimus ja sinulla on johdonmukainen malli, hei, niin tekisin sen.
Hei, mennään seuraavaan arkkiin. Nyt flash-täytön käyttämisen sijaan voimme todella käyttää tehokyselyä. Käytän nyt Excel 2016: ta, joten minulla on GET & TRANSFORM -ryhmä. Se on tehokysely. Aikaisemmissa versioissa, 2013 (klo 10–2:30), sinun on todella ladattava ilmainen virtakyselylisäosa.
Nyt, jotta tehokysely toimisi, se on muunnettava Excel-taulukoksi. Nyt taas kerran käytän flash-täyttöä, jos tämä olisi kertaluonteinen sopimus. Milloin käyttäisit virtakyselyä? No, jos sinulla olisi todella suuria tietoja tai tulisit ulkoisesta lähteestä, tämä olisi oikea tapa edetä, tai saatat jopa pitää tästä paremmin kuin sinun tarvitse kirjoittaa 3 tai 4 esimerkkiä flash-täytteelle, koska tehokyselyllä voimme sanoa erityisesti löytää ensimmäinen - ja löytää viimeinen.
Muunan tämän nyt Excel-taulukoksi. Minulla on valittu yksi solu, tyhjät solut ympäriinsä. Siirry kohtaan INSERT, TABLE tai käytät näppäimistöä, CONTROL + T. Voin napsauttaa OK tai ENTER. Haluan nimetä tämän taulukon, joten siirryn PÖYTÄTYÖKALUIIN, SUUNNITTELU, ylös OMINAISUUKSIIN. Aion soittaa tälle STARTKEYTABLE ja ENTER. Nyt voin palata DATA-tilaan ja tuoda sen virtakyselyyn FROM TABLE -painikkeella. Siinä on sarakkeeni. Siinä on nimi. En halua säilyttää tätä nimeä, koska tulos viedään Exceliin ja haluan antaa sille toisen nimen. Joten kutsun sitä CLEANEDKEYTABLE. En tarvitse sitä MUUTTUNUT TYYPPIÄ. Katson vain lähdettä. Nyt voin napsauttaa saraketta ja heti HOME: ssa on SPLIT-painike. Voin sanoa SPLIT, DELIMITER. Näyttää siltä, että se on jo arvattu. Minä 'sanon VASEN-ENNEN. Napsauta OK.
Jos katson täältä, näen MUUTTUNEEN TYYPPIIN. En tarvitse sitä, joten aion päästä eroon siitä vaiheesta. Minulla on vain SPLIT COLUMN BY DELIMITER. Aion tehdä tämän uudelleen, mutta sen sijaan, että käytän SPLIT-painiketta täällä, napsauta hiiren kakkospainikkeella alas SPLIT COLUMN, BY DELIMITER, ja katso sitä. Voimme halutessasi jakaa sen OIKEALLA-POISIMMALLA. Napsauta OK. En nyt tarvitse näitä kahta saraketta, joten aion napsauttaa hiiren kakkospainikkeella saraketta, jonka haluan pitää, POISTA MUUT SARAKKEET. Menen itse asiassa X: ään tämän MUUTTUNEEN TYYPPIIN. Se sanoo, oletko varma, että haluat poistaa tämän? Sanon kyllä, POISTA. Siellä on puhtaat tietoni.
Nyt voin tulla KIINNI & LATAA. SULJE JA LASI. Tämä on uusi TUONTI-valintaikkuna. Aikaisemmin sanottiin LOAD TO, mutta haluan ladata sen pöydälle, OLEMASSA olevalle TYÖLOMAKEELLE. Napsauta tiivistä-painiketta. Aion valita C1, purkaa, napsauta OK, ja mennään. Tehokysely tietojen puhdistamiseksi ja vain haluamiesi tietojen saamiseksi. Hyvä on. Heitän sen takaisin.
Bill: Siellä on asia, OIKEASIMMAN RAJOITIN SPLIT COLUMN BY DELIMITER -ohjelmassa, joka on yksi virtakyselyn hienoista ominaisuuksista. Se on mahtavaa.
Hyvä on. Polvi-ääliöreaktioni - VBA UDF (käsittämätön - 05:34) todella helppo tehdä VBA. Vaihda tilaan ALT + F11. ASENNA MODUULI. Kirjoita kyseiseen moduuliin tämä koodi. Aion (luoda - 05:43) upouuden toiminnon, kutsun sitä MIDPARTiksi ja välitän sille jonkin tekstin, ja sitten aion tehdä siirrytään solun viimeisestä merkistä MYTEXT: n pituudesta takaisin kohtaan 1, VAIHE -1 ja katsotaan kyseistä merkkiä. Joten MYTEXTIN MID, tämä muuttuja i, kertoo meille, mitä merkkiä katsomme 1: n pituudelle. Onko se -? Heti kun löydän -, otan VASEN MYTEXT: n alkaen merkistä i - 1, joten pääsen eroon kaikesta viimeiseksi - aina ulos ja varmista sitten, etten mene Etsitkö lisää viivoja, EXIT FOR vie minut pois tästä (käsittämätön - 06:17) silmukasta,ja sieltä on helppo osa. Otamme vain MYTEXT: n, aloitamme MYTEXT: n MID: stä, (missä käytän - 06:26), etsi funktio ETSI löytääksesi ensimmäisen -, menemme 1 enemmän ja palautamme sen takaisin.
Palataan siis takaisin, ALT + Q, palataksesi Exceliin. = Sen MIDPART-välilehti, ja näyttää siltä, että se toimii. Kopioi se alas. Mike, onko sinulla toinen? (= MIDPart (A2))
Mike: No, minulla on toinen, mutta se tulee olemaan yksi pitkä kaava - ei niin lyhyt kuin tuo UDF. Selvä, siirrytään seuraavaan taulukkoon. Jos aiomme tehdä kaavan ja meillä on jonkin verran tekstiä, ja rajaimia on aina eri määrä, minun on jotenkin saatava viimeisen erottimen sijainti.
Nyt tämä vie muutaman askeleen, mutta aloitan SUBSTITUTE-toiminnolla. Aion selata tätä tekstiä,, vanha teksti, jonka haluan löytää, on ”, siinä -, ja mitä haluan laittaa sen tilalle tai korvata? "". Se ei aseta mitään. Jos minä) ja CONTROL + ENTER, mitä se tekee? (= SUBSTITUTE (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.
No, sinä menet. Haluan kiittää kaikkia pysähtymisestä. Nähdään ensi kerralla uudelle Dueling Excel-podcastille ja ExcelIsFunilta.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Duel185.xlsm
