Tekst naar kolommen is een geweldige functie in Excel die veel meer lof verdient dan gewoonlijk.
Zoals de naam al doet vermoeden, wordt het gebruikt om de tekst in meerdere kolommen te splitsen. Als u bijvoorbeeld een voornaam en achternaam in dezelfde cel heeft, kunt u deze gebruiken om deze snel in twee verschillende cellen te splitsen.
Dit kan erg handig zijn wanneer u uw gegevens uit databases haalt of importeert uit andere bestandsindelingen zoals tekst of CSV.
In deze zelfstudie leert u over veel nuttige dingen die u kunt doen met Tekst naar kolommen in Excel.
Waar vind ik tekst naar kolommen in Excel
Om toegang te krijgen tot Tekst naar kolommen, selecteert u de dataset en gaat u naar Gegevens → Hulpmiddelen voor gegevens → Tekst naar kolommen.
Dit zou de wizard Tekst naar kolommen converteren openen.
Deze wizard heeft drie stappen en vereist enkele gebruikersinvoer voordat de tekst in kolommen wordt gesplitst (u zult in onderstaande voorbeelden zien hoe deze verschillende opties kunnen worden gebruikt).
Om toegang te krijgen tot Tekst naar kolommen, kunt u ook de sneltoets gebruiken - ALT + A + E.
Laten we nu een duik nemen en een aantal fantastische dingen zien die u kunt doen met Tekst naar kolommen in Excel.
Voorbeeld 1 - Namen splitsen in voornaam en achternaam
Stel dat u een dataset heeft zoals hieronder weergegeven:
Om snel de voornaam en achternaam te splitsen en deze in aparte cellen te krijgen, volgt u de onderstaande stappen:
- Selecteer de dataset.
- Ga naar Gegevens → Hulpmiddelen voor gegevens → Tekst naar kolommen. Hiermee wordt de wizard Tekst naar kolommen converteren geopend.
- Zorg er in stap 1 voor dat Gescheiden is geselecteerd (wat ook de standaardselectie is). Klik op Volgende.
- Selecteer in stap 2 'Space' als scheidingsteken. Als u vermoedt dat er dubbele of driedubbele opeenvolgende spaties tussen de namen kunnen zijn, selecteert u ook de optie 'Opeenvolgende scheidingstekens als één behandelen'. Klik op Volgende.
- Selecteer in stap 3 de bestemmingscel. Als u geen doelcel selecteert, zou deze uw bestaande gegevensset overschrijven met de voornaam in de eerste kolom en achternaam in de aangrenzende kolom. Als u de originele gegevens intact wilt houden, maakt u een kopie of kiest u een andere bestemmingscel.
- Klik op Voltooien.
Dit zou u onmiddellijk de resultaten geven met de voornaam in de ene kolom en de achternaam in een andere kolom.
Opmerking:
- Deze techniek werkt goed als je de naam alleen uit de voornaam en de achternaam hebt. Als er initialen of tussennamen zijn, werkt dit mogelijk niet. Klik hier voor een gedetailleerde handleiding over het aanpakken van zaken met verschillende combinaties van namen.
- Het resultaat dat u krijgt door de functie Tekst naar kolommen te gebruiken, is statisch. Dit betekent dat als er wijzigingen zijn in de oorspronkelijke gegevens, u het proces moet herhalen om bijgewerkte resultaten te krijgen.
Lees ook: Sorteren op achternaam in Excel
Voorbeeld 2 - E-mail-ID's splitsen in gebruikersnaam en domeinnaam
Met Tekst naar kolommen kunt u uw eigen scheidingsteken kiezen om tekst te splitsen.
Dit kan worden gebruikt om e-mailadressen op te splitsen in gebruikersnamen en domeinnamen, aangezien deze worden gescheiden door het @-teken.
Stel dat u een dataset heeft zoals hieronder weergegeven:
Dit zijn enkele fictieve e-mail-ID's van een aantal coole superhelden (behalve ikzelf, ik ben gewoon een normale tijdverspilling op Netflix).
Hier zijn de stappen om deze gebruikersnamen en domeinnamen te splitsen met behulp van de functie Tekst naar kolommen.
- Selecteer de dataset.
- Ga naar Gegevens → Hulpmiddelen voor gegevens → Tekst naar kolommen. Hiermee wordt de wizard Tekst naar kolommen converteren geopend.
- Zorg er in stap 1 voor dat Gescheiden is geselecteerd (wat ook de standaardselectie is). Klik op Volgende.
- Selecteer in stap 2 Overige en voer @ in het vak rechts ervan in. Zorg ervoor dat u elke andere optie deselecteert (indien aangevinkt). Klik op Volgende.
- Wijzig de doelcel in de cel waar u het resultaat wilt hebben.
- Klik op Voltooien.
Dit zou het e-mailadres splitsen en u de voornaam en de achternaam in afzonderlijke cellen geven.
Voorbeeld 3 - Haal het hoofddomein op van de URL
Als u met web-URL's werkt, moet u soms het totale aantal unieke hoofddomeinen weten.
In het geval van bijvoorbeeld http://www.google.com/example1 en http://google.com/example2 is het hoofddomein hetzelfde, namelijk www.google.com
Stel dat u een dataset heeft zoals hieronder weergegeven:
Hier zijn de stappen om het hoofddomein van deze URL's te krijgen:
- Selecteer de dataset.
- Ga naar Gegevens → Hulpmiddelen voor gegevens → Tekst naar kolommen. Hiermee wordt de wizard Tekst naar kolommen converteren geopend.
- Zorg er in stap 1 voor dat Gescheiden is geselecteerd (wat ook de standaardselectie is). Klik op Volgende.
- Selecteer in stap 2 Overige en typ / (slash) in het vak rechts ervan. Zorg ervoor dat u elke andere optie deselecteert (indien aangevinkt). Klik op Volgende.
- Wijzig de doelcel in de cel waar u het resultaat wilt hebben.
- Klik op Voltooien.
Dit zou de URL splitsen en je het hoofddomein geven (in de derde kolom omdat er twee schuine strepen voor stonden).
Als u nu het aantal unieke domeinen wilt vinden, verwijdert u gewoon de duplicaten.
Opmerking: dit werkt goed als je alle URL's hebt die in het begin http:// hebben. Als dit niet het geval is, krijgt u het hoofddomein in de eerste kolom zelf. Het is een goede gewoonte om deze URL's consistent te maken voordat u Tekst naar kolommen gebruikt.
Voorbeeld 4 - Ongeldige datumnotaties converteren naar geldige datumnotaties
Als u uw gegevens uit databases zoals SAP/Oracle/Capital IQ haalt, of u importeert deze uit een tekstbestand, bestaat de mogelijkheid dat het datumformaat onjuist is (d.w.z. een formaat dat Excel niet als datum beschouwt).
Er zijn maar een paar indelingen die Excel kan begrijpen, en elk ander formaat moet worden geconverteerd naar een geldig formaat dat in Excel kan worden gebruikt.
Stel dat u datums heeft in het onderstaande formaat (die niet in het geldige Excel-datumformaat staan).
Hier zijn de stappen om deze om te zetten in geldige datumnotaties:
- Selecteer de dataset.
- Ga naar Gegevens → Hulpmiddelen voor gegevens → Tekst naar kolommen. Hiermee wordt de wizard Tekst naar kolommen converteren geopend.
- Zorg er in stap 1 voor dat Gescheiden is geselecteerd (wat ook de standaardselectie is). Klik op Volgende.
- Zorg er in stap 2 voor dat GEEN scheidingsteken is geselecteerd. Klik op Volgende.
- Selecteer Datum in de kolomgegevensindeling en selecteer de gewenste indeling (DMY betekent datum, maand en jaar). Wijzig ook de doelcel in de cel waar u het resultaat wilt hebben.
- Klik op Voltooien.
Dit zou deze ongeldige datumnotaties onmiddellijk omzetten in geldige datumnotaties die u in Excel kunt gebruiken.
Voorbeeld 5 - Converteer tekst naar cijfers
Wanneer u gegevens uit databases of andere bestandsindelingen importeert, worden de getallen soms geconverteerd naar tekstindeling.
Er zijn verschillende manieren waarop dit kan gebeuren:
- Met een apostrof voor het nummer. Dit leidt ertoe dat het nummer als tekst wordt behandeld.
- Getallen krijgen als resultaat van tekstfuncties zoals LEFT, RIGHT of MID.
Het probleem hiermee is dat deze getallen (die in tekstformaat zijn) worden genegeerd door Excel-functies zoals SOM en GEMIDDELDE.
Stel dat u een dataset heeft zoals hieronder weergegeven waarbij de getallen in tekstformaat staan (merk op dat deze links uitgelijnd zijn).
Dit zijn de stappen om Tekst naar kolommen te gebruiken om tekst naar getallen te converteren
- Selecteer de dataset.
- Ga naar Gegevens → Hulpmiddelen voor gegevens → Tekst naar kolommen. Hiermee wordt de wizard Tekst naar kolommen converteren geopend.
- Zorg er in stap 1 voor dat Gescheiden is geselecteerd (wat ook de standaardselectie is). Klik op Volgende.
- Zorg er in stap 2 voor dat GEEN scheidingsteken is geselecteerd. Klik op Volgende.
- Selecteer Algemeen in het gegevensformaat Kolom. Wijzig ook de doelcel in de cel waar u het resultaat wilt hebben.
- Klik op Voltooien.
Dit zou deze getallen terug converteren naar het algemene formaat dat nu in formules kan worden gebruikt.
Voorbeeld 6 - Extraheer de eerste vijf karakters van een string
Soms moet u de eerste paar tekens van een tekenreeks extraheren. Dit kan het geval zijn wanneer u transactiegegevens heeft en de eerste vijf tekens (of een ander aantal tekens) een unieke identificatie vertegenwoordigen.
In de onderstaande dataset zijn bijvoorbeeld de eerste vijf tekens uniek voor een productlijn.
Hier zijn de stappen om snel de eerste vijf tekens uit deze gegevens te extraheren met Tekst naar kolommen:
- Selecteer de dataset.
- Ga naar Gegevens → Hulpmiddelen voor gegevens → Tekst naar kolommen. Hiermee wordt de wizard Tekst naar kolommen converteren geopend.
- Zorg er in stap 1 voor dat Vaste breedte is geselecteerd. Klik op Volgende.
- Sleep in stap 2 in het gedeelte Gegevensvoorbeeld de verticale lijn en plaats deze na 5 tekens in de tekst. Klik op Volgende.
- Wijzig de doelcel in de cel waar u het resultaat wilt hebben.
- Klik op Voltooien.
Dit zou uw dataset splitsen en u de eerste vijf tekens van elke transactie-ID in één kolom geven en allemaal in de tweede kolom.
Opmerking: u kunt ook meer dan één verticale lijn instellen om de gegevens in meer dan 2 kolommen te splitsen. Klik gewoon ergens in het gebied Gegevensvoorbeeld en sleep de cursor om de scheidingslijn in te stellen.
Voorbeeld 7 - Converteer getallen met een minteken naar negatieve getallen
Hoewel dit niet iets is dat je vaak tegenkomt, maar soms merk je dat je de getallen corrigeert met mintekens achteraan en deze getallen negatief maakt.
Tekst naar kolommen is de perfecte manier om dit te regelen.
Stel dat u een dataset heeft zoals hieronder weergegeven:
Hier zijn de stappen om deze achterlopende minnen om te zetten in negatieve getallen:
- Selecteer de dataset.
- Ga naar Gegevens → Hulpmiddelen voor gegevens → Tekst naar kolommen. Hiermee wordt de wizard Tekst naar kolommen converteren geopend.
- Klik in Stap 1 op ‘Volgende’.
- Klik in stap 2 op ‘Volgende’.
- Klik in stap 3 op de knop Geavanceerd.
- Selecteer in het dialoogvenster Geavanceerde instellingen voor tekstimport de optie 'Trailing min voor negatief getal'. Klik OK.
- Stel de bestemmingscel in.
- Klik op Voltooien.
Dit zou onmiddellijk het minteken vanaf het einde van het nummer van het begin ervan plaatsen. Nu kunt u deze getallen eenvoudig gebruiken in formules en berekeningen.
