Pri delu z besedilnimi podatki v Excelu boste morda morali odstraniti besedilo pred ali za določenim znakom ali nizom besedila.
Če imate na primer ime in podatke o označevanju ljudi, lahko oznako odstranite za vejico in ime obdržite le (ali obratno, če obdržite oznako in odstranite ime).
Včasih je to mogoče storiti s preprosto formulo ali hitrim iskanjem in zamenjavo, včasih pa potrebujejo bolj zapletene formule ali rešitve.
V tej vadnici vam bom pokazal, kako odstranite besedilo pred ali po določenem znaku v Excelu (z različnimi primeri).
Zato začnimo z nekaj preprostimi primeri.
Odstranite besedilo po znaku s funkcijo Poišči in zamenjaj
Če želite hitro odstraniti vse besedilo po določenem besedilnem nizu (ali pred besedilnim nizom), lahko to storite s funkcijo Poišči in zamenjaj ter z nadomestnimi znaki.
Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite odstraniti oznako za vejico in obdržati besedilo pred vejico.
Spodaj so navedeni koraki za to:
- Kopirajte in prilepite podatke iz stolpca A v stolpec B (to je tudi za ohranitev izvirnih podatkov)
- Ko so celice v stolpcu B izbrane, kliknite zavihek Domov
- V skupini Urejanje kliknite možnost Najdi in izberi
- V možnostih, ki se prikažejo v spustnem meniju, kliknite možnost Zamenjaj. S tem se odpre pogovorno okno Najdi in zamenjaj
- V polje »Poišči kaj« vnesite ,* (t.j. vejica, ki ji sledi znak zvezdice)
- Polje »Zamenjaj z« pustite prazno
- Kliknite gumb Zamenjaj vse
Zgornji koraki bi našli nabor v naboru podatkov in odstranili vse besedilo za vejico (vključno z vejico).
Ker to nadomešča besedilo iz izbranih celic, je dobro, da besedilo kopirate v drug stolpec in nato izvedete to operacijo iskanja in zamenjave, ali ustvarite varnostno kopijo svojih podatkov, tako da so izvirni podatki nedotaknjeni
Kako to deluje?
* (zvezdica) je nadomestni znak, ki lahko predstavlja poljubno število znakov.
Ko ga uporabim za vejico (v polju »Poišči kaj«) in nato kliknem gumb Zamenjaj vse, najde prvo vejico v celici in meni, da se ujema.
To je zato, ker se zvezdica (*) šteje za ujemanje s celotnim nizom besedila, ki sledi vejici.
Torej, ko pritisnete gumb za zamenjavo vseh, zamenja vejico in vse besedilo, ki sledi.
Opomba: Ta metoda dobro deluje, potem imate v vsaki celici le eno vejico (kot v našem primeru). Če imate več vej, bi ta metoda vedno našla prvo vejico in nato odstranila vse. Zato te metode ne morete uporabiti, če želite zamenjati vse besedilo po drugi vejici in ohraniti prvo takšno, kot je.Če želite odstraniti vse znake pred vejico, spremenite vnos polja poišči, tako da pred vejico postavite zvezdico (*, namesto,*)
Odstranite besedilo s formulami
Če potrebujete več nadzora nad tem, kako poiščete in zamenjate besedilo pred ali po določenem znaku, je bolje uporabiti vgrajene besedilne formule v Excelu.
Recimo, da imate spodnji niz podatkov, kamor želite odstraniti vse besedilo za vejico.
Spodaj je formula za to:
= LEFT (A2, FIND (",", A2) -1)
Zgornja formula uporablja funkcijo FIND za iskanje položaja vejice v celici.
To številko položaja nato funkcija LEFT uporabi za izvlečenje vseh znakov pred vejico. Ker ne želim vejice kot dela rezultata, sem od nastale vrednosti formule Find odštela 1.
To je bil preprost scenarij.
Vzemimo nekoliko zapleteno.
Recimo, da imam spodnji niz podatkov, kjer želim odstraniti vse besedilo po drugi vejici.
Tukaj je formula, ki bo to naredila:
= LEVO (A2, NAJDI ("!", ZAMENJIVA (A2, ",", "!", 2))-1)
Ker ima ta nabor podatkov več vej, funkcije FIND ne morem dobiti, da dobim položaj prve vejice in izvlečem vse levo od nje.
Moram nekako ugotoviti položaj druge vejice in nato izvleči vse, kar je levo od druge vejice.
V ta namen sem s funkcijo SUBSTITUTE drugo vejico spremenil v klicaj. To mi daje edinstven značaj v celici. Zdaj lahko s položajem klicaja izvlečem vse levo od druge vejice.
Ta položaj klicaja se uporablja v funkciji LEFT za izvlečenje vsega pred drugo vejico.
Zaenkrat tako dobro!
Kaj pa, če je v naboru podatkov neskladno število vej.
Na primer, v spodnjem nizu podatkov imajo nekatere celice dve vejici, nekatere celice pa tri vejice, zato moram izvleči vse besedilo pred zadnjo vejico.
V tem primeru moram nekako ugotoviti položaj zadnjega pojavljanja vejice in nato izvleči vse levo od nje.
Spodaj je formula, ki bo to naredila
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Zgornja formula uporablja funkcijo LEN za iskanje celotne dolžine besedila v celici in dolžine besedila brez vejice.
Ko odštejem ti dve vrednosti, dobim skupno število vej v celici.
Torej bi mi dal 3 za celico A2 in 2 za celico A4.
Ta vrednost se nato uporabi v formuli SUBSTITUTE za zamenjavo zadnje vejice s klicajem. Nato lahko z levo funkcijo izvlečete vse, kar je levo od klicaja (tam je bila zadnja vejica)
Kot lahko vidite v primerih, vam uporaba kombinacije besedilnih formul omogoča obvladovanje številnih različnih situacij.
Ker je rezultat povezan z izvirnimi podatki, se bo rezultat samodejno posodobil, ko spremenite izvirne podatke.
Odstranite besedilo z uporabo Flash Fill
Flash Fill je orodje, ki je bilo predstavljeno v Excelu 2013 in je po tem na voljo v vseh različicah.
Deluje tako, da identificira vzorce, ko ročno vnesete podatke in nato ekstrapolirate, da dobite podatke za celoten stolpec.
Torej, če želite odstraniti besedilo pred ali po določenem znaku, morate samo flash flash pokazati, kakšen bi bil rezultat (če ga vnesete nekajkrat ročno), funkcija Flash fill pa bi samodejno razumela vzorec in vam dala Rezultati.
Naj vam to pokažem s primerom.
Spodaj imam niz podatkov, kjer želim odstraniti vse besedilo za vejico.
Tukaj je nekaj korakov za to z uporabo Flash Fill:
- V celico B2, ki je sosednji stolpec naših podatkov, ročno vnesite "Jeffery Haggins" (kar je pričakovani rezultat)
- V celico B3 vnesite "Tim Scott" (to je pričakovani rezultat za drugo celico)
- Izberite obseg B2: B10
- Kliknite zavihek Domov
- V skupini Urejanje kliknite spustni meni Polni
- Kliknite na Flash Fill
Zgornji koraki bi dali rezultat, kot je prikazano spodaj:
Uporabite lahko tudi bližnjico na tipkovnici Flash Fill Control + E po izbiri celic v stolpcu z rezultati (stolpec B v našem primeru)
Flash Fill je čudovito orodje in v večini primerov lahko prepozna vzorec in vam da pravi rezultat. v nekaterih primerih pa morda ne bo mogel pravilno prepoznati vzorca in vam bo dal napačne rezultate.
Zato preverite rezultate Flash Fill
Tako kot smo z določenim znakom s polnilom flash odstranili vse besedilo, lahko z istimi koraki odstranite besedilo pred določenim znakom. samo pokažite flash fill, kako naj bo rezultat videti v mojem internetu v sosednjem stolpcu, in ostalo bo naredilo.
Odstranite besedilo z uporabo VBA (funkcija po meri)
Celoten koncept odstranjevanja besedila pred ali po določenem znaku je odvisen od iskanja položaja tega znaka.
Kot je prikazano zgoraj, ugotoviti, da je zadnji pojav tega znaka dober, pomeni uporabiti kombinacijo formul.
Če morate to početi precej pogosto, lahko ta postopek poenostavite tako, da ustvarite funkcijo po meri z uporabo VBA (imenovane funkcije, ki jih določijo uporabniki).
Ko ustvarite, lahko to funkcijo znova in znova uporabite. Je tudi veliko enostavnejši in lažji za uporabo (saj večino težkega dvigovanja opravi koda VBA na zadnji strani).
Pod kodo VBA, ki jo lahko uporabite za ustvarjanje funkcije po meri v Excelu:
Funkcija LastPosition (rCell As Range, rChar As String) 'Ta funkcija daje zadnji položaj navedenega znaka' To kodo je razvil Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Za i = rLen Na 1 korak -1 Če je sredina (rCell, i - 1, 1) = rChar Potem LastPosition = i - 1 Izhod iz funkcije Konec Naprej Funkcija i Konec
Kodo VBA morate postaviti v običajen modul v urejevalniku VB ali v delovni zvezek osebnega makra. Ko ga imate tam, ga lahko uporabite kot katero koli drugo običajno funkcijo delovnega lista v delovnem zvezku.
Ta funkcija ima 2 argumenta:
- Referenca celice, za katero želite najti zadnji pojav določenega znaka ali besedilnega niza
- Znak ali besedilni niz, katerega položaj morate poiskati
Recimo, da imate zdaj spodnji niz podatkov in želite odstraniti vse besedilo za zadnjo vejico in imeti samo besedilo pred zadnjo vejico.
Spodaj je formula, ki bo to naredila:
= LEFT (A2, LastPosition (A2, ",")-1)
V zgornji formuli sem določil položaj zadnje vejice. Če želite poiskati položaj drugega znakovnega ali besedilnega niza, ga uporabite kot drugi argument v funkciji.
Kot lahko vidite, je to veliko krajše in lažje za uporabo v primerjavi s formulo za dolgo besedilo, ki smo jo uporabili v prejšnjem razdelku
Če kodo VBA vnesete v modul v delovnem zvezku, bi lahko to funkcijo po meri uporabili le v tem delovnem zvezku. Če želite to uporabiti v vseh delovnih zvezkih v vašem sistemu, morate to kodo kopirati in prilepiti v delovni zvezek Personal Macro.
To je torej nekaj e -poštnih sporočil, s katerimi lahko hitro odstranite besedilo pred ali po določenem znaku v Excelu.
Če gre za preprosto enkratno stvar, lahko to storite s funkcijo poišči in zamenjaj. kaj pa, če je nekoliko bolj zapleteno, morate uporabiti kombinacijo vgrajenih Excelovih formul ali celo ustvariti svojo lastno formulo po meri z uporabo VBA.
Upam, da vam je bila ta vadnica koristna.
