Het volgende is afkomstig van de PositSum blog. De makkelijkste manier om cellen en kolommen te splitsen, cellen en kolommen te combineren, en andere veel voorkomende OCR transcriptie en data invoer fouten in Google Sheets te corrigeren is door gebruik te maken van de gratis add-on, Paperpusher. Deze blogpost laat zien hoe je cellen en kolommen kunt splitsen als je de add-on niet wilt downloaden.
Wanneer ik tabellen kopieer-plak van de ene indeling naar een spreadsheet, kom ik vaak cellen en kolommen tegen die ik samen moet splitsen. Google Sheets gaat daar meestal goed mee om. Bijvoorbeeld, als ik een aantal door komma’s gescheiden waarden heb, zoals hieronder:
Waarden: 5, 6, 8, 12
Ik kan gewoon naar Gegevens gaan en klikken op “Tekst splitsen naar kolommen”, zoals te zien is in de schermafbeelding hieronder. Ik kan Google het automatisch laten uitzoeken, of zelf kiezen welk scheidingsteken ik nodig heb. Zo eenvoudig is het.
Maar wat als uw scheidingstekens niet consistent zijn? Neem de volgende 9 cellen als voorbeeld:
Original: 56, 56A, 180.000 honden
Wenselijke splitsing 1: 5, 56, 180.000
Wenselijke splitsing 2: 6, A, honden
In de eerste wil ik 56 in twee cellen splitsen, 5 en 6. In de tweede wil ik 56A splitsen in cijfers en een letter. In de derde wil ik het getal en het woord splitsen.
Om hiertussen te splitsen, moet ik reguliere expressies gebruiken. Dit stelt mij in staat om te kijken naar “strings”, of stukken tekst, en ze te manipuleren op basis van regels. Hoe algemener de regels, hoe minder ik hoef te schrijven. Ook moet ik, voordat ik reguliere uitdrukkingen ga gebruiken, de cellen dwingen tekst te zijn, wat ik kan doen met de formuleopdracht =TEXT, of ik kan het doen met de volgende menu-optie.
Mijn plan is om regels te maken om de juiste string in “Gewenste splitsing 1” te zetten, dan vertel ik Google gewoon om wat er over is in “Gewenste splitsing 2” te zetten. Laten we er voor gaan.
Om de “5” uit “56” te selecteren, vertel ik Google om het eerste getal in de string te selecteren. Ik doe dit door deze formule in B2 te gebruiken:
=REGEXTRACT(A2,””)
Dit vertelt Google om uit cel A2 (“56″) elk enkel cijfer van 0-9 te extraheren. Als ik dat gedaan heb, kan ik Google vertellen om in cel C2 te zetten wat er over is van cel A2. Mijn specifieke formule voor cel C2 is:
=REGEXREPLACE(A2,B2,””)
Dit vertelt Google om te kijken naar A2, en de “5” in A2 te vervangen door niets “”. Ik zal dezelfde formule gebruiken voor de cellen C3 en C4.
Voor cel B3 moet ik echter een manier vinden om Google te vertellen alleen de getallen uit cel A3 te halen. Ik doe dit met de formule
=REGEXTRACT(A3, “*”)
Dit vertelt Google om uit cel A3 elk cijfer te halen dat een willekeurig aantal keren wordt herhaald *. Dus, ik krijg 56. Ik gebruik dezelfde formule uit cel C2 om de rest in cel C3 te zetten.
Ten slotte moet ik voor cel A4 een manier vinden om Google te vertellen alleen de cijfers eruit te halen. Maar, er is een twist: de komma is inbegrepen in het nummer. Als ik de getallen eruit haal zonder de komma, dan hou ik een onhandige bungelende komma over, wat niet logisch is. Dus gebruik ik de formule:
=REGEXTRACT(A4, “**”)
Dit vertelt Google om uit A4 een willekeurig aantal cijfers * te extraheren, gevolgd door een komma , gevolgd door een willekeurig aantal cijfers *. Het zal dat eruit halen en alleen dat. Ik kan dan mijn formule herhalen voor cel C4 die ik gebruikte voor C3.
Het is uiteraard onmogelijk om mijn formules voor cellen B2, B3, en B4 te combineren, omdat ik in B2 de getallen wilde scheiden, maar in B3 en B4 wilde ik ze bij elkaar houden. Maar, ik kan mijn formule voor B3 en B4 combineren, en Google vertellen dat ik altijd het nummer wil extraheren, ongeacht of er een komma staat of niet, en ongeacht of er een spatie staat tussen het laatste nummer en de eerste letter of niet.
Dus gebruik ik de formule:
=REGEXTRACT(A3, “**|*”)
Dit gebruikt de OR operator |, en het vertelt Google om uit A3 ofwel een nummer te extraheren dat is geformatteerd met een komma, zoals “180.000”, of | een nummer dat is geformatteerd zonder een komma. Merk op dat de volgorde hier belangrijk is: Ik vertel Google eerst te controleren of er een komma is, en dan, als dat niet zo is, het getal zonder komma te extraheren. Als het in de omgekeerde volgorde was, zou Google niet controleren of er een komma is, en zou het gewoon “180” extraheren, wat de cijfers voor de komma zijn.
Het is het gemakkelijkst om cellen en kolommen te splitsen, cellen en kolommen te combineren, en andere veelvoorkomende OCR-transcriptie en gegevensinvoerfouten in Google Sheets te corrigeren met behulp van de gratis add-on, Paperpusher. Check it out!