Combinatie

Een geplakte lijst splitsen en opschonen tot één kolom

Splits een in één cel geplakte lijst factuurnummers op de komma's, haal de losse spaties weg met SPATIES.WISSEN en zet de stukjes met NAAR.KOLOM onder elkaar, klaar om te vergelijken.

Gebruikte functies:TEKST.SPLITSENSPATIES.WISSENNAAR.KOLOMExcel 2024 of nieuwer

Moeilijkheidsgraad

Uitgewerkt voorbeeld

Factuurnummers in één cel geplakt

Situatie

Voor de controle van de debiteuren van Veldhuis Installatie BV mailt de klant je de facturen die na het boekjaareinde van 31 december 2025 zijn betaald, in één cel geplakt met onregelmatige spaties rond de komma's.

Je wilt één schone kolom factuurnummers, zodat je elk nummer kunt vergelijken met de debiteurenlijst.

De gegevens

ABCD
1Factuurnummers (zoals geplakt)
225-0412, 25-0415 ,25-0420,25-0431 , 25-043725-0412=NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2, ",")))=NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2; ",")))
325-0415
425-0420
525-0431
625-0437
7
Formulecel (C2)OverloopresultaatTekens: extra spatie

Formule in C2

=NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2, ",")))
=NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2; ",")))

Resultaat: 25-0412 / 25-0415 / 25-0420 / 25-0431 / 25-043725-0412 / 25-0415 / 25-0420 / 25-0431 / 25-0437

Hoe de onderdelen samenwerken

Let op: SPATIES.WISSEN haalt alleen gewone spaties weg. Een harde spatie, TEKEN(160), blijft staan en de lengtes blijven te groot.

De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.

  1. =TEKST.SPLITSEN(A2, ",")
    =TEKST.SPLITSEN(A2; ",")

    Knipt de tekst in A2 bij elke komma en zet de stukjes naast elkaar in één rij: 5 cellen met "25-0412", " 25-0415 ", "25-0420", " 25-0431 " en " 25-0437". De spaties rond de nummers staan er nog.

  2. =LENGTE(TEKST.SPLITSEN(A2, ","))
    =LENGTE(TEKST.SPLITSEN(A2; ","))

    Telt de tekens van elk stukje: 7, 9, 7, 10 en 8. Een schoon factuurnummer is 7 tekens lang, dus de extra 2, 3 en 1 tekens zijn de spaties waardoor een vergelijking mislukt.

  3. =SPATIES.WISSEN(TEKST.SPLITSEN(A2, ","))
    =SPATIES.WISSEN(TEKST.SPLITSEN(A2; ","))

    SPATIES.WISSEN werkt in één keer op elke cel van de rij en haalt de spaties aan het begin en eind weg. Alle vijf de stukjes zijn nu 7 tekens: 25-0412, 25-0415, 25-0420, 25-0431 en 25-0437.

  4. =NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2, ",")))
    =NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2; ",")))

    NAAR.KOLOM zet de rij van 5 om in een kolom van 5, van C2 tot en met C6. Die kolom gebruik je direct als zoekwaarden voor X.ZOEKEN of als criteria voor AANTAL.ALS.

Stap voor stap

  1. TEKST.SPLITSEN(A2; ",") knipt de tekst bij de 4 komma's, wat 5 stukjes in één rij geeft: "25-0412", " 25-0415 ", "25-0420", " 25-0431 " en " 25-0437".
  2. De lengtes van de stukjes zijn 7, 9, 7, 10 en 8 tekens. Alleen het eerste en het derde zijn schoon.
  3. SPATIES.WISSEN haalt de spaties aan het begin en eind van elk stukje weg: 9 - 2 = 7, 10 - 3 = 7 en 8 - 1 = 7. Alle vijf zijn nu 7 tekens.
  4. NAAR.KOLOM zet de rij van 5 onder elkaar in een kolom van 5. Die loopt van C2 tot en met C6: 25-0412, 25-0415, 25-0420, 25-0431 en 25-0437.

Waarom het werkt

TEKST.SPLITSEN geeft een matrix terug en SPATIES.WISSEN accepteert een matrix en maakt elk element schoon, dus je hebt geen hulpkolom per stukje nodig.

NAAR.KOLOM verandert daarna de vorm van een rij in een kolom, de vorm die opzoekfuncties en AANTAL.ALS-criteria verwachten. Het geheel is één doorlopende formule die meebeweegt als de geplakte tekst verandert.

Let op

  • Een komma aan het eind ("25-0437,") maakt een leeg laatste stukje en je krijgt een lege rij onderaan de kolom. Zet het vierde argument van TEKST.SPLITSEN op WAAR om lege stukjes over te slaan.
  • Tekst uit een pdf of webpagina bevat vaak harde spaties, TEKEN(160). Vervang die eerst met SUBSTITUEREN(A2; TEKEN(160); " ").

  • Scheidt de klant de nummers ook met puntkomma's of regeleinden, dan mist een enkele komma die en krijg je één lang stuk. Geef TEKST.SPLITSEN een lijst scheidingstekens, bijvoorbeeld {","\";"}.

    Een regeleinde is TEKEN(10) en moet ook in de lijst met scheidingstekens staan.

Varianten

=SPATIES.WISSEN(TEKST.SPLITSEN(A2, , ","))
=SPATIES.WISSEN(TEKST.SPLITSEN(A2; ; ","))

Zet de komma in het derde argument (het scheidingsteken voor rijen) in plaats van het tweede. TEKST.SPLITSEN geeft dan direct een kolom terug en je hebt geen NAAR.KOLOM nodig. Dezelfde vijf nummers.

=NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2, ",", , WAAR)))
=NAAR.KOLOM(SPATIES.WISSEN(TEKST.SPLITSEN(A2; ","; ; WAAR)))

Dezelfde formule met het vierde argument WAAR, zodat een komma aan het eind of twee komma's achter elkaar ("25-0415,,25-0420") geen lege rijen opleveren. Gebruik dit als de lijst van de klant niet voorspelbaar is.

=SPATIES.WISSEN(DEEL(SUBSTITUEREN(A2, ",", HERHALING(" ", 100)), REEKS(5, , 0) * 100 + 1, 100))
=SPATIES.WISSEN(DEEL(SUBSTITUEREN(A2; ","; HERHALING(" "; 100)); REEKS(5; ; 0) * 100 + 1; 100))

De oudere manier zonder TEKST.SPLITSEN: vervang elke komma door 100 spaties, haal stukken van 100 tekens op vanaf positie 1, 101, 201, 301 en 401 en maak elk stuk schoon. Je krijgt dezelfde vijf nummers.

Er zijn twee beperkingen: je geeft het aantal stukjes (5) zelf op, en de stukjes samen, zonder de komma's, mogen niet langer zijn dan 100 tekens. Bij een langere lijst wordt een stuk afgekapt of ontbreekt het, zonder foutmelding.

Alle combinaties