Kai dirbate su „Excel“ teksto duomenimis, gali tekti pašalinti tekstą prieš arba po tam tikro simbolio ar teksto eilutės.
Pvz., Jei turite žmonių vardų ir pavadinimų duomenis, galbūt norėsite pašalinti žymėjimą po kablelio ir palikti tik vardą (arba atvirkščiai, kai pasiliekate žymėjimą ir pašalinate vardą).
Kartais tai galima padaryti naudojant paprastą formulę arba greitą paiešką ir pakeitimą, o kartais reikia sudėtingesnių formulių ar problemų sprendimo būdų.
Šioje pamokoje parodysiu, kaip pašalinti tekstą prieš arba po tam tikro „Excel“ simbolio (naudojant skirtingus pavyzdžius).
Taigi pradėkime nuo kelių paprastų pavyzdžių.
Pašalinkite tekstą po simbolio naudodami „Find and Replace“
Jei norite greitai pašalinti visą tekstą po tam tikros teksto eilutės (arba prieš teksto eilutę), galite tai padaryti naudodami „Find and Replace“ ir „wild card“ simbolius.
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite pašalinti žymėjimą po kablelio ir palikti tekstą prieš kablelį.
Žemiau pateikiami žingsniai, kaip tai padaryti:
- Nukopijuokite ir įklijuokite duomenis iš A stulpelio į B stulpelį (taip bus išsaugoti ir pradiniai duomenys)
- Pasirinkę B stulpelio langelius, spustelėkite skirtuką Pagrindinis
- Redagavimo grupėje spustelėkite parinktį Rasti ir pasirinkti
- Išskleidžiamajame meniu rodomose parinktyse spustelėkite parinktį Pakeisti. Bus atidarytas dialogo langas Rasti ir pakeisti
- Lauke „Rasti ką“ įveskite ,* (t. y. kablelis, po kurio eina žvaigždutė)
- Palikite lauką „Pakeisti“ tuščią
- Spustelėkite mygtuką Pakeisti viską
Atliekant aukščiau nurodytus veiksmus, duomenų rinkinyje surandamas kablelis ir pašalinamas visas tekstas po kablelio (įskaitant kablelį).
Kadangi tai pakeičia pasirinktų langelių tekstą, patartina nukopijuoti tekstą į kitą stulpelį, tada atlikti šią paieškos ir pakeitimo operaciją arba sukurti atsarginę duomenų kopiją, kad originalūs duomenys būtų nepažeisti
Kaip tai veikia?
* (žvaigždutė) yra pakaitos simbolis, kuris gali reikšti bet kokį simbolių skaičių.
Kai naudoju jį po kablelio (lauke „Rasti ką“) ir paspaudžiu mygtuką Keisti viską, jis randa pirmąjį kablelį langelyje ir laiko jį atitiktimi.
Taip yra todėl, kad žvaigždutė (*) laikoma atitiktimi visai teksto eilutei, einančiai po kablelio.
Taigi, kai paspaudžiate mygtuką „Keisti viską“, jis pakeičia kablelį ir visą toliau pateikiamą tekstą.
Pastaba: Šis metodas veikia gerai, tada kiekvienoje langelyje yra tik vienas kablelis (kaip mūsų pavyzdyje). Jei turite kelis kablelius, šis metodas visada suras pirmąjį kablelį ir po jo pašalins viską. Taigi negalite naudoti šio metodo, jei norite pakeisti visą tekstą po antrojo kablelio ir palikti pirmąjį tokį, koks yra.Jei norite pašalinti visus simbolius prieš kablelį, pakeiskite laukelio įrašą, pažymėdami žvaigždutės ženklą prieš kablelį (*, o ne,*)
Pašalinkite tekstą naudodami formules
Jei jums reikia daugiau kontroliuoti, kaip rasti ir pakeisti tekstą prieš arba po tam tikro simbolio, geriau naudoti integruotas teksto formules programoje „Excel“.
Tarkime, kad turite žemiau esantį duomenų rinkinį, kuriame norite pašalinti visą tekstą po kablelio.
Žemiau yra formulė, kaip tai padaryti:
= LEFT (A2, FIND (",", A2) -1)
Aukščiau pateikta formulė naudoja FIND funkciją, kad surastų kablelio vietą ląstelėje.
Tada šį pozicijos numerį naudoja funkcija LEFT, kad išgautų visus simbolius prieš kablelį. Kadangi nenoriu kablelio kaip rezultato dalies, iš „Find“ formulės gautos vertės atėmiau 1.
Tai buvo paprastas scenarijus.
Paimkime šiek tiek sudėtingą.
Tarkime, kad turiu šį duomenų rinkinį, kuriame noriu pašalinti visą tekstą po antrojo kablelio.
Čia yra formulė, kuri tai padarys:
= LEFT (A2, FIND ("!", PAKAITA (A2, ",", "!", 2))-1)
Kadangi šiame duomenų rinkinyje yra keli kableliai, negaliu naudoti FIND funkcijos, kad gaučiau pirmojo kablelio padėtį ir ištraukčiau viską į kairę.
Turiu kažkaip išsiaiškinti antrojo kablelio padėtį ir tada ištraukti viską, kas yra antrojo kablelio kairėje.
Norėdami tai padaryti, naudoju funkciją SUBSTITUTE, kad pakeistumėte antrąjį kablelį į šauktuką. Dabar tai suteikia man unikalų charakterį ląstelėje. Dabar galiu naudoti šauktuko poziciją, kad ištraukčiau viską į kairę nuo antrojo kablelio.
Ši šauktuko padėtis naudojama funkcijoje LEFT, norint išgauti viską prieš antrąjį kablelį.
Kol kas viskas gerai!
Bet ką daryti, jei duomenų rinkinyje yra nenuoseklus kablelių skaičius.
Pavyzdžiui, žemiau esančiame duomenų rinkinyje kai kuriose ląstelėse yra du kableliai, o kai kuriose - trys kableliai, ir aš turiu ištraukti visą tekstą prieš paskutinį kablelį.
Šiuo atveju turiu kažkaip išsiaiškinti paskutinio kablelio atsiradimo padėtį ir tada išgauti viską kairėje.
Žemiau yra formulė, kuri tai padarys
= LEFT (A2, FIND ("!", PAKAITA (A2, ",", "!", LEN (A2) -LEN (PAKAITA (A2, ",", ""))))-1)
Aukščiau pateikta formulė naudoja LEN funkciją, kad surastų bendrą teksto ilgį langelyje ir teksto ilgį be kablelio.
Kai atimu šias dvi vertes, tai parodo bendrą kablelių skaičių langelyje.
Taigi, man duotų 3 langeliui A2 ir 2 - langeliui A4.
Tada ši vertė naudojama formulėje SUBSTITUTE, kad paskutinis kablelis būtų pakeistas šauktuku. Tada galite naudoti kairę funkciją ištraukti viską, kas yra šauktuko kairėje (kur buvo paskutinis kablelis)
Kaip matote pavyzdžiuose, naudodami teksto formulių derinį, galite susidoroti su daugybe skirtingų situacijų.
Be to, kadangi rezultatas yra susietas su pradiniais duomenimis, pasikeitus pradiniams duomenims, rezultatas bus automatiškai atnaujinamas.
Pašalinkite tekstą naudodami „Flash Fill“
„Flash Fill“ yra įrankis, pristatytas programoje „Excel 2013“ ir po to pasiekiamas visomis versijomis.
Jis veikia nustatant šablonus, kai rankiniu būdu įvedate duomenis, o po to ekstrapoliuojate, kad gautumėte viso stulpelio duomenis.
Taigi, jei norite pašalinti tekstą prieš arba po tam tikro simbolio, jums tiesiog reikia parodyti „flash fairy“, kaip atrodytų rezultatas (porą kartų jį įvedus rankiniu būdu), o „flash fill“ automatiškai suprastų modelį ir suteiktų jums rezultatai.
Leiskite man tai parodyti pavyzdžiu.
Žemiau turiu duomenų rinkinį, kuriame noriu pašalinti visą tekstą po kablelio.
Štai kaip tai padaryti naudojant „Flash Fill“:
- B2 langelyje, kuris yra gretimas mūsų duomenų stulpelis, rankiniu būdu įveskite „Jeffery Haggins“ (tai yra laukiamas rezultatas)
- B3 langelyje įveskite „Tim Scott“ (tai yra laukiamas antrojo langelio rezultatas)
- Pasirinkite diapazoną B2: B10
- Spustelėkite skirtuką Pagrindinis
- Redagavimo grupėje spustelėkite išskleidžiamąją parinktį Užpildyti
- Spustelėkite „Flash Fill“
Pirmiau minėti veiksmai suteiks jums rezultatą, kaip parodyta žemiau:
Taip pat galite naudoti sparčiuosius klavišus „Flash Fill“ „Control“ + E. pasirinkę langelius rezultatų stulpelyje (mūsų pavyzdžio B stulpelis)
„Flash Fill“ yra puikus įrankis ir daugeliu atvejų jis gali atpažinti modelį ir suteikti jums tinkamą rezultatą. tačiau kai kuriais atvejais jis gali neatpažinti modelio ir gali duoti neteisingus rezultatus.
Taigi būtinai dar kartą patikrinkite „Flash Fill“ rezultatus
Ir kaip mes pašalinome visą tekstą po tam tikro simbolio naudodami „flash“ užpildymą, galite atlikti tuos pačius veiksmus, kad pašalintumėte tekstą prieš konkretų simbolį. tiesiog parodykite „flash fill“, kaip rezultatas turėtų atrodyti kaip mano internetas rankiniu būdu gretimame stulpelyje, o visa kita atliks.
Pašalinkite tekstą naudodami VBA (pasirinktinė funkcija)
Visa teksto pašalinimo prieš arba po tam tikro simbolio koncepcija priklauso nuo to simbolio padėties nustatymo.
Tarsi būtų matyti aukščiau, rasti paskutinį to simbolio įvykį - tai naudoti formulių kompoziciją.
Jei tai turite padaryti gana dažnai, galite supaprastinti šį procesą sukurdami pasirinktinę funkciją naudodami VBA (vadinamą vartotojo apibrėžtomis funkcijomis).
Sukūrę šią funkciją galite vėl ir vėl naudoti. Tai taip pat daug paprasčiau ir lengviau naudoti (nes didžiąją dalį sunkių kėlimų atlieka VBA kodas gale).
Žemiau VBA kodas, kurį galite naudoti kurdami pasirinktinę funkciją „Excel“:
Funkcija „LastPosition“ („rCell As Range“, „rChar As String“) „Ši funkcija suteikia paskutinę nurodyto simbolio vietą“ Šį kodą sukūrė Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Jei = i
Turite įdėti VBA kodą į įprastą modulį VB redaktoriuje arba asmeninėje makrokomandos darbaknygėje. Kai jį turėsite, galėsite jį naudoti kaip bet kurią kitą įprastą darbalapio funkciją.
Ši funkcija turi 2 argumentus:
- Langelio nuoroda, kuriai norite rasti paskutinį konkretaus simbolio ar teksto eilutės įvykį
- Simbolis arba teksto eilutė, kurios vietą reikia rasti
Tarkime, kad dabar turite žemiau pateiktą duomenų rinkinį ir norite pašalinti visą tekstą po paskutinio kablelio, o tekstą - tik prieš paskutinį kablelį.
Žemiau yra formulė, kuri tai padarys:
= LEFT (A2, LastPosition (A2, ",")-1)
Aukščiau pateiktoje formulėje nurodžiau rasti paskutinio kablelio padėtį. Jei norite rasti kito simbolio ar teksto eilutės padėtį, turėtumėte tai naudoti kaip antrą funkcijos argumentą.
Kaip matote, tai yra daug trumpiau ir lengviau naudoti, palyginti su ilgo teksto formule, kurią naudojome ankstesniame skyriuje
Jei įdėtumėte VBA kodą į darbo knygos modulį, šią pasirinktinę funkciją galėtumėte naudoti tik toje konkrečioje darbaknygėje. Jei norite tai naudoti visose sistemos darbaknygėse, turite nukopijuoti ir įklijuoti šį kodą į asmeninę makrokomandos darbaknygę.
Taigi tai yra keletas laiškų, kuriuos galite naudoti norėdami greitai pašalinti tekstą prieš arba po tam tikro „Excel“ simbolio.
Jei tai paprastas vienkartinis dalykas, tai galite padaryti naudodami funkciją „Rasti ir pakeisti“. Ką daryti, jei jis yra šiek tiek sudėtingesnis, tuomet turite naudoti integruotų „Excel“ formulių derinį arba net sukurti savo pasirinktinę formulę naudodami VBA.
Tikiuosi, kad ši pamoka jums buvo naudinga.
