Voorloopnullen toevoegen in Excel - Alles wat u moet weten

Er zijn situaties waarin u voorloopnullen moet toevoegen aan een gegevensset in Excel. Dit kan het geval zijn als u records bijhoudt in Excel, zoals werknemers-ID's of transactie-ID's.

U wilt bijvoorbeeld een consistent uiterlijk in uw gegevensset krijgen, zoals hieronder wordt weergegeven:

In deze zelfstudie leert u verschillende manieren om voorloopnullen in Excel toe te voegen:

• Het formaat naar tekst converteren
• Aangepaste nummeropmaak gebruiken
• Tekstfunctie gebruiken
• REPT/LEN-functies gebruiken
• VBA gebruiken

Elk van deze methoden heeft een aantal voor- en nadelen (die in elke sectie worden behandeld).

Laten we eens kijken hoe elk van deze werkt.

Voorloopnullen toevoegen door de opmaak naar tekst te converteren

Wanneer te gebruiken: Wanneer u een kleine numerieke gegevensset hebt en u van plan bent deze bewerking handmatig uit te voeren.

Stel dat u werknemers-ID's van de marketingafdeling heeft, zoals hieronder weergegeven, en dat u deze ID's consistent wilt laten lijken door voorloopnullen toe te voegen.

Dus je probeert de id te wijzigen door voorloopnullen in te voeren (00001 in plaats van 1).

Maar tot uw verbazing converteert Excel het weer naar 1.

Dit gebeurt omdat Excel begrijpt dat 00001 en 1 dezelfde nummers zijn en dezelfde weergaveregels moeten volgen.

Hoe frustrerend het ook voor u is, Excel heeft zijn redenen.

Dus om het werk gedaan te krijgen zonder Excel-regels te buigen, moet u profiteren van het feit dat deze regel niet van toepassing is op tekstopmaak.

Dus hier is wat je moet doen:

1. Selecteer de cellen waaraan u handmatig voorloopnullen wilt toevoegen.
2. Ga naar Start → Nummergroep en selecteer Tekst in de vervolgkeuzelijst.

Dat is het!

Als u nu handmatig voorloopnullen invoert, voldoet Excel daar gemakkelijk aan.

Let op: Wanneer u het formaat naar tekst converteert, werken sommige Excel-functies niet goed. De functie SUM/COUNT negeert bijvoorbeeld de cel omdat deze in de tekstindeling is.

Voorloopnullen toevoegen door aangepaste getalopmaak te gebruiken

Wanneer te gebruiken: wanneer u een numerieke gegevensset hebt en u wilt dat het resultaat numeriek is (geen tekst).

Wanneer u een getal in een specifieke indeling weergeeft, verandert dit niet de onderliggende waarde van het getal. Ik kan het getal 1000 bijvoorbeeld weergeven als 1000 of 1.000 of 1000.00 of 001000 of 26-09-1902 (even datums zijn getallen in de backend in Excel).

In alle verschillende manieren om het getal weer te geven, verandert de waarde van het getal nooit. Alleen de manier waarop het wordt weergegeven, is veranderd.

Om voorloopnullen toe te voegen, kunnen we het zo opmaken dat het op die manier wordt weergegeven, terwijl de onderliggende waarde ongewijzigd blijft.

Hier zijn de stappen om deze techniek te gebruiken om voorloopnullen in Excel toe te voegen:

1. Selecteer de cellen waaraan u voorloopnullen wilt toevoegen.
2. Ga naar Start → Nummergroep en klik op de dialoogstarter (een kleine schuine pijl rechtsonder). Dit opent het dialoogvenster Cellen opmaken. Als alternatief kunt u ook de sneltoets gebruiken: Control + 1.
3. Selecteer in het dialoogvenster Cellen opmaken op het tabblad Getal de optie Aangepast in de lijst Categorie.
4. Voer in het veld Type 00000 . in
5. Klik OK.

Als u dit doet, worden alle getallen altijd weergegeven als vijf cijfers, waarbij voorloopnullen automatisch worden toegevoegd als het getal minder dan 5 cijfers bevat. Dus 10 zou 00010 worden en 100 zou 00100 worden.

In dit geval hebben we zes nullen gebruikt, maar als uw gegevens getallen met meer cijfers bevatten, moet u het formaat dienovereenkomstig gebruiken.

Opmerking: deze techniek werkt alleen voor een numerieke gegevensset. Als u werknemers-ID's heeft zoals A1, A2, A3 enzovoort, dan zijn deze tekst en zouden ze niet veranderen als u de aangepaste indeling toepast zoals hierboven weergegeven.

Voorloopnullen toevoegen met behulp van de TEKST-functie

Wanneer te gebruiken: Wanneer u wilt dat het resultaat tekst is.

Met de TEKST-functie kunt u de waarde wijzigen in het gewenste formaat.

Als u bijvoorbeeld wilt dat 1 wordt weergegeven als 001, kunt u daarvoor de functie TEKST gebruiken.

Onthoud echter dat de TEKST-functie het formaat zou veranderen en het TEKST zou maken. Dit betekent dat wanneer u 1 maakt als 001, Excel het nieuwe resultaat behandelt als tekst met drie tekens (net als abc of xyz).

Hier leest u hoe u voorloopnullen toevoegt met behulp van de TEKST-functie:

1. Als je de getallen in kolom A hebt (bijvoorbeeld van A2:A100), selecteer dan B2:B100 en voer de volgende formule in:
   =TEKST(A2,”00000″)
2. Druk op Control + Enter om de formule op alle geselecteerde cellen toe te passen.

Hierdoor worden alle getallen weergegeven als vijf cijfers, waarbij voorloopnullen automatisch worden toegevoegd als het getal kleiner is dan 5 cijfers.

Een voordeel van het converteren van gegevens naar tekst is dat u het nu kunt gebruiken in opzoekformules zoals VERT.ZOEKEN of INDEX/MATCH om de details van een werknemer op te halen met behulp van zijn/haar werknemers-ID.

Opmerking: deze techniek werkt alleen voor een numerieke gegevensset. In het geval dat u werknemers-ID's heeft zoals A1, A2, A3 enzovoort, dan zijn dit tekst en zouden ze niet veranderen wanneer u het aangepaste formaat toepast zoals hierboven weergegeven.

Voorloopnullen toevoegen met behulp van de REPT- en LEN-functies

Wanneer te gebruiken: Wanneer u een gegevensset hebt die numeriek/alfanumeriek is en u wilt dat het resultaat tekst is.

Het nadeel van het gebruik van de TEKST-functie was dat deze alleen zou werken met numerieke gegevens. Maar als je een alfanumerieke dataset hebt (zeg A1, A2, A3, enzovoort), dan zou de TEXT-functie mislukken.

In dergelijke gevallen is een combinatie van de REPT- en LEN-functie voldoende.

Hier is hoe het te doen:

1. Als je de getallen in kolom A hebt (bijvoorbeeld van A2:A100), selecteer dan B2:B100 en voer de volgende formule in:
   =HERHALEN(0,5-LENGTE(A2))&A2
2. Druk op Control + Enter om de formule op alle geselecteerde cellen toe te passen.

Dit zou alle getallen/strings 5 ​​karakters lang maken met voorloopnullen waar nodig.

Zo werkt deze formule:

• LEN(A2) geeft de lengte van de string/getallen in de cel.
• =REPT(0,5-LEN(A2)) zou het getal 0 opleveren dat moet worden toegevoegd. Hier heb ik 5 in de formule gebruikt, omdat dat de maximale lengte was van tekenreeksen / getallen in mijn dataset. U kunt dit aanpassen aan uw gegevens.
• =REPT(0,5-LEN(A2))&A2 zou gewoon het aantal nullen optellen bij de waarde van de cel. Als de waarde in de cel bijvoorbeeld 123 is, zou dit 00123 opleveren.

Voorloopnullen toevoegen met behulp van Custom Function (VBA)

Als het toevoegen van voorloopnullen in Excel iets is dat u vrij vaak moet doen, is het een goed idee om een ​​aangepaste functie te gebruiken.

Hier is de VBA-code die een eenvoudige functie maakt voor het toevoegen van voorloopnullen:

'Code door Sumit Bansal van http://trumpexcel.com Functie AddLeadingZeroes(ref As Range, Length As Integer) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len(ref) For i = 1 To Length If i <= StrLen Dan Resultaat = Resultaat & Mid(ref, i, 1) Anders Resultaat = "0" & ​​Resultaat Eind Als Volgende i AddLeadingZeroes = Resultaat Eindfunctie

Voeg deze code eenvoudig toe aan het modulecodevenster en u kunt deze net als elke andere werkbladfunctie gebruiken.

Of maak er een invoegtoepassing voor en deel deze met uw collega's.