Kun käsittelet tekstitietoja Excelissä, saatat joutua poistamaan tekstin ennen tiettyä merkkiä tai tekstimerkkijonoa tai sen jälkeen.
Jos sinulla on esimerkiksi ihmisten nimet ja nimitystiedot, voit poistaa nimityksen pilkun jälkeen ja säilyttää vain nimen (tai päinvastoin, jos pidät nimityksen ja poistat nimen).
Joskus se voidaan tehdä yksinkertaisella kaavalla tai pikahaulla ja -korvauksella, ja joskus se vaatii monimutkaisempia kaavoja tai kiertotapoja.
Tässä opetusohjelmassa näytän sinulle, kuinka poistaa teksti ennen tiettyä merkkiä tai sen jälkeen Excelissä (käyttämällä erilaisia esimerkkejä).
Aloitetaan siis muutamalla yksinkertaisella esimerkillä.
Poista teksti merkin jälkeen Etsi ja korvaa -painikkeella
Jos haluat poistaa nopeasti kaiken tekstin tietyn merkkijonon jälkeen (tai ennen tekstimerkkijonoa), voit tehdä sen käyttämällä Etsi ja korvaa -merkkiä ja jokerimerkkejä.
Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat poistaa merkinnän pilkun jälkeen ja säilyttää tekstin ennen pilkkua.
Alla on vaiheet tämän tekemiseen:
- Kopioi ja liitä tiedot sarakkeesta A sarakkeeseen B (tämä säilyttää myös alkuperäiset tiedot)
- Kun sarakkeen B solut on valittu, napsauta Etusivu -välilehteä
- Napsauta Muokkaus -ryhmässä Etsi ja valitse -vaihtoehtoa
- Valitse avattavasta valikosta Korvaa-vaihtoehto. Tämä avaa Etsi ja korvaa -valintaikkunan
- Kirjoita Etsi mitä -kenttään ,* (eli pilkku, jota seuraa tähti)
- Jätä Korvaa -kenttä tyhjäksi
- Napsauta Korvaa kaikki -painiketta
Yllä olevat vaiheet etsivät pilkun tietojoukosta ja poistavat kaiken tekstin pilkun jälkeen (mukaan lukien pilkku).
Koska tämä korvaa valitun solun tekstin, on hyvä kopioida teksti toiseen sarakkeeseen ja suorittaa tämä etsintä- ja korvaustoiminto tai luoda varmuuskopio tiedoistasi, jotta alkuperäiset tiedot säilyvät ennallaan
Miten tämä toimii?
* (tähti) on jokerimerkki, joka voi edustaa mitä tahansa määrää merkkejä.
Kun käytän sitä pilkun jälkeen (Etsi mitä -kentässä) ja napsauta sitten Korvaa kaikki -painiketta, se löytää solun ensimmäisen pilkun ja pitää sitä osumana.
Tämä johtuu siitä, että tähti (*) -merkkiä pidetään osumana koko pilkkua seuraavan tekstimerkkijonon kanssa.
Joten kun painat Korvaa kaikki -painiketta, se korvaa pilkun ja kaiken seuraavan tekstin.
Merkintä: Tämä menetelmä toimii hyvin, jolloin sinulla on vain yksi pilkku jokaisessa solussa (kuten esimerkissämme). Jos sinulla on useita pilkkuja, tämä menetelmä löytää aina ensimmäisen pilkun ja poistaa sitten kaikki sen jälkeen. Joten et voi käyttää tätä menetelmää, jos haluat korvata kaiken tekstin toisen pilkun jälkeen ja pitää ensimmäisen sellaisenaan.Jos haluat poistaa kaikki merkit ennen pilkkua, muuta kenttämerkintää asettamalla tähtimerkki ennen pilkkua (*,*: n sijasta)
Poista teksti kaavojen avulla
Jos haluat enemmän hallita tekstin etsimistä ja korvaamista ennen tiettyä merkkiä tai sen jälkeen, on parempi käyttää Excelin sisäänrakennettuja tekstikaavoja.
Oletetaan, että sinulla on alla oleva tietojoukko Mihin haluat poistaa kaiken tekstin pilkun jälkeen.
Alla on kaava tämän tekemiseen:
= VASEN (A2, ETSI (",", A2) -1)
Yllä oleva kaava käyttää ETSI -funktiota löytääkseen pilkun sijainnin solussa.
VASEN -funktio käyttää tätä sijaintinumeroa poistaakseen kaikki merkit ennen pilkkua. Koska en halua pilkkua osana tulosta, olen vähentänyt yhden Find -kaavan tuloksesta.
Tämä oli yksinkertainen skenaario.
Otetaan hieman monimutkainen.
Oletetaan, että minulla on tämä alla oleva tietojoukko, jossa haluan poistaa kaiken tekstin toisen pilkun jälkeen.
Tässä on kaava, joka tekee tämän:
= VASEN (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", 2))-1)
Koska tässä tietojoukossa on useita pilkkuja, en voi käyttää FIND -funktiota ensimmäisen pilkun sijainnin selvittämiseen ja purkaa kaikki sen vasemmalle puolelle.
Minun täytyy jotenkin selvittää toisen pilkun sijainti ja purkaa sitten kaikki, mikä on toisen pilkun vasemmalla puolella.
Tätä varten olen käyttänyt SUBSTITUTE -funktiota toisen pilkun muuttamiseksi huutomerkiksi. Nyt tämä antaa minulle ainutlaatuisen luonteen solussa. Voin nyt käyttää huutomerkkiä käyttämällä kaiken toisen pilkun vasemmalle puolelle.
Tätä huutomerkkiä käytetään vasemmanpuoleisessa toiminnossa poimimaan kaikki ennen toista pilkkua.
Toistaiseksi niin hyvin!
Mutta entä jos tietojoukossa on epäjohdonmukainen määrä pilkkuja.
Esimerkiksi alla olevassa tietojoukossa joissakin soluissa on kaksi pilkkua ja joissakin soluissa kolme pilkkua, ja minun on purettava kaikki teksti ennen viimeistä pilkkua.
Tässä tapauksessa minun on jotenkin selvitettävä pilkun viimeisen esiintymän sijainti ja purettava sitten kaikki sen vasemmalla puolella.
Alla on kaava, joka tekee sen
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Yllä oleva kaava käyttää LEN -funktiota löytääkseen solun tekstin kokonaispituuden sekä tekstin pituuden ilman pilkkua.
Kun vähennän nämä kaksi arvoa, se antaa minulle solun pilkkujen kokonaismäärän.
Joten se antaisi minulle 3 solulle A2 ja 2 solulle A4.
Tätä arvoa käytetään sitten SUBSTITUTE -kaavassa viimeisen pilkun korvaamiseksi huutomerkillä. Ja sitten voit käyttää vasenta funktiota poimimaan kaiken, mikä on huutomerkin vasemmalla puolella (missä oli viimeinen pilkku)
Kuten esimerkeistä näkyy, tekstikaavojen yhdistelmän avulla voit käsitellä monia erilaisia tilanteita.
Lisäksi koska tulos on linkitetty alkuperäisiin tietoihin, tulos päivittyy automaattisesti, kun muutat alkuperäisiä tietoja.
Poista teksti Flash Fillin avulla
Flash Fill on työkalu, joka otettiin käyttöön Excel 2013: ssa ja joka on saatavana kaikissa versioissa sen jälkeen.
Se toimii tunnistamalla malleja, kun syötät tiedot manuaalisesti ja ekstrapoloit, jolloin saat koko sarakkeen tiedot.
Joten jos haluat poistaa tekstin ennen tiettyä merkkiä tai sen jälkeen, sinun on vain näytettävä flash keijulle, miltä tulos näyttäisi (syöttämällä sen manuaalisesti pari kertaa), ja flash -täyttö ymmärtää automaattisesti kuvion ja antaa sinulle tulokset.
Näytän tämän esimerkin avulla.
Alla on tietojoukko, josta haluan poistaa kaiken tekstin pilkun jälkeen.
Voit tehdä tämän käyttämällä Flash Fill -toimintoa seuraavasti:
- Kirjoita soluun B2, joka on tietojemme viereinen sarake, kirjoita manuaalisesti "Jeffery Haggins" (mikä on odotettu tulos)
- Kirjoita soluun B3 "Tim Scott" (mikä on odotettu tulos toiselle solulle)
- Valitse alue B2: B10
- Napsauta Etusivu -välilehteä
- Napsauta Muokkaus-ryhmässä avattavaa Täytä-vaihtoehtoa
- Napsauta Flash Fill
Yllä olevat vaiheet antavat sinulle alla olevan tuloksen:
Voit myös käyttää Flash Fill -pikanäppäintä Control + E. tulosten sarakkeen solujen valitsemisen jälkeen (esimerkin sarake B)
Flash Fill on loistava työkalu, ja useimmissa tapauksissa se tunnistaa kuvion ja antaa sinulle oikean tuloksen. mutta joissakin tapauksissa se ei ehkä pysty tunnistamaan mallia oikein ja voi antaa sinulle vääriä tuloksia.
Muista siis tarkistaa Flash Fill -tuloksesi uudelleen
Ja aivan kuten olemme poistaneet kaiken tekstin tietyn merkin jälkeen flash -täyttöä käyttämällä, voit samoilla vaiheilla poistaa tekstin ennen tiettyä merkkiä. näytä vain flash -täyttö, miten tuloksen pitäisi näyttää Internetiltäni manuaalisesti viereisessä sarakkeessa, ja se tekisi loput.
Tekstin poistaminen VBA: lla (mukautettu toiminto)
Koko käsite tekstin poistamisesta ennen tiettyä merkkiä tai sen jälkeen riippuu kyseisen merkin sijainnin löytämisestä.
Kuten edellä on nähty, kyseisen merkin viimeisen esiintymän löytäminen hyväksi tarkoittaa kaavojen keksimistä.
Jos tämä on tehtävä usein, voit yksinkertaistaa tätä prosessia luomalla mukautetun toiminnon VBA: lla (nimeltään Käyttäjän määrittämät toiminnot).
Kun olet luonut toiminnon, voit käyttää sitä uudelleen ja uudelleen. Se on myös paljon yksinkertaisempi ja helpompi käyttää (koska suurin osa raskaasta nostosta tehdään takaosan VBA -koodilla).
VBA -koodin alla, jolla voit luoda mukautetun toiminnon Excelissä:
Toiminto LastPosition (rCell As Range, rChar As String) 'Tämä toiminto antaa määritetyn merkin viimeisen sijainnin' Tämän koodin on kehittänyt Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) I = rLen To 1 Vaihe -1 Jos keskitaso (rCell, i - 1, 1) = rChar Sitten LastPosition = i - 1 Lopeta toiminto Lopeta Jos seuraava i Lopeta
Sinun on sijoitettava VBA -koodi tavalliseen moduuliin VB Editorissa tai henkilökohtaiseen makrotyökirjaan. Kun se on siellä, voit käyttää sitä kuin mitä tahansa muuta työkirjan tavallista laskentataulukkotoimintoa.
Tämä funktio vaatii 2 argumenttia:
- Soluviittaus, jolle haluat löytää tietyn merkin tai tekstimerkkijonon viimeisen esiintymän
- Merkki tai tekstijono, jonka sijainti sinun on löydettävä
Oletetaan, että sinulla on nyt alla oleva tietojoukko ja haluat poistaa kaiken tekstin viimeisen pilkun jälkeen ja vain tekstin ennen viimeistä pilkkua.
Alla on kaava, joka tekee sen:
= VASEN (A2, viimeinen sijainti (A2, ",")-1)
Yllä olevassa kaavassa olen määrittänyt etsimään viimeisen pilkun sijainnin. Jos haluat löytää jonkin muun merkin tai tekstimerkkijonon sijainnin, käytä sitä funktion toisena argumenttina.
Kuten näette, tämä on paljon lyhyempi ja helpompi käyttää verrattuna edelliseen osaan käyttämäämme pitkään tekstikaavaan
Jos laitat VBA -koodin työkirjan moduuliin, voit käyttää tätä mukautettua toimintoa vain kyseisessä työkirjassa. Jos haluat käyttää tätä kaikissa järjestelmän työkirjoissa, sinun on kopioitava ja liitettävä tämä koodi henkilökohtaiseen makro -työkirjaan.
Nämä ovat joitain sähköpostiviestejä, joiden avulla voit nopeasti poistaa tekstin ennen tiettyä merkkiä tai sen jälkeen Excelissä.
Jos se on yksinkertainen kertaluonteinen asia, voit tehdä sen käyttämällä Etsi ja korvaa -toimintoa. entä jos se on hieman monimutkaisempi, sinun on käytettävä sisäänrakennettujen Excel -kaavojen yhdistelmää tai jopa luotava oma mukautettu kaava VBA: n avulla.
Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä.
