hoe voorloopnullen toe te voegen in Excel (5 eenvoudige manieren)

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

bijvoorbeeld:

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

• Het formaat omzetten naar tekst
• met behulp van aangepaste nummeropmaak
• met behulp van tekstfunctie
• met behulp van rept/len functies
• met behulp van VBA

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

laten we eens kijken hoe elk van deze werken.,

voeg voorloopnullen toe door het formaat naar tekst te converteren

wanneer te gebruiken: wanneer u een kleine numerieke dataset hebt en u van plan bent dit handmatig te bewerken.

stel dat u werknemers-ID ’s van de marketingafdeling hebt zoals hieronder weergegeven en u wilt deze ID’ s consistent laten lijken door voorloopnullen toe te voegen.

dus probeer je de id te veranderen door voorloopnullen in te voeren (00001 in plaats van 1).

maar tot uw verbazing converteert Excel het terug naar 1.,

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

nu, hoe frustrerend het ook voor u kan zijn, Excel heeft zijn redenen.

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

Dit is wat u moet doen:

1. Selecteer de cellen waarin u handmatig voorloopnullen wilt toevoegen.
2. ga naar Home → Number Group en selecteer tekst uit de drop-down.,

dat is het!

wanneer u nu handmatig voorloopnullen invoert, voldoet Excel gemakkelijk.

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

voeg voorloopnullen toe met behulp van aangepaste nummeropmaak

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

wanneer u een getal in een specifieke opmaak weergeeft, verandert dit de onderliggende waarde van het getal niet., Ik kan bijvoorbeeld het nummer 1000 weergeven als 1000 of 1000 of 1000.00 of 001000 of 26-09-1902 (zelfs datums zijn nummers in de backend in Excel).

op alle verschillende manieren om het getal weer te geven, verandert de waarde van het getal nooit. Het is alleen de manier waarop het wordt weergegeven dat is veranderd.

om voorloopnullen toe te voegen, kunnen we het formatteren om het op die manier te laten zien, terwijl de onderliggende waarde onveranderd blijft.

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

1. Selecteer de cellen waarin u voorloopnullen wilt toevoegen.,
2. ga naar Home → Number Group en klik op het dialoogvenster Starter (een kleine gekantelde pijl rechtsonder). Hiermee wordt het dialoogvenster Cellen opmaken geopend. Als alternatief kunt u ook de sneltoets gebruiken: Control + 1.
3. in het dialoogvenster Cellen opmaken, op het tabblad Getal, selecteer Aangepast in de categorie lijst.
4. voer in het veld Type 00000
5. klik op OK.,

Als u dit doet, worden alle getallen altijd weergegeven als vijf cijfers, waarbij de eerste 0 ‘ s automatisch worden toegevoegd als het aantal minder dan 5 cijfers is. 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 hebben, moet u de indeling dienovereenkomstig gebruiken.

Opmerking: Deze techniek zou alleen werken voor een numerieke dataset. In het geval dat u werknemer-ID ‘ s zoals A1, A2, A3 enzovoort, dan zijn dit tekst en zouden deze niet veranderen wanneer u het aangepaste formaat toepast zoals hierboven weergegeven.,

voeg voorloopnullen toe met behulp van de tekstfunctie

wanneer te gebruiken: wanneer u wilt dat het resultaat tekst is.met de functie

tekst kunt u de waarde naar het gewenste formaat wijzigen.

Als u bijvoorbeeld 1 als 001 wilt weergeven, kunt u daarvoor de tekstfunctie gebruiken.

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

zo kunt u voorloopnullen toevoegen met behulp van de tekstfunctie:

1. Als u de getallen in kolom A hebt (zeg uit A2:A100), selecteer dan B2:B100 en voer de volgende formule in:
   =TEXT(A2,”00000″)
2. druk op Control + Enter om de formule op alle geselecteerde cellen toe te passen.

Dit toont alle getallen als vijf cijfers, waarbij de eerste 0 ‘ s automatisch worden toegevoegd als het aantal minder dan 5 cijfers is.,

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

Opmerking: Deze techniek zou alleen werken voor een numerieke dataset. In het geval dat u werknemer-ID ‘ s zoals A1, A2, A3 enzovoort, dan zijn dit tekst en zouden deze niet veranderen wanneer u het aangepaste formaat toepast zoals hierboven weergegeven.

voeg voorloopnullen toe met behulp van de functies REPT en LEN

wanneer te gebruiken: wanneer u een gegevensverzameling hebt die numeriek / alfanumeriek is en u wilt dat het resultaat tekst is.,

het nadeel van het gebruik van de tekst functie was dat het alleen zou werken met numerieke gegevens. Maar in het geval dat u een alfanumerieke dataset hebt (bijvoorbeeld A1, A2, A3, enzovoort), dan zou de tekstfunctie mislukken.

in dergelijke gevallen doet een combinatie van de functie REPT en LEN de truc.

Hier is hoe het te doen:

Dit zou alle getallen / tekenreeksen 5 tekens lang maken met voorloopnullen waar nodig.

zo werkt deze formule:

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

voeg voorloopnullen toe met behulp van Custom Function (VBA)

als het toevoegen van voorloopnullen in Excel iets is dat u heel vaak moet doen, is het gebruik van een aangepaste functie een goed idee.,

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

'Code by Sumit Bansal from http://trumpexcel.comFunction AddLeadingZeroes(ref As Range, Length As Integer)Dim i As IntegerDim Result As StringDim StrLen As IntegerStrLen = Len(ref)For i = 1 To LengthIf i <= StrLen ThenResult = Result & Mid(ref, i, 1)ElseResult = "0" & ResultEnd IfNext iAddLeadingZeroes = ResultEnd Function

voeg gewoon deze code toe aan het module-codevenster, en u zult deze net als elke andere werkbladfunctie kunnen gebruiken.

OF Maak een invoegtoepassing voor het en in staat zijn om het te delen met uw collega ‘ s.

u kunt ook de volgende Excel-Tutorials leuk vinden:

• Hoe voorloopnullen in Excel te verwijderen
• spaties verwijderen in Excel – voorloop, achterloop en dubbel
• zes geweldige dingen die Excel aangepaste Nummerindeling voor u kan doen.,
• 10 manieren om gegevens in Excel-Spreadsheets op te schonen.
• Hoe cellen in Excel te vergrendelen.
• Hoe decimalen toe te voegen in Excel (automatisch)