Excel kennis verspreiden - deel 06: Tekstfuncties in Excel
- 29/01/2014
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Deel 6, we gaan wat meer de formuletoer op. Uiteindelijk is Excel een spreadsheet programma, een rekentool dus, met veel ingebouwde functies. Waarom bekijken we eens niet Tekstfuncties in Excel? Genre LINKS en RECHTS, maar uiteraard nog veel meer uitgebreide mogelijkheden. Zeker als we functies gaan nesten tot nieuwe combinaties… Ben je echt niet goed met formules? Geen erg, lees dan helemaal onderaan over het concept van Flashfill in Excel 2013. Een aanrader in de nieuwste versie van Excel. In de tekst zullen voorbeelden aangehaald worden die je kan downloaden en bestuderen in bijgevoegd bestand.
Basisfuncties
Zoals reeds eerder gesteld, iedereen zal wel de functies LINKS en RECHTS kennen:
=LINKS(A1; 5) levert de 5 meest linkse karakters op uit cel A1. =RECHTS(A1; 2) levert het voorlaatste en laatste karakter op uit cel A1.
Duidelijk één van de meest voorkomende fouten bij dit soort dingen op forums, is dat er extra spaties in de cellen staan. "Voorbeeld" en "Voorbeeld met spatie erachter" zijn voor Excel niet hetzelfde. Vergelijk maar rijen 2 en 3 op de afbeelding: cel A4 heeft een extra spatie. Haal dus steeds zulke irritante spaties weg ! Denk maar aan zoekfuncties zoals VERT.ZOEKEN: die zullen niet werken indien er extra spaties (of andere moeilijk zichtbare karakters) toegevoegd werden. Vaak gebeurt dit zonder het te weten. Het handigste middel om zulke fouten op te sporen is de Formule auditor - zie enkele lessen terug. Zijn er nog meer van die onzichtbare karakters, overweeg dan de functie WISSEN.CONTROL.
Een andere functie om fouten te vinden, is de LENGTE functie: die geeft de lengte van de tekst in de cel. Zie rij 5. Heb je effectief extra spaties aan het begin en/of aan het einde van een cel, verwijder die dan met de SPATIES.WISSEN functie. De Engelse tegenhanger is veel bekender: de TRIM functie.
Wil je snel weten of cel A1 extra spaties heeft? Hier is een mogelijke functie (geneste functies) met als uitkomst WAAR of ONWAAR (TRUE of FALSE):
=LENGTE(A7) = LENGTE(SPATIES.WISSEN(A7))
Van links naar rechts en van binnen naar buiten, lezen we dit als: is het aantal karakters in cel A7 evenveel als het aantal karakters van cel A7, nadat we op die cel de functie SPATIES.WISSEN toepassen? Deze notatie van wat eigenlijk een ALS functie is, is ook een goede om te onthouden. Typ gewoon 'isgelijkaan' gevolgd door de logische test. De ALS functie en ook de gebruikelijke haakjes kan je achterwege laten als je toch maar WAAR/ONWAAR als resultaat wil. Wil je 1 of 0 als resultaat (1 = WAAR, 0 = ONWAAR), doe dan iets als:
=--(LENGTE(A7) = LENGTE(SPATIES.WISSEN(A7)))
De -- (2 liggende streepjes) zetten logische waarden als WAAR en ONWAAR om naar 1 en 0. Alternatief: doe +0 bij de WAAR/ONWAAR, of *1 of verhef het tot de macht 1: allemaal bewerkingen die leiden tot 0 of 1 als resultaat.
De functie DEEL kan je gebruiken om een DEEL uit een andere cel te halen. Dit kan ook ergens middenin de cel zijn:
=DEEL(A8; 8; 6) levert het woord 'always' op. Daarbij moet je de karakterpositie kennen waarop het woord begint (positie 8) en ook het aantal op te halen karakters (6).
Uiteraard weet je niet altijd waar iets begint in een cel… Daar kan je uit bij de VIND.SPEC. Willen we uit cel A9 de stand halen (3-4) dan kan dat met:
=DEEL(A9; VIND.SPEC("-"; A9)-1;3))
Ik maak hier een aantal veronderstellingen: er komt geen liggend streepje vóór het liggend streepje van de stand, en beide ploegen vonden elk maximaal 9 keer de weg naar de netten. Een grote uitslag (België - San Marino) zou je dan zo kunnen ophalen:
=SPATIES.WISSEN(DEEL(A10; VIND.SPEC("-"; A10)-2;5))Snap je waar de 5 vandaan komt, en dus ook de nood aan de functie SPATIES.WISSEN?
Hoofdletters en kleine letters
Dat is op zich zeer simpel:
=HOOFDLETTERS(A11)
=KLEINE.LETTERS(A12)
=BEGINLETTERS(A13)
De BEGINLETTERS functie zet elke eerste letter van de zin om naar een hoofdletter, de rest blijven/worden kleine letters, moeilijker dan dat is het niet.
Wil je testen of een cel enkel hoofdletters bevat? De functie GELIJK is hoofdlettergevoelig (case sensitive): de functie kijkt of de 2 argumenten gelijk zijn aan mekaar, rekening houdend met hoofd- en kleine letters. De functie HOOFDLETTERS in het tweede argument is slim gekozen in combinatie met de functie GELIJK :-)
=GELIJK(A14;HOOFDLETTERS(A14))
Heeft er iemand ooit begrepen waarom de functie GELIJK slechts 2 argumenten toelaat? Waarom geen 3 of 4 of 5, die aan allemaal aan mekaar moeten gelijk zijn. Soit. Nu hoor ik jou luidop denken…
"Wim, hoe gemakkelijk is het artikel van deze week… Het meeste kende ik al!"
Klopt (wellicht toch)! Wil je lastigere formules? Hier is een formule om de "kleinste" letter (op volgorde volgens het alfabet) uit een tekst te halen:
=TEKEN(MIN(CODE(DEEL(HOOFDLETTERES(A15);RIJ(INDIRECT("1:"&LENGTE(A15)));1))))
Dat is al wat anders, niet? :-) Dit is een matrixformule, en zal je moeten ingeven met Ctrl-Shift-Enter in plaats van de gewone Enter. Dan verschijnen er accolades rond de formule. Breng de accolades zelf niet mee in, dat doet Excel wel voor jou als je bovenstaande toetsencombinatie hanteert bij het afsluiten van een formule. Matrixformules kunnen later nog aan bod komen in deze lessenreeks.
Tekst aan mekaar plakken
Een hele handige functie is de TEKST.SAMENVOEGEN functie, al gebruikt quasi niemand die functie onder die vorm. De snellere en kortere manier is de notatie met & (ampersand):
="Maged " & "Samy"
Uiteraard zal je in de praktijk meer functies gebruiken in 1 cel:
="We zijn vandaag " & TEKST(A17;"dd/mm/jjjj")
="Die broek kostte " & TEKST(A18;"#,00 €")
Pas op voor de datumnotaties: ik moet jjjj gebruiken voor de 4 cijferige jaarnotatie (2014), terwijl Engelse instellingen op de PC zullen vereisen dat je yyyy neemt voor hetzelfde resultaat. Excel zet zelf functies om tussen talen (vanzelfsprekend), maar bovenstaande jjjj omzetting naar yyyy zal niet gebeuren! Hetgeen we hier moeten onthouden is de functie TEKST: die is zeer handig en heeft uitgebreide mogelijkheden naar het opmaken van getallen als tekst. Ter info, datums zijn in Excel ook maar getallen; laat in cel B17 de functie TEKST maar eens weg en je zal het verschil merken.
Vervangen
In tekst moet je al wel eens dingen vervangen. Bijvoorbeeld: liggende streepjes door underscores vervangen:
=SUBSTITUEREN(A19;"-";"_")
Dan gebruik je de functie SUBSTITUEREN. Vindt Excel het te zoeken karakter (of de te zoeken karakters) niet, dan wordt er niets vervangen. Een handigheidje is dat je ook kan opgeven het hoeveelste liggend streepje je wil omzetten naar een underscore. Bvb. enkel het eerste en niet het tweede. Vermeld je dat niet, dan zet Excel alle liggende streepjes om.
Naast die functie heb je ook de VERVANGEN functie. Die gebruik je als je een bepaalde positie en lengte kent in de originele tekst, of alleszins als je ze kan berekenen of achterhalen met formules. Als je enkel het woord 'woensdag' wil omzetten naar hoofdletters en niet het woord 'januari':
=VERVANGEN(A20;1;VIND.SPEC(" ";A20);LINKS(HOOFDLETTERS(A20);VIND.SPEC(" ";A20)))
Het voorbeeldje over Wikipedia (alles wat de taal voorstelt wordt vervangen door nl van Nederlands):
=VERVANGEN(A21;VIND.SPEC(":";A21)+3;SOM(VIND.SPEC({".";":"};A21)*{1;-1})-3;"nl")
Begrijp je hoe dit werkt? Oh ja, hulpkolommen gerbuiken om deze functie in meerdere cellen te berekenen, kan natuurlijk ook.
Tekst afsplitsen
Nu we toch bezig zijn met wat moeilijkere functies, genest in mekaar, laten we eens volgend geval bekijken. Van cellen wil je telkens de laatste 2 delen afsplitsen. Zoals: "You'll never walk alone" wordt "walk alone". Je weet niet hoeveel woorden er in een cel staan (soms in totaal ook maar 1 woord bvb.), je weet niet lang de woorden zijn, je wil telkens de laatste 2 woorden afzonderen. Met Data > Tekst naar kolommen zou dit ook gaan met een spatie als scheidingsteken, maar dan eindigen de laatste 2 woorden steevast in wisselende aantal woorden / kolommen. De onderstaande functie is echt sterk gevonden! Gebruik de Formula auditor om hem te ontleden zodat je begrijpt wat er gebeurt (ik heb dat ook moeten doen ;-) )
=SPATIES.WISSEN(RECHTS(SUBSTITUEREN(A22;" ";HERHALING(" ";20));40))
Heb je de functie HERHALING opgemerkt? 20 is het maximaal aantal tekens dat je verwacht per woord. Kan je hoger of lager kiezen als je wil. We willen 2 woorden afsplitsen, dus 40 is 2 keer 20. De rest laat ik aan jou over. (Een langer) alternatief:
=RECHTS(A23;LENGTE(A23)-VIND.ALLES(TEKEN(1);SUBSTITUEREN(A23;" ";TEKEN(1);LENGTE(A23)-LENGTE(SUBSTITUEREN(A23;" ";""))-1)))
Nog eentje om eens rustig op het gemak te bekijken! Overigens zijn UDF's in VBA (User Defined Functions) zeker aan te raden indien de formules te lang gaan worden. Dan kan je beter gaan voor een duidelijkere geprogrammeerde functie dan veel functies in mekaar te gaan nesten. Reden: je moet later nog begrijpen wat je ooit zo vlot in mekaar stak… Functies in VBA hebben het grote voordeel dat je met variabelen kan werken, je hoeft niet steeds dingen te herhalen. Daarnaast hebben functies een duidelijke beschrijvende naam (die kies je zelf) en is de functie hierboven totaal niet beschrijvend. Performantieredenen kunnen ook een reden zijn om eerder VBA of eerder gewone Excel functies te gebruiken.
Fouten afvangen
-
Als je zoekt naar tekst in een cel, maar die tekst komt niet voor: dan heb je een probleem. Dat kan je oplossen op meerdere manieren:
- De fout in de cel laten staan, en evt. met Voorwaardelijke opmaak verbergen
- Een formule zoals ISFOUT toepassen
- Een formule zoals ISGETAL toepassen
=VIND.SPEC("basketbal";A24)
=ALS.FOUT(VIND.SPEC("basketbal";A24);"niet gevonden")
=ALS(ISGETAL(VIND.SPEC("basketbal";A25));"gevonden";"niet gevonden")
En zo kan je nog wel variaties bedenken.
Adressen en namen splitsen
Een heikel punt in Excel (en elders) is het splitsen van namen van personen, en adressen. Hiervoor is er geen oplossing met 100% garantie. Er zal steeds een bepaalde inzet van mensen nodig zijn, of extra informatie, of logica. Met formules alleen kan je niet alle situaties perfect aan. Kijk maar naar het plaatje:
… dit gaat lastig zijn om enkel met formules uit mekaar te trekken naar huisnummer, straat, gemeente, regio, land. En de voorbeelden kunnen nog veel exotischer zijn dan dit! Diezelfde problematiek stelt zich bij voornamen, familienamen, initialen, tussennamen, (ere)titels, dubbele voornamen, dubbele achternamen, suffixen, enz. De combinaties zijn zeer talrijk en generieke formules maken dus ook lastig. Neemt niet weg dat een formule opstellen om pakweg 80-90% van een database reeds goed te splitsen, ook een hele uitdaging kan vormen. Creatief zijn is de boodschap, en functies in mekaar gaan nesten (of hulpkolommen gebruiken).
Liedjes en hun uitvoerders
Een concreet voorbeeld… Een jaarlijkse traditie voor mij is het ophalen van de lijst van 100 tijdloze songs van Studio Brussel, en ook de lijst van de 100 op 1 van Radio 1. Dat geeft dan alle informatie in 1 cel, zoals te zien hieronder voor de top 5 van de Tijdloze 100, en ook nummer 100 in de lijst:
Daaruit moet ik dan 4 stukken filteren: notering dit jaar, notering vorig jaar, uitvoerder, song. Alles staat in 1 cel, met spaties en ascii karakter 160 als spaties! En niet overal evenveel spaties uiteraard (zie nummer 100) Het Excel bestand in bijlage bevat formules om dit te doen, hoewel ze niet fool-proof zijn: als uitvoerder of song een liggend streepje hebben gaat het mis.
Wie zich geroepen voelt voor een generieke, correcte functie, doe gerust! Maar, er is sinds kort Flashfill! Lees verder…
Flashfill
En dan nu het langverwachte onderwerp van Flashfill. Deze functionaliteit werd geïntroduceerd in Excel 2013. Flashfill kan cellen opvullen op basis van een patroon in een eerdere rij of rijen. Bijvoorbeeld, we moeten uit kolom A drie zaken halen, en die moeten kolom in kolommen B, C en D - speler, aantal seizoenen en ploegnaam:
Vul de eerste rij in, cellen B1, C1, D1. Zet de cursor in cel B2 en klik op Data > Flashfill:
Resultaat:
Herhaal dit voor cel C2 en cel D2, en je bent klaar! Zelfs voor duizenden rijen kan dit in 1 beweging gedaan worden.
Je hoeft zelf geen enkele formule meer te typen of uit te vinden, Excel zoekt via ingebouwde algoritmen naar patronen in de data.
Nog 2 keer Flashfill en je hebt:
Geloof je dit niet? Of wil je iets straffers zien? Neem dan samen met mij de proef op de som (als je Excel 2013 hebt uiteraard) en herneem het voorbeeld van de Tijdloze 100 songs:
Je kan ook de vulgreep gebruiken voor Flashfill. In plaats van met de linkermuisknop door te trekken via de vulgreep, neem je nu de rechtermuisknop, houd die ingedrukt en laat pas los als je bij de laatste cel aangekomen bent. Laat dan los en je ziet een menu verschijnen waar je voor Flashfill kiest:
Indrukwekkend, niet? :-) Ziezo, alweer een stukje Excel kennis rijker !
