Prilikom rada s tekstualnim podacima u Excelu možda ćete morati ukloniti tekst prije ili poslije određenog znaka ili tekstualnog niza.
Na primjer, ako imate ime i podatke o označavanju ljudi, možda ćete htjeti ukloniti oznaku iza zareza i zadržati samo ime (ili obrnuto ako zadržite oznaku i uklonite ime).
Ponekad se to može učiniti jednostavnom formulom ili brzim načinom Nađi i zamijeni, a ponekad su potrebne složenije formule ili zaobilazna rješenja.
U ovom vodiču ću vam pokazati kako ukloniti tekst prije ili poslije određenog znaka u Excelu (koristeći različite primjere).
Pa krenimo s nekoliko jednostavnih primjera.
Uklonite tekst nakon znaka pomoću značajke Pronađi i zamijeni
Ako želite brzo ukloniti sav tekst nakon određenog tekstualnog niza (ili prije tekstualnog niza), to možete učiniti pomoću značajke Pronađi i zamijeni te zamjenskih znakova.
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite ukloniti oznaku iza znaka zareza i zadržati tekst ispred zareza.
U nastavku su navedeni koraci za to:
- Kopirajte i zalijepite podatke iz stupca A u stupac B (ovo služi i za čuvanje izvornih podataka)
- S odabranim ćelijama u stupcu B kliknite karticu Početna
- U grupi Uređivanje kliknite opciju Pronađi i odaberi
- U opcijama koje se pojavljuju u padajućem izborniku kliknite opciju Zamijeni. Ovo će otvoriti dijaloški okvir Pronađi i zamijeni
- U polje "Pronađi što" unesite ,* (tj. zarez iza kojeg slijedi znak zvjezdice)
- Ostavite polje "Zamijeni s" prazno
- Pritisnite gumb Zamijeni sve
Gore navedeni koraci će pronaći zarez u skupu podataka i ukloniti sav tekst iza zareza (uključujući zarez).
Budući da ovo zamjenjuje tekst iz odabranih ćelija, dobro je kopirati tekst u drugi stupac, a zatim izvršiti ovu operaciju pronalaženja i zamjene, ili stvoriti sigurnosnu kopiju svojih podataka tako da izvorni podaci budu netaknuti
Kako ovo radi?
* (znak zvjezdice) je zamjenski znak koji može predstavljati bilo koji broj znakova.
Kad ga koristim nakon zareza (u polju ‘Pronađi što’), a zatim pritisnem gumb Zamijeni sve, on pronalazi prvi zarez u ćeliji i smatra ga podudaranjem.
To je zato što se znak zvjezdica (*) smatra podudaranjem sa cijelim nizom teksta koji slijedi zarez.
Dakle, kada pritisnete gumb zamijeni sve, on zamjenjuje zarez i sav tekst koji slijedi.
Bilješka: Ova metoda dobro funkcionira, tada imate samo jedan zarez u svakoj ćeliji (kao u našem primjeru). U slučaju da imate više zareza, ova metoda će uvijek pronaći prvi zarez, a zatim ukloniti sve iza njega. Dakle, ne možete koristiti ovu metodu ako želite zamijeniti sav tekst nakon drugog zareza, a prvi zadržati onakvim kakav jest.U slučaju da želite ukloniti sve znakove ispred zareza, promijenite unos polja pronađi koje polje stavljajući zvjezdicu ispred zareza (*, umjesto,*)
Uklonite tekst pomoću formula
Ako vam je potrebna veća kontrola kako pronaći i zamijeniti tekst prije ili poslije određenog znaka, bolje je koristiti ugrađene tekstualne formule u Excelu.
Pretpostavimo da imate donji skup podataka Gdje želite ukloniti sav tekst iza zareza.
Ispod je formula za to:
= LIJEVO (A2, FIND (",", A2) -1)
Gornja formula koristi funkciju FIND za pronalaženje položaja zareza u ćeliji.
Ovaj broj pozicije zatim koristi funkcija LIJEVO za izdvajanje svih znakova ispred zareza. Budući da ne želim zarez kao dio rezultata, oduzeo sam 1 od rezultirajuće vrijednosti formule Find.
Ovo je bio jednostavan scenarij.
Uzmimo jednu malo složeniju.
Pretpostavimo da imam ovaj skup podataka u nastavku u kojem želim ukloniti sav tekst nakon drugog zareza.
Evo formule koja će to učiniti:
= LIJEVO (A2, FIND ("!", ZAMJENA (A2, ",", "!", 2))-1)
Budući da ovaj skup podataka ima više zareza, ne mogu koristiti funkciju FIND za dobivanje pozicije prve zareze i izdvajanje svega lijevo od nje.
Moram nekako saznati položaj drugog zareza, a zatim izdvojiti sve što je lijevo od drugog zareza.
Da bih to učinio, upotrijebio sam funkciju ZAMJENA da promijenim drugi zarez u uskličnik. Ovo mi daje jedinstven lik u ćeliji. Sada mogu koristiti položaj uskličnika za izdvajanje svega lijevo od drugog zareza.
Ovaj položaj uskličnika koristi se u funkciji LIJEVO za izdvajanje svega prije drugog zareza.
Zasada je dobro!
No što ako u skupu podataka postoji nedosljedan broj zareza.
Na primjer, u donjem skupu podataka neke ćelije imaju dvije zareze, a neke ćelije tri zareze, a ja moram izdvojiti sav tekst prije zadnjeg zareza.
U ovom slučaju moram nekako shvatiti poziciju posljednje pojave zareza, a zatim izdvojiti sve lijevo od nje.
Ispod je formula koja će to učiniti
= LIJEVO (A2, NAĐI ("!", ZAMJENA (A2, ",", "!", LEN (A2) -LEN (ZAMJENA (A2, ",", ""))))-1)
Gornja formula koristi funkciju LEN za pronalaženje ukupne duljine teksta u ćeliji, kao i duljine teksta bez zareza.
Kad oduzmem ove dvije vrijednosti, dobivam ukupan broj zareza u ćeliji.
Dakle, dao bih 3 za ćeliju A2 i 2 za ćeliju A4.
Ta se vrijednost zatim koristi unutar formule ZAMJENA za zamjenu zadnjeg zareza uskličnikom. A zatim možete upotrijebiti lijevu funkciju za izdvajanje svega što je lijevo od uskličnika (gdje je nekada bio zadnji zarez)
Kao što možete vidjeti u primjerima, upotreba kombinacije tekstualnih formula omogućuje vam rješavanje mnogih različitih situacija.
Također, budući da je rezultat povezan s izvornim podacima, kada promijenite izvorne podatke, rezultat bi se automatski ažurirao.
Uklonite tekst pomoću Flash Fill -a
Flash Fill je alat koji je predstavljen u programu Excel 2013, a nakon toga je dostupan u svim verzijama.
Djeluje tako da identificira obrasce kada ručno unesete podatke, a zatim ih ekstrapolirate kako biste dobili podatke za cijeli stupac.
Dakle, ako želite ukloniti tekst prije ili poslije određenog znaka, samo trebate pokazati flash vilici kako bi izgledao rezultat (unošenjem ručno nekoliko puta), a bljeskalica bi automatski razumjela uzorak i dala vam Rezultati.
Dopustite mi da vam to pokažem primjerom.
Ispod imam skup podataka u kojem želim ukloniti sav tekst iza zareza.
Evo koraka za to pomoću Flash Fill -a:
- U ćeliju B2, koja je susjedni stupac naših podataka, ručno unesite "Jeffery Haggins" (što je očekivani rezultat)
- U ćeliju B3 unesite "Tim Scott" (što je očekivani rezultat za drugu ćeliju)
- Odaberite raspon B2: B10
- Kliknite karticu Početna
- U grupi Uređivanje kliknite padajuću opciju Popuni
- Kliknite na Flash Fill
Gore navedeni koraci dali bi vam rezultat kao što je prikazano u nastavku:
Također možete koristiti tipkovnički prečac Flash Fill Control + E nakon odabira ćelija u stupcu rezultata (stupac B u našem primjeru)
Flash Fill je prekrasan alat i u većini slučajeva može prepoznati uzorak i dati vam pravi rezultat. ali u nekim slučajevima možda neće moći pravilno prepoznati uzorak i može vam dati pogrešne rezultate.
Stoga svakako provjerite rezultate Flash Fill-a
I baš kao što smo uklonili sav tekst nakon određenog znaka pomoću flash fill -a, možete koristiti iste korake za uklanjanje teksta prije određenog znaka. samo ručno pokažite flash fill kako bi rezultat trebao izgledati kao moj Internet u susjednom stupcu, a on će učiniti ostalo.
Uklonite tekst pomoću VBA (prilagođena funkcija)
Cijeli koncept uklanjanja teksta prije ili poslije određenog znaka ovisi o pronalaženju položaja tog znaka.
Kao što se vidi gore, pronaći posljednju pojavu tog znaka dobro znači koristiti izmišljotine formula.
Ako je to nešto što morate činiti prilično često, možete pojednostaviti ovaj postupak stvaranjem prilagođene funkcije pomoću VBA (nazvane korisnički definirane funkcije).
Nakon stvaranja ovu funkciju možete ponovno koristiti iznova i iznova. Također je mnogo jednostavniji i lakši za upotrebu (budući da se većina teških tereta obavlja VBA kodom na stražnjoj strani).
Ispod VBA koda koji možete koristiti za stvaranje prilagođene funkcije u Excelu:
Funkcija LastPosition (rCell As Range, rChar As String) 'Ova funkcija daje posljednju poziciju navedenog znaka' Ovaj kôd je razvio Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Za i = rLen do 1 korak -1 Ako je sredina (rCell, i - 1, 1) = rChar Zatim LastPosition = i - 1 Izlaz iz funkcije Kraj Ako je sljedeća funkcija i završi
VBA kôd morate postaviti u običan modul u VB Editor -u ili u Osobnu radnu knjigu makronaredbi. Kad ga imate tamo, možete ga koristiti kao bilo koju drugu redovitu funkciju radnog lista u radnoj knjizi.
Ova funkcija ima 2 argumenta:
- Referenca ćelije za koju želite pronaći posljednje pojavljivanje određenog znaka ili tekstualnog niza
- Znak ili tekstualni niz čiji položaj morate pronaći
Pretpostavimo da sada imate donji skup podataka i želite ukloniti sav tekst nakon zadnjeg zareza, a tekst imati samo prije zadnjeg zareza.
Ispod je formula koja će to učiniti:
= LIJEVO (A2, Zadnja pozicija (A2, ",")-1)
U gornjoj formuli sam odredio da se pronađe položaj posljednjeg zareza. U slučaju da želite pronaći položaj nekog drugog znaka ili tekstualnog niza, to biste trebali koristiti kao drugi argument u funkciji.
Kao što vidite, ovo je puno kraće i lakše za korištenje, u usporedbi s formulom s dugim tekstom koju smo koristili u prethodnom odjeljku
Ako stavite VBA kôd u modul u radnoj knjizi, ovu prilagođenu funkciju moći ćete koristiti samo u toj radnoj knjizi. Ako ovo želite koristiti u svim radnim knjigama na svom sustavu, morate kopirati i zalijepiti ovaj kôd u osobnu radnu knjigu makronaredbi.
Ovo su neki od poruka koje možete koristiti za brzo uklanjanje teksta prije ili poslije određenog znaka u Excelu.
Ako je to jednostavna jednokratna stvar, onda to možete učiniti pomoću značajke pronađi i zamijeni. što ako je malo složenije, tada morate upotrijebiti kombinaciju ugrađenih Excel formula ili čak stvoriti vlastitu prilagođenu formulu koristeći VBA.
Nadam se da vam je ovaj vodič bio koristan.
