Strādājot ar teksta datiem programmā Excel, jums var būt nepieciešams noņemt tekstu pirms vai pēc konkrētas rakstzīmes vai teksta virknes.
Piemēram, ja jums ir cilvēku vārdi un apzīmējumu dati, iespējams, vēlēsities noņemt apzīmējumu aiz komata un paturēt tikai nosaukumu (vai otrādi, ja saglabājat apzīmējumu un noņemat vārdu).
Dažreiz to var izdarīt ar vienkāršu formulu vai ātru atrašanu un nomaiņu, un dažreiz tai ir vajadzīgas sarežģītākas formulas vai risinājumi.
Šajā apmācībā es parādīšu, kā noņemt tekstu pirms vai pēc noteiktas rakstzīmes programmā Excel (izmantojot dažādus piemērus).
Tāpēc sāksim ar dažiem vienkāršiem piemēriem.
Noņemiet tekstu pēc rakstzīmes, izmantojot funkciju Atrast un aizstāt
Ja vēlaties ātri noņemt visu tekstu pēc noteiktas teksta virknes (vai pirms teksta virknes), varat to izdarīt, izmantojot funkciju Atrast un aizstāt un aizstājējzīmes.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties noņemt apzīmējumu aiz komata rakstzīmes un saglabāt tekstu pirms komata.
Tālāk ir norādītas darbības, kā to izdarīt:
- Kopējiet un ielīmējiet datus no kolonnas A uz kolonnu B (tas ir, lai saglabātu arī sākotnējos datus)
- Kad B slejas šūnas ir atlasītas, noklikšķiniet uz cilnes Sākums
- Rediģēšanas grupā noklikšķiniet uz opcijas Atrast un atlasīt
- Nolaižamajā izvēlnē redzamajās opcijās noklikšķiniet uz opcijas Aizstāt. Tiks atvērts dialoglodziņš Atrast un aizstāt
- Laukā “Atrast ko” ievadiet ,* (ti, komats, kam seko zvaigznītes zīme)
- Atstājiet lauku “Aizstāt ar” tukšu
- Noklikšķiniet uz pogas Aizstāt visu
Iepriekš minētās darbības atrastu komatu datu kopā un noņemtu visu tekstu aiz komata (ieskaitot komatu).
Tā kā tas aizstāj tekstu no atlasītajām šūnām, ieteicams kopēt tekstu citā kolonnā un pēc tam veikt šo atrašanas un aizstāšanas darbību vai izveidot savu datu rezerves kopiju, lai sākotnējie dati būtu neskarti
Kā tas darbojas?
* (zvaigznītes zīme) ir aizstājējzīme, kas var attēlot neierobežotu rakstzīmju skaitu.
Kad es to lietoju pēc komata (laukā “Atrast”) un pēc tam noklikšķiniet uz pogas Aizstāt visu, tas atrod pirmo komatu šūnā un uzskata to par atbilstību.
Tas ir tāpēc, ka zvaigznīte (*) tiek uzskatīta par atbilstību visai teksta virknei, kas seko komatam.
Tātad, nospiežot pogu Aizstāt visu, tas aizstāj komatu un visu nākamo tekstu.
Piezīme: Šī metode darbojas labi, tad katrā šūnā ir tikai viens komats (kā mūsu piemērā). Ja jums ir vairāki komati, šī metode vienmēr atradīs pirmo komatu un pēc tam noņems visu pēc tā. Tātad jūs nevarat izmantot šo metodi, ja vēlaties aizvietot visu tekstu pēc otrā komata un saglabāt pirmo tādu, kāds tas ir.Ja vēlaties noņemt visas rakstzīmes pirms komata, nomainiet lauka ierakstu, ievietojot zvaigznīti pirms komata (*, nevis*)
Noņemiet tekstu, izmantojot formulas
Ja jums ir nepieciešama lielāka kontrole pār to, kā atrast un aizstāt tekstu pirms vai pēc konkrētas rakstzīmes, programmā Excel labāk izmantojiet iebūvētās teksta formulas.
Pieņemsim, ka jums ir tālāk norādītā datu kopa Kur vēlaties noņemt visu tekstu aiz komata.
Zemāk ir formula, kā to izdarīt:
= LEFT (A2, FIND (",", A2) -1)
Iepriekš minētā formula izmanto funkciju FIND, lai atrastu komata atrašanās vietu šūnā.
Pēc tam šo pozīcijas numuru izmanto funkcija LEFT, lai izvilktu visas rakstzīmes pirms komata. Tā kā es nevēlos komatu kā daļu no rezultāta, esmu atņēmis 1 no meklēšanas formulas iegūtās vērtības.
Tas bija vienkāršs scenārijs.
Pieņemsim nedaudz sarežģītāku.
Pieņemsim, ka man ir šī datu kopa, kurā es vēlos noņemt visu tekstu pēc otrā komata.
Šeit ir formula, kas to darīs:
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", 2))-1)
Tā kā šai datu kopai ir vairāki komati, es nevaru izmantot funkciju FIND, lai iegūtu pirmā komata stāvokli un izvilktu visu pa kreisi no tā.
Man kaut kā jānoskaidro otrā komata pozīcija un pēc tam jāizņem viss, kas atrodas pa kreisi no otrā komata.
Lai to izdarītu, esmu izmantojis funkciju SUBSTITUTE, lai otro komatu mainītu uz izsaukuma zīmi. Tagad tas man dod unikālu raksturu šūnā. Tagad es varu izmantot izsaukuma zīmes pozīciju, lai visu izvilktu pa kreisi no otrā komata.
Šī izsaukuma zīmes pozīcija tiek izmantota LEFT funkcijā, lai izvilktu visu pirms otrā komata.
Tik tālu, labi!
Bet ko darīt, ja datu kopā ir nevienmērīgs komatu skaits.
Piemēram, zemāk esošajā datu kopā dažām šūnām ir divi komati, bet dažām - trīs komati, un man ir jāizņem viss teksts pirms pēdējā komata.
Šajā gadījumā man kaut kā jāizdomā komata pēdējās parādīšanās pozīcija un pēc tam jāizņem viss pa kreisi no tā.
Zemāk ir formula, kas to darīs
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Iepriekš minētā formula izmanto funkciju LEN, lai atrastu kopējā teksta garumu šūnā, kā arī teksta garumu bez komata.
Kad es atņemu šīs divas vērtības, tas dod kopējo komatu skaitu šūnā.
Tātad, tas dotu man 3 šūnai A2 un 2 - šūnai A4.
Pēc tam šī vērtība tiek izmantota formulā SUBSTITUTE, lai pēdējo komatu aizstātu ar izsaukuma zīmi. Un tad jūs varat izmantot kreiso funkciju, lai izvilktu visu, kas atrodas pa kreisi no izsaukuma zīmes (kur agrāk bija pēdējais komats)
Kā redzat piemēros, teksta formulu kombinācijas izmantošana ļauj rīkoties daudzās dažādās situācijās.
Turklāt, tā kā rezultāts ir saistīts ar sākotnējiem datiem, mainot sākotnējos datus, rezultāts tiks automātiski atjaunināts.
Noņemiet tekstu, izmantojot Flash Fill
Flash Fill ir rīks, kas tika ieviests programmā Excel 2013 un pēc tam ir pieejams visās versijās.
Tas darbojas, identificējot modeļus, kad manuāli ievadāt datus un pēc tam ekstrapolējat, lai iegūtu datus par visu kolonnu.
Tātad, ja vēlaties noņemt tekstu pirms vai pēc konkrētas rakstzīmes, jums vienkārši jāparāda zibspuldzes paskaidrojums, kā izskatītos rezultāts (pāris reizes to ievadot manuāli), un zibspuldzes aizpilde automātiski izprastu modeli un sniegtu jums rezultāti.
Ļaujiet man to parādīt ar piemēru.
Zemāk ir datu kopa, kurā es vēlos noņemt visu tekstu aiz komata.
Tālāk ir norādītas darbības, lai to izdarītu, izmantojot Flash Fill.
- Šūnā B2, kas ir mūsu datu blakus sleja, manuāli ievadiet “Jeffery Haggins” (tas ir paredzamais rezultāts)
- Šūnā B3 ievadiet “Tim Scott” (tas ir gaidāmais rezultāts otrajā šūnā)
- Izvēlieties diapazonu B2: B10
- Noklikšķiniet uz cilnes Sākums
- Rediģēšanas grupā noklikšķiniet uz nolaižamās opcijas Aizpildīt
- Noklikšķiniet uz Flash Fill
Iepriekš minētās darbības sniegs jums rezultātu, kā parādīts zemāk:
Varat arī izmantot īsinājumtaustiņu Flash Fill Control + E. pēc šūnu atlasīšanas rezultātu slejā (mūsu piemēra B sleja)
Flash Fill ir brīnišķīgs rīks, un vairumā gadījumu tas spēj atpazīt modeli un sniegt jums pareizo rezultātu. bet dažos gadījumos tas var nespēt pareizi atpazīt modeli un var sniegt nepareizus rezultātus.
Tāpēc noteikti pārbaudiet Flash aizpildīšanas rezultātus
Tāpat kā mēs esam noņēmuši visu tekstu pēc noteiktas rakstzīmes, izmantojot zibatmiņas aizpildi, jūs varat izmantot tās pašas darbības, lai noņemtu tekstu pirms konkrētas rakstzīmes. blakus esošajā slejā vienkārši parādiet zibspuldzes aizpildījumu, kā rezultātam vajadzētu izskatīties manu internetu.
Teksta noņemšana, izmantojot VBA (pielāgota funkcija)
Viss teksta noņemšanas jēdziens pirms vai pēc konkrētas rakstzīmes ir atkarīgs no šī rakstura pozīcijas noteikšanas.
Kā redzams iepriekš, lai atrastu šī rakstura pēdējo notikumu, tas nozīmē izmantot formulas.
Ja tas ir jādara diezgan bieži, varat vienkāršot šo procesu, izveidojot pielāgotu funkciju, izmantojot VBA (sauktas par lietotāja definētām funkcijām).
Kad šī funkcija ir izveidota, varat to atkal un atkal izmantot. Tas ir arī daudz vienkāršāk un vieglāk lietojams (jo lielāko daļu smago celšanu veic ar VBA kodu aizmugurē).
Zem VBA koda, kuru varat izmantot, lai izveidotu pielāgotu funkciju programmā Excel:
Funkcija LastPosition (rCell As Range, rChar As String) 'Šī funkcija dod norādītās rakstzīmes pēdējo pozīciju' Šo kodu ir izstrādājis Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Attiecībā uz i = rLen līdz 1. solim -1 Ja vidējs (rCell, i - 1, 1) = rChar Tad LastPosition = i - 1 Iziet no funkcijas Beigu Ja nākamā i Beigu funkcija
VBA kods jāievieto parastajā modulī VB redaktorā vai personiskajā makro darbgrāmatā. Kad esat to tur atradis, varat to izmantot kā jebkuru citu parasto darblapas funkciju darbgrāmatā.
Šai funkcijai ir divi argumenti:
- Šūnas atsauce, kurai vēlaties atrast konkrētas rakstzīmes vai teksta virknes pēdējo gadījumu
- Rakstzīme vai teksta virkne, kuras atrašanās vieta jums jāatrod
Pieņemsim, ka jums tagad ir zemāk esošā datu kopa un vēlaties noņemt visu tekstu pēc pēdējā komata un tikai tekstu pirms pēdējā komata.
Zemāk ir formula, kas to darīs:
= LEFT (A2, LastPosition (A2, ",")-1)
Iepriekš minētajā formulā esmu norādījis, lai atrastu pēdējā komata pozīciju. Ja vēlaties atrast kādas citas rakstzīmes vai teksta virknes pozīciju, tas jāizmanto kā otrais funkcijas arguments.
Kā redzat, tas ir daudz īsāks un vieglāk lietojams, salīdzinot ar garo teksta formulu, kuru izmantojām iepriekšējā sadaļā
Ja ievietojat VBA kodu darbgrāmatas modulī, šo pielāgoto funkciju varēsit izmantot tikai konkrētajā darbgrāmatā. Ja vēlaties to izmantot visās sistēmas darbgrāmatās, šis kods ir jākopē un jāielīmē personiskajā makro darbgrāmatā.
Tātad šie ir daži no e -pastiem, kurus varat izmantot, lai ātri noņemtu tekstu pirms vai pēc noteiktas rakstzīmes programmā Excel.
Ja tā ir vienkārša vienreizēja lieta, varat to izdarīt, izmantojot atrašanu un nomaiņu. ko darīt, ja tas ir nedaudz sarežģītāk, tad jums ir jāizmanto iebūvētu Excel formulu kombinācija vai pat jāizveido sava pielāgotā formula, izmantojot VBA.
Es ceru, ka šī apmācība jums šķita noderīga.
