Excelil on TEXT -funktsioonide komplekt, mis võib imet teha. Nende funktsioonide abil saate teha igasuguseid tekstilõike ja täringut.
Tekstiandmetega töötavate inimeste üks levinumaid ülesandeid on Excelis alamstringi ekstraheerimine (st teksti lahtri psrt hankimine).
Kahjuks pole Excelis alamstringi funktsiooni, mis seda hõlpsalt teeks. Seda saab siiski teha tekstivalemite ja mõne muu Exceli sisseehitatud funktsiooni abil.
Vaatame kõigepealt mõningaid tekstifunktsioone, mida selles õpetuses kasutame.
Exceli teksti funktsioonid
Excelil on mitmeid tekstifunktsioone, mis hõlbustaksid alamstringi ekstraheerimist Exceli algsest tekstist. Siin on Exceli teksti funktsioonid, mida selles õpetuses kasutame:
- Funktsioon PAREM: ekstraheerib tekstistringi paremalt poolt määratud arvu tähemärke.
- Funktsioon LEFT: ekstraheerib määratud stringi vasakult tekstimärkide arvu.
- Funktsioon MID: ekstraheerib määratud stringide arvu määratud stringi tekstistringist.
- Funktsioon FIND: otsib tekstistringist määratud teksti lähtepositsiooni.
- Funktsioon LEN: tagastab tekstistringi märkide arvu.
Extract Exceli alamstringi funktsioonide abil
Oletame, et teil on allpool näidatud andmekogum:
Need on mõned juhuslikud (kuid superkangelasega) e-posti ID-d (välja arvatud minu oma) ja alltoodud näidetes näitan teile, kuidas kasutajanime ja domeeninime Exceli tekstifunktsioonide abil ekstraheerida.
Näide 1 - kasutajanimede väljavõtmine e -posti aadressidest
Tekstifunktsioonide kasutamisel on oluline tuvastada muster (kui see on olemas). See muudab valemi koostamise väga lihtsaks. Ülaltoodud juhul on muster @ märk kasutajanime ja domeeninime vahel ning kasutame seda kasutajanimede saamiseks viitena.
Siin on valem kasutajanime saamiseks:
= LEFT (A2, FIND ("@", A2) -1)
Ülaltoodud valem kasutab kasutajanime eraldamiseks funktsiooni LEFT, tuvastades @ -märgi asukoha id -is. Seda tehakse funktsiooni FIND abil, mis tagastab @positsiooni.
Näiteks [email protected] puhul tagastaks FIND (“@”, A2) 11, mis on selle asukoht tekstistringis.
Nüüd kasutame funktsiooni LEFT, et eraldada stringist vasakult 10 märki (üks vähem kui funktsiooni LEFT tagastatav väärtus).
Näide 2 - Domeeninime väljavõtmine e -posti aadressidest
Sama loogikat, mida kasutati ülaltoodud näites, saab kasutada domeeninime saamiseks. Väike erinevus on see, et peame tekstistringi paremalt tähemärgid välja võtma.
Siin on valem, mis seda teeb:
= PAREM (A2, LEN (A2) -FIND ("@", A2))
Ülaltoodud valemis kasutame sama loogikat, kuid kohandage seda, et veenduda, et saame õige stringi.
Võtame taas näite [email protected]. Funktsioon FIND tagastab @ märgi asukoha, mis on antud juhul 11. Nüüd peame välja võtma kõik märgid pärast @. Seega tuvastame stringi kogupikkuse ja lahutame märkide arvu kuni @. See annab meile tähemärkide arvu, mis katavad paremal asuva domeeninime.
Nüüd saame domeeni nime saamiseks kasutada funktsiooni PAREM.
Näide 3 - Domeeninime väljavõtmine e -posti aadressidest (ilma .comita)
Teksti stringi keskelt alamstringi eraldamiseks peate tuvastama markeri asukoha vahetult enne ja pärast alamstringi.
Näiteks allolevas näites oleks domeeninime saamiseks ilma .com osata marker @ (mis on vahetult domeeninime ees) ja. (mis on kohe pärast seda).
Siin on valem, mis eraldab ainult domeeninime:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Funktsioon Excel MID ekstraheerib määratud lähtepositsioonist määratud arvu märke. Selles ülaltoodud näites määrab FIND (“@”, A2) +1 lähtepositsiooni (mis on kohe pärast@) ja FIND (“.”, A2) -FIND (“@”, A2) -1 tuvastab tähemärkide arvu vahel@' ja '.‘
Värskendus: Üks lugejatest William19 mainis, et ülaltoodud valem ei tööta, kui e -posti aadressis on punkt (.) (Näiteks [email protected]). Nii et siin on valem selliste juhtumite käsitlemiseks:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Teksti kasutamine veergudeks Exceli alamstringi ekstraheerimiseks
Funktsioonide kasutamine Exceli alamstringi eraldamiseks on dünaamiline. Kui muudate algteksti, värskendab valem tulemusi automaatselt.
Kui seda ei pruugi vaja minna, võib funktsiooni Tekst veergudele kasutamine olla kiire ja lihtne viis teksti jaotamiseks alamstringideks, mis põhinevad määratud markeritel.
Siin on, kuidas seda teha.
- Valige lahtrid, kus teil on tekst.
- Avage Andmed -> Andmetööriistad -> Tekst veergudesse.
- Teksti veergu viisardis 1. sammus valige Eraldatud ja vajutage nuppu Edasi.
- 2. sammus märkige suvand Muu ja sisestage selle paremasse kasti @. See on meie eraldaja, mida Excel kasutaks teksti alamstringideks jagamiseks. Andmete eelvaadet näete allpool. Klõpsake nuppu Edasi.
- Kolmandas etapis töötab üldine seade sel juhul hästi. Kui aga jagate numbreid/kuupäevi, saate valida teise vormingu. Vaikimisi on sihtrakk see, kus teil on algandmed. Kui soovite esialgsed andmed puutumatuna hoida, muutke see mõnele muule lahtrile.
- Klõpsake nuppu Lõpeta.
See annab teile koheselt kaks alamstringi komplekti iga selles näites kasutatud e -posti ID kohta.
Kui soovite teksti veelgi jagada (näiteks jagada batman.com batmaniks ja comiks), korrake sama protsessi.
FIND ja REPLACE kasutamine Exceli lahtrist teksti ekstraheerimiseks
FIND ja REPLACE võib olla võimas tehnika, kui töötate Excelis tekstiga. Allolevates näidetes saate teada, kuidas kasutada FIND ja REPLACE koos metamärkidega, et teha Excelis hämmastavaid asju.
Vaata ka: Lisateave Exceli metamärkide kohta.
Võtame samad näited e -posti aadresside kohta.
Näide 1 - kasutajanimede väljavõtmine e -posti aadressidest
Siin on sammud kasutajanimede eemaldamiseks e -posti aadressidest, kasutades funktsiooni Otsing ja asendamine.
- Kopeerige ja kleepige algsed andmed. Kuna funktsioon Otsi ja asenda töötab ja muudab andmeid, mille kohta seda rakendatakse, on parem varundada algsed andmed.
- Valige andmed ja minge avalehele -> redigeerimine -> otsi ja vali -> asenda (või kasutage kiirklahvi Ctrl + H).
- Sisestage dialoogiboksis Otsi ja asenda järgmine:
- Leia mida: @*
- Asenda: (jäta see tühjaks)
- Klõpsake nuppu Asenda kõik.
See eemaldab koheselt kogu teksti, mis asub enne @ -d e -posti aadressides. Saate tulemuse, nagu allpool näidatud:
Kuidas see toimib ?? - Ülaltoodud näites oleme kasutanud kombinatsiooni @ ja *. Tärn (*) on metamärk, mis tähistab suvalist arvu tähemärke. Seega @* tähendaks tekstistringi, mis algab @ -ga ja mille järel võib olla suvaline arv tähemärke. Näiteks aadressil [email protected] oleks @* @batman.com. Kui asendame @* tühjaga, eemaldab see kõik tähed pärast @(sh @).
Näide 2 - Domeeninime väljavõtmine e -posti aadressidest
Sama loogikat kasutades saate domeeni nime saamiseks muuta kriteeriume „Leia”.
Siin on sammud.
- Valige andmed.
- Minge avalehele -> redigeerimine -> otsi ja vali -> asenda (või kasutage kiirklahvi Ctrl + H).
- Sisestage dialoogiboksis Otsi ja asenda järgmine:
- Leia mida: *@
- Asenda: (jäta see tühjaks)
- Klõpsake nuppu Asenda kõik.
See eemaldab koheselt kogu teksti, mis asub enne @ -d e -posti aadressides. Saate tulemuse, nagu allpool näidatud:
