Het is weer tijd voor het doen van aangifte voor de belastingen. Dan is het altijd handig om Excel achter de hand te hebben om bedragen op te tellen etc.
Maar wist u, dat Excel ook teksten kan ‘optellen’? Nou ja, beter gezegd samenvoegen.

Het komt in Excel nogal eens voor, dat de inhoud van verschillende cellen moet worden samengevoegd.
Zeker wanneer de gegevens uit een ander systeem of uit een Pdf-bestand worden overgenomen, komen bijvoorbeeld dubbele voor- en achternamen vaak in verschillende cellen terecht.

TEKST.SAMENVOEGEN

In het Voorbeeldbestand staat op het werkblad Namen een klein lijstje namen. Al snel is te zien dat de regels 4, 6 en 7 nog aanpassing nodig hebben.

Gelukkig heeft Excel een ingebouwde functie voor het samenvoegen van teksten: TEKST.SAMENVOEGEN (klinkt logisch, toch?).

Op basis van de geïmporteerde namen maken we als volgt een nieuw overzicht:

1. iets onder het bestaande overzicht (in het voorbeeld in cel B10) verwijzen we naar de eerste cel van de kop door in te tikken  =B2
2. deze formule kopiëren we één cel naar rechts en net zo vaak naar beneden als er regels zijn in het oorspronkelijke bestand. Gebruik hiervoor de vulgreep van cel B10; zie het artikel Doorvoeren en sorteren.
3. de meeste cellen zijn natuurlijk meteen goed, maar bijvoorbeeld de tweede naam kan nog wel een aanpassing gebruiken. In de betreffende cel (in het voorbeeld B11) moeten de 2 voornamen gecombineerd worden: =TEKST.SAMENVOEGEN(B4;C4)
4. het resultaat  is niet precies wat we willen; er moet nog een spatie tussen de 2 voornamen: =TEKST.SAMENVOEGEN(B4;” “;C4)
5. deze formule kopiëren we naar alle cellen waar namen moeten worden samengevoegd (in het voorbeeld B13, B14 en C14)
6. maar nu kloppen de achternamen van Marie Jose nog niet; de formule voegt de verkeerde cellen samen. Dit lossen we snel op:
   * selecteer de betreffende cel (in dit geval C13)
   * klik in de formulebalk of druk op F2 om de formule aan te passen
   * de cellen, die in de formule gebruikt worden hebben dan een gekleurde rand, overeenkomend met de celverwijzing in de formule:
   * ‘pak’ met de muis de groene rand vast en sleep 1 cel naar rechts
   * idem met de blauwe rand; door dit slepen wordt de formule automatisch aangepast
7. de achternaam van de tweede persoon is nog niet juist; de celverwijzing dient nog aangepast te worden.

Is het bestand veel groter, dan moet per regel bekeken worden welke formule waar moet komen; het handigste gaat dit door de 2 cellen van de tweede naam te Kopiëren en overal waar dezelfde situatie zich voordoet te Plakken. Idem voor de vierde en de vijfde naam (dan hebben we alle mogelijkheden gehad).

Alternatieve samenvoeging

Waar we in Excel een optelling van getallen regelen via het +-teken, kunnen we ook teksten ‘optellen’. Alleen wordt daar een ander teken voor gebruikt: & (ampersand; zie het interessante artikel op Wikipedia).

Een alternatieve (simpelere) manier om de naam Jan Willem te maken krijgen we dan als volgt: =B4&” “&C4

Zie verder het Voorbeeldbestand.

Flexibele overzichten

Tekst-samenvoegingen kunnen goed gebruikt worden om rapportages/overzichten flexibeler te maken.
Hoe vaak gebeurt het niet dat je een overzicht van een vorig jaar of vorige maand kopieert en vergeet om alle ‘harde’ jaar- en maand-aanduiding aan te passen.

Het is dan ook een goede gewoonte om harde gegevens (vaak parameters genoemd) apart vast te leggen en daar in formules naar te verwijzen.

In het Voorbeeldbestand op het werkblad Parameters is dit in het kort uitgewerkt:

1. leg het jaar vast in cel C2 en de maand in C3
2. typ de volgende formule in cel B7:
   =”Overzicht van “&C3&” “&C2
   (denk aan de spaties op de juiste plaatsen!)
3. verander een input-variabele en bekijk het resultaat.

In plaats van 2 variabelen kunnen we ook eenzelfde resultaat bereiken door uit te gaan van één datum (cel C4).
In cel F7 voeren we de volgende formule in: =”Overzicht van “&C4
 Helaas, het resultaat is niet precies wat we verwachten! Excel “vergeet” nu dat we te maken hebben met een datum. We moeten zelf nog wat denkwerk verrichten:  =”Overzicht van “&TEKST(C4;”mmmm jjjj”). Door opmaak aan de broncel mee te geven, krijgen we wel het gehoopte resultaat.

 NB1 omdat we weten, dat cel C4 een datum voorstelt, geven we die cel met behulp van de functie TEKST een datum-opmaak; ‘speel’ met de notatie-instellingen: het aantal m‘s, zet er ook eens een d in etc.

NB2 met dezelfde functie TEKST kan ook de opmaak van een getal worden aangepast. De opmaak volgt dezelfde notatie als bij Celeigenschappen.

De formules in de Excel-sheet worden beter leesbaar wanneer de cellen met parameters een Naam krijgen. Geef je aan cel C2 de naam Jaar mee en aan C3 de naam Maand dan wordt de formule in B7 =”Overzicht van “&Maand&” “&Jaar
Zeker wanneer verwijzingen zich op andere werkbladen bevinden is een dergelijke formule veel duidelijker.

In het kader van flexibiliteit: om bij een volgende maand niet allerlei wijzigingen te hoeven doorvoeren, moeten de datums natuurlijk ook afhankelijk zijn van een parameter:
via de functies DATUM, JAAR en MAAND wordt gebruik gemaakt van de waarde in cel C4.
NB met DATUM zijn allerlei mogelijkheden denkbaar. Voorbeeld: wil je de laatste dag hebben van de maand, waarin de datum in C4 valt?
=DATUM(JAAR(C4);MAAND(C4)+1;1)-1
ofwel maak een datum door het jaar van C4 te nemen, de maand van C4 plus 1 (de volgende maand dus) en de eerste dag. Door dan van het resultaat 1 af te trekken weten we zeker dat we op de laatste van de betreffende maand uitkomen!

Eigen functies

In het eerste voorbeeld waren we al even bezig om alle namen goed te krijgen, ook al betrof het maar een bestandje met enkele regels.
Zeker wanneer het meer regels betreft en met meer variaties dan wordt het netjes samenvoegen van teksten een behoorlijk karwei.

In het Voorbeeldbestand heb ik in het tabblad Tennis een overzicht opgenomen van een tenniscompetitie, zoals die binnen is gehaald vanuit een PDF. Zoals te zien is moet er flink ‘gepoetst’ worden. Voor dit soort werk heb ik een eigen TekstSamenvoeg-functie geschreven. Laten we die eerst aan het werk zetten:

1. voeg op de plaats van kolom C een nieuwe kolom in
2. in de nieuwe cel C3 plaatsen we de volgende functie:
   =TekstSamenvoegenRange(D3:E3)
3. kopieer cel C3 naar beneden (dubbelklik op de vulgreep van cel C3)
4. ook nu klopt nog niet alles: in regel 7 moeten 3 cellen samengevoegd worden. Daar moet de formule dus worden: =TekstSamenvoegenRange(D7:F7)
5. kopieer deze cel naar alle regels waar drie cellen moeten worden samengevoegd
6. selecteer cel D2, ‘pak’ de rand vast en sleep naar C2
7. selecteer de hele kolom C (klik op de C boven de kolom)
8. kies kopiëren en daarna direct Plakken/waarden; op deze manier is de inhoud van kolom C ‘hard’ gemaakt
9. voeg op de plaats van kolom D een nieuwe kolom in
10. in de nieuwe cel D3 plaatsen we de volgende functie:
    =TekstSamenvoegenRange(G3:I3)
11. kopieer cel D3 naar beneden (dubbelklik op de vulgreep van cel D3)
12. op verschillende plaatsen klopt het nu niet, bijvoorbeeld in regel 4. Selecteer cel D4 en druk op de functietoets F2. De cellen G4 t/m I4 hebben nu een blauwe rand. Pak de hoekpunt linksonder en sleep deze één cel naar rechts en druk op Enter.
13. pas op dezelfde manier de overige formules aan, waar dat nodig is.
14. sleep het kopje uit cel F2 naar D2
15. maak kolom D hard en verwijder alle kolommen E t/m J

De VBA-routine van deze functie is te vinden in Module1 van het Voorbeeldbestand. Druk daartoe op Alt-F11 (dus de Alt-toets inhouden en op de functietoets F11 drukken). De commentaarregels (groene tekst) lichten de bedoeling van de functie en de diverse stappen toe.

De functie kent een optionele parameter, de KoppelTekst (die hoeft dus niet ingevuld te worden; in dat geval wordt hier dan een spatie gekozen).
wanneer we in cel C3 hadden ingevoerd =TekstSamenvoegenRange(D3:E3;” “) dan was het resultaat dus hetzelfde geweest (denk aan de aanhalingstekens om de spatie!).  Maar het had ook =TekstSamenvoegenRange(D3:E3;” | “) kunnen zijn etc.
Door =TekstSamenvoegenRange(D3:E3;TEKEN(10)) worden de elementen gescheiden door een ‘harde-return’ (ofwel nieuwe-regel-opdracht). Vink bij Celeigenschappen/Uitlijning dan wel de optie Terugloop aan.

Ik heb nog een andere functie toegevoegd: TekstSamenvoegenTeller.
Kies opnieuw het tabblad Tennis van het originele Voorbeeldbestand en voer het volgende uit:

1. voeg op de plaats van kolom C TWEE nieuwe kolommen in
2. in cel D3 tikken we het getal 2 in; in de nieuwe cel C3 plaatsen we de volgende functie:
   =TekstSamenvoegenTeller(E3;D3)
3. kopieer de cellen C3 en D3 naar beneden
4. in sommige cellen zal de waarde 2 gewijzigd moeten worden in 3

---