Een eenvoudig, op het eerste gezicht, probleem met een niet voor de hand liggende oplossing: haal het laatste woord uit een regel tekst. Welnu, of, in het algemeen, het laatste fragment, gescheiden door een bepaald scheidingsteken (spatie, komma, enz.). Met andere woorden, het is noodzakelijk om een omgekeerde zoekactie (van eind naar begin) in de string van een gegeven teken en extraheer vervolgens alle tekens rechts ervan.
Laten we eens kijken naar de traditioneel verschillende manieren om uit te kiezen: formules, macro's en via Power Query.
Methode 1. Formules
Om het gemakkelijker te maken om de essentie en mechanica van de formule te begrijpen, beginnen we een beetje van een afstandje. Laten we eerst het aantal spaties tussen woorden in onze brontekst vergroten naar bijvoorbeeld 20 stukjes. Dit doe je met de vervangfunctie. PLAATSVERVANGER (VERVANGING) en de functie van het N-maal herhalen van een bepaald teken - HERHALING (HERHALEN):
Nu snijden we 20 tekens af van het einde van de resulterende tekst met behulp van de functie RECHTS (RECHTSAF):
Het wordt warmer, toch? Het blijft om extra spaties te verwijderen met behulp van de functie TRIM (TRIM) en het probleem is opgelost:
In de Engelse versie ziet onze formule er als volgt uit:
=TRIM(RECHTS(VERVANG(A1;» «;HERHALEN(» «;20));20))
Ik hoop dat het duidelijk is dat het in principe niet nodig is om precies 20 spaties in te voegen – elk getal is voldoende, zolang het maar langer is dan de lengte van het langste woord in de brontekst.
En als de brontekst niet door een spatie moet worden gedeeld, maar door een ander scheidingsteken (bijvoorbeeld door een komma), dan moet onze formule enigszins worden gecorrigeerd:
Methode 2. Macrofunctie
De taak om het laatste woord of fragment uit de tekst te extraheren, kan ook worden opgelost met behulp van macro's, namelijk het schrijven van een omgekeerde zoekfunctie in Visual Basic die doet wat we nodig hebben - zoeken naar een bepaalde substring in een string in de tegenovergestelde richting - van het einde tot het begin.
Druk op de sneltoets anders+F11 of knop Visual Basic tab ontwikkelaar (Ontwikkelaar)om de macro-editor te openen. Voeg dan een nieuwe module toe via het menu Invoegen – Module en kopieer daar de volgende code:
Functie LastWord(txt As String, Optioneel delim As String = " ", Optioneel n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
Nu kunt u de werkmap opslaan (in een macro-enabled formaat!) en de gemaakte functie in de volgende syntaxis gebruiken:
=LaatsteWoord(txt; delim;n)
WAAR
- txt – cel met brontekst
- delim — scheidingsteken (standaard — spatie)
- n – welk woord moet van het einde worden geëxtraheerd (standaard – het eerste van het einde)
Bij eventuele toekomstige wijzigingen in de brontekst, wordt onze macrofunctie direct herberekend, zoals elke standaard Excel-bladfunctie.
Methode 3. Power-query
Power Query is een gratis add-on van Microsoft voor het importeren van gegevens in Excel vanuit bijna elke bron en het vervolgens omzetten van de gedownloade gegevens in elke vorm. De kracht en coolheid van deze invoegtoepassing is zo groot dat Microsoft al zijn functies standaard in Excel 2016 heeft ingebouwd. Voor Excel 2010-2013 kan Power Query hier gratis worden gedownload.
Onze taak om het laatste woord of fragment door een bepaald scheidingsteken te scheiden met behulp van Power Query is heel eenvoudig opgelost.
Laten we eerst van onze gegevenstabel een slimme tabel maken met behulp van sneltoetsen. Ctrl+T of commando's Home – Opmaak als tabel (Home - Opmaken als tabel):
Vervolgens laden we de gemaakte "slimme tabel" in Power Query met behulp van de opdracht Van tafel/reeks (Van tafel/assortiment) tab Data (als je Excel 2016 hebt) of op het tabblad Power Query (als je Excel 2010-2013 hebt):
In het query-editorvenster dat wordt geopend, op het tabblad Transformatie (Transformeren) kies een team Kolom splitsen - op scheidingsteken (Kolom splitsen — Per scheidingsteken) en dan blijft het om het scheidingsteken in te stellen en de optie te selecteren meest rechtse scheidingstekenom niet alle woorden te schrappen, maar alleen de laatste:
Na het klikken op OK het laatste woord wordt gescheiden in een nieuwe kolom. De onnodige eerste kolom kan worden verwijderd door met de rechtermuisknop op de kop te klikken en . te selecteren verwijderen (Verwijderen). U kunt ook de naam van de resterende kolom in de tabelkop wijzigen.
De resultaten kunnen weer naar het blad worden geüpload met behulp van de opdracht Home — Sluiten en laden — Sluiten en laden naar … (Home — Sluiten & laden — Sluiten & laden naar…):
En als resultaat krijgen we:
Zo - goedkoop en vrolijk, zonder formules en macro's, bijna zonder het toetsenbord aan te raken 🙂
Als de originele lijst in de toekomst verandert, volstaat het om met de rechtermuisknop te klikken of een sneltoets te gebruiken Ctrl+anders+F5 update ons verzoek.
- Plaktekst in kolommen splitsen
- Tekst ontleden en ontleden met reguliere expressies
- De eerste woorden uit de tekst extraheren met de SUBSTITUTE-functie