Excelis tekstiandmetega töötamisel võib tekkida vajadus eemaldada tekst enne või pärast konkreetset märki või tekstistringi.
Näiteks kui teil on inimeste nimed ja tähistusandmed, võiksite eemaldada tähise pärast koma ja jätta alles nimi (või vastupidi, kus hoiate tähist ja eemaldate nime).
Mõnikord saab seda teha lihtsa valemi või kiire leidmise ja asendamisega ning mõnikord vajab see keerukamaid valemeid või lahendusi.
Selles õpetuses näitan teile, kuidas eemaldada Excelis konkreetse märgi ees või järel olev tekst (kasutades erinevaid näiteid).
Nii et alustame mõne lihtsa näitega.
Eemaldage tekst pärast tähemärki, kasutades funktsiooni Otsing ja asendamine
Kui soovite kiiresti eemaldada kogu teksti pärast kindlat tekstistringi (või enne tekstistringi), saate seda teha, kasutades otsi ja asenda ning metamärke.
Oletame, et teil on allpool näidatud andmekogum ja soovite eemaldada tähise pärast koma ja jätta tekst koma ette.
Allpool on toodud sammud selle tegemiseks.
- Kopeerige ja kleepige andmed veerust A veergu B (see võimaldab säilitada ka algseid andmeid)
- Kui veeru B lahtrid on valitud, klõpsake vahekaarti Avaleht
- Klõpsake rühmas Redigeerimine valikut Otsi ja vali
- Klõpsake rippmenüüs kuvatavate suvandite valikul Asenda. See avab dialoogiboksi Otsi ja asenda
- Sisestage väljale „Otsi mida” ,* (st koma, millele järgneb tärn)
- Jätke väli „Asenda” tühjaks
- Klõpsake nuppu Asenda kõik
Ülaltoodud sammud leiaksid andmekogust koma ja eemaldaksid kogu teksti pärast koma (sh koma).
Kuna see asendab valitud lahtrite teksti, on hea mõte kopeerida tekst teise veergu ja seejärel teha see otsimis- ja asendustoiming või luua oma andmetest varukoopia, et algsed andmed oleksid terved
Kuidas see töötab?
* (tärn) on metamärk, mis võib tähistada suvalist arvu märke.
Kui kasutan seda pärast koma (väljal „Otsi mida”) ja seejärel klõpsan nupul Asenda kõik, leiab see lahtrist esimese koma ja peab seda vasteks.
Selle põhjuseks on asjaolu, et tärni (*) märki peetakse vasteks kogu komale järgnevale tekstistringile.
Nii et kui vajutate nuppu Asenda kõik, asendab see koma ja kogu järgneva teksti.
Märge: See meetod töötab hästi, siis on igas lahtris ainult üks koma (nagu meie näites). Kui teil on mitu koma, leiaks see meetod alati esimese koma ja eemaldaks pärast seda kõik. Nii et te ei saa seda meetodit kasutada, kui soovite kogu teksti pärast teist koma asendada ja esimese jätta sellisena.Kui soovite eemaldada kõik koma ees olevad tähemärgid, muutke välja sisestamise leidmiseks täht, märkides koma ette tärni (*, mitte*)
Teksti eemaldamine valemite abil
Kui vajate rohkem kontrolli selle üle, kuidas teksti leida või asendada enne või pärast konkreetset märki, on parem kasutada Excelis sisseehitatud tekstivalemeid.
Oletame, et teil on allpool olev andmekogum, kuhu soovite koma järel kogu teksti eemaldada.
Allpool on valem selle tegemiseks:
= LEFT (A2, FIND (",", A2) -1)
Ülaltoodud valem kasutab funktsiooni FIND, et leida koma asukoht lahtris.
Seda positsiooni numbrit kasutab seejärel funktsioon VASAK, et eraldada kõik märgid enne koma. Kuna ma ei soovi tulemuse osana koma, lahutasin valemi Find tulemuse väärtusest 1.
See oli lihtne stsenaarium.
Võtame natuke keerulise.
Oletame, et mul on see allpool olev andmekogum, kus ma tahan kogu teksti pärast teist koma eemaldada.
Siin on valem, mis seda teeb:
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", 2))-1)
Kuna sellel andmekogul on mitu koma, ei saa ma funktsiooni FIND kasutada esimese koma asukoha määramiseks ja sellest vasakule jääva väljavõtmiseks.
Pean kuidagi välja selgitama teise koma asukoha ja seejärel ekstraheerima kõik, mis on teisest komast vasakul.
Selleks olen kasutanud funktsiooni SUBSTITUTE, et muuta teine koma hüüumärgiks. Nüüd annab see mulle lahtris ainulaadse iseloomu. Nüüd saan kasutada hüüumärgi asendit, et eraldada kõik teisest koma vasakule.
Seda hüüumärgi asendit kasutatakse funktsioonis LEFT, et eraldada kõik enne teist koma.
Siiamaani on kõik korras!
Aga mis siis, kui andmekogus on ebaühtlane arv komasid.
Näiteks allpool olevas andmekogumis on mõnel lahtril kaks koma ja mõnel lahtril kolm koma ning ma pean kogu teksti enne viimast koma välja võtma.
Sellisel juhul pean ma kuidagi välja selgitama koma viimase esinemise positsiooni ja seejärel ekstraheerima kõik sellest vasakul.
Allpool on valem, mis seda teeb
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (ASENDUS (A2, ",", ""))))-1)
Ülaltoodud valem kasutab funktsiooni LEN, et leida lahtris oleva teksti üldpikkus ja teksti pikkus ilma koma.
Kui ma need kaks väärtust lahutan, annab see mulle lahtris olevate komade koguarvu.
Niisiis, see annaks mulle lahtri A2 jaoks 3 ja lahtri A4 jaoks 2.
Seda väärtust kasutatakse seejärel asendaja valemis, et asendada viimane koma hüüumärgiga. Ja siis saate vasakut funktsiooni kasutades ekstraheerida kõik, mis asub hüüumärgist vasakul (kus oli viimane koma)
Nagu näitest näete, võimaldab tekstivalemite kombinatsiooni kasutamine lahendada paljusid erinevaid olukordi.
Samuti, kuna tulemus on lingitud algandmetega, värskendatakse tulemust automaatselt, kui muudate algseid andmeid.
Eemaldage tekst välktäite abil
Flash Fill on tööriist, mis võeti kasutusele programmis Excel 2013 ja mis on pärast seda saadaval kõigis versioonides.
See toimib mustrite tuvastamisel, kui sisestate andmed käsitsi ja ekstrapoleerite, et saada teile kogu veeru andmed.
Nii et kui soovite teksti eemaldada enne või pärast konkreetset tähemärki, peate lihtsalt näitama flash haldjale, kuidas tulemus välja näeks (sisestades selle paar korda käsitsi) ja välktäide mõistaks mustrit automaatselt ja annaks teile tulemused.
Näitan teile seda näitega.
Allpool on mul andmekogum, kust soovin eemaldada kogu teksti pärast koma.
Siin on sammud, kuidas seda Flash Filli abil teha:
- Lahtrisse B2, mis on meie andmete külgne veerg, sisestage käsitsi „Jeffery Haggins” (see on oodatud tulemus)
- Lahtrisse B3 sisestage "Tim Scott" (see on teise lahtri oodatav tulemus)
- Valige vahemik B2: B10
- Klõpsake vahekaarti Avaleht
- Klõpsake rühmas Redigeerimine rippmenüüd Täida
- Klõpsake nuppu Flash Fill
Ülaltoodud sammud annavad teile tulemuse, nagu on näidatud allpool:
Võite kasutada ka kiirklahvi Flash Fill Control + E. pärast tulemuste veeru lahtrite valimist (meie näite veerg B)
Flash Fill on suurepärane tööriist ja enamikul juhtudel suudab see mustri ära tunda ja anda teile õige tulemuse. kuid mõnel juhul ei pruugi see mustrit õigesti ära tunda ja võib anda teile valesid tulemusi.
Nii et kontrollige kindlasti oma Flash Fill tulemusi
Ja nagu me oleme välktäite abil eemaldanud kogu teksti pärast teatud märki, saate samade toimingute abil eemaldada teksti enne konkreetset märki. lihtsalt näidake välktäites, kuidas tulemus peaks minu Internetis käsitsi välja nägema kõrvalolevas veerus, ja see teeks ülejäänu.
Teksti eemaldamine VBA abil (kohandatud funktsioon)
Kogu teksti eemaldamise mõiste enne või pärast konkreetset märki sõltub selle märgi asukoha leidmisest.
Nagu ülal näha, tähendab selle tegelase hea esinemise leidmine hea valemite koostamist.
Kui peate seda sageli tegema, saate seda protsessi lihtsustada, luues kohandatud funktsiooni, kasutades VBA -d (nn kasutaja määratud funktsioonid).
Kui olete selle funktsiooni loonud, saate seda uuesti ja uuesti kasutada. Seda on ka palju lihtsam ja hõlpsam kasutada (kuna suurema osa rasketest tõstmistest teeb tagaosas olev VBA -kood).
Allpool VBA -koodi, mida saate kasutada Excelis kohandatud funktsiooni loomiseks:
Funktsioon LastPosition (rCell As Range, rChar As String) "See funktsioon annab määratud märgi viimase positsiooni" Selle koodi on välja töötanud Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) I = rLen 1. sammule -1 Kui keskmine (rCell, i - 1, 1) = rChar Siis LastPosition = i - 1 Välju funktsioonist Lõpp Kui järgmine i Lõppfunktsioon
Peate paigutama VBA koodi VB redaktori tavalisse moodulisse või isikliku makro töövihikusse. Kui see on seal olemas, saate seda kasutada töövihiku mis tahes muu tavalise töölehe funktsioonina.
See funktsioon võtab 2 argumenti:
- Lahtri viide, mille kohta soovite leida konkreetse märgi või tekstistringi viimast esinemist
- Märk või tekstistring, mille asukoha peate leidma
Oletame, et teil on nüüd alltoodud andmekogum ja soovite eemaldada kogu teksti pärast viimast koma ning alles on tekst enne viimast koma.
Allpool on valem, mis seda teeb:
= LEFT (A2, LastPosition (A2, ",")-1)
Ülaltoodud valemis olen määranud viimase koma asukoha leidmise. Kui soovite leida mõne muu märgi või tekstistringi positsiooni, peaksite seda funktsiooni teise argumendina kasutama.
Nagu näete, on see palju lühem ja hõlpsamini kasutatav, võrreldes eelmise tekstivalemiga, mida kasutasime eelmises jaotises
Kui panete VBA -koodi töövihiku moodulisse, saate seda kohandatud funktsiooni kasutada ainult selles konkreetses töövihikus. Kui soovite seda kasutada kõigis oma süsteemi töövihikutes, peate selle koodi kopeerima ja kleepima isikliku makro töövihikusse.
Nii et need on mõned kirjad, mida saate kasutada teksti kiireks eemaldamiseks enne või pärast konkreetset tähemärki Excelis.
Kui see on lihtne ühekordne asi, saate seda teha otsingu ja asendamise abil. mis siis, kui see on natuke keerulisem, siis peate kasutama sisseehitatud Exceli valemite kombinatsiooni või isegi looma oma kohandatud valemi VBA abil.
Loodan, et leidsite selle õpetuse kasulikuks.
