Voorloopnullen toevoegen in Excel

Deze zelfstudie behandelt de verschillende methoden om voorloopnullen toe te voegen of te behouden en om voorloopnullen in Excel te verwijderen.

Telkens wanneer u getallen invoert of importeert met een of meer voorloopnullen, zoals 000652, verwijdert Excel automatisch die nullen en wordt alleen het getal zelf in de cellen weergegeven (652). Dit komt omdat de voorloopnullen niet nodig zijn voor berekeningen en niet meetellen.

Er zijn echter momenten waarop die voorloopnullen nodig zijn, zoals wanneer u ID-nummers, telefoonnummers, creditcardnummers, productcodes of postcodes, enz. invoert. Gelukkig biedt Excel ons verschillende manieren om voorloopnullen toe te voegen of te behouden in cellen. In dit artikel laten we u de verschillende manieren zien om voorloopnullen toe te voegen of te behouden en voorloopnullen te verwijderen.

Voorloopnullen toevoegen in Excel

In wezen zijn er 2 methoden die u kunt gebruiken om voorloopnullen toe te voegen: één, formatteer uw nummer als 'Tekst'; twee, gebruik aangepaste opmaak om voorloopnullen toe te voegen. De methode die u wilt gebruiken, kan afhangen van wat u met het nummer wilt doen.

Misschien wilt u een voorloopnul toevoegen wanneer u unieke ID-nummers, rekeningnummers, burgerservicenummers of postcodes, enz. invoert. Maar u gaat deze cijfers niet gebruiken voor berekeningen of in functies, dus u kunt deze het beste converteren nummers naar tekst. U zou nooit telefoonnummers of rekeningnummers optellen of middelen.

Er zijn verschillende manieren waarop u nullen voor de cijfers kunt toevoegen of invullen door ze als tekst op te maken:

• De celopmaak wijzigen in Tekst
• Een apostrof toevoegen (')
• De TEKST-functie gebruiken
• De REPT/LEN-functie gebruiken
• Gebruik CONCATENATE Functie/Ampersand-operator (&)
• Functie RECHTS gebruiken:

De celopmaak wijzigen in tekst

Dit is een van de eenvoudigste manieren om voorloopnullen aan uw getallen toe te voegen. Als u alleen getallen gaat invoeren en u wilt de voorloopnullen behouden terwijl u typt, dan is dit de methode voor u. Door de celindeling te wijzigen van Algemeen of Getal naar Tekst, kunt u Excel dwingen uw getallen als tekstwaarden te behandelen en alles wat u in de cel typt, blijft precies hetzelfde. Hier is hoe je het doet:

Selecteer de cel(len) waarin u voorloopnullen wilt toevoegen. Ga naar het tabblad 'Start', klik op de vervolgkeuzelijst 'Opmaak' in de groep Nummers en selecteer 'Tekst' in de opmaakopties.

Wanneer u nu uw cijfers typt, verwijdert Excel er geen voorloopnullen uit.

Mogelijk ziet u een kleine groene driehoek (foutindicator) in de linkerbovenhoek van de cel en wanneer u die cel selecteert, wordt er een waarschuwingsbord weergegeven dat aangeeft dat u het nummer als tekst hebt opgeslagen.

Om de foutmelding te verwijderen, selecteert u de cel(len), klikt u op het waarschuwingsteken en selecteert u vervolgens 'Fout negeren' in de lijst.

U kunt ook telefoonnummers typen met spatie of koppelteken tussen nummer, Excel behandelt deze nummers automatisch als tekst.

Leading gebruiken Apostrof ( ' )

Een andere manier om voorloopnullen in Excel toe te voegen, is door een apostrof (') aan het begin van het getal toe te voegen. Hierdoor wordt Excel gedwongen een getal als tekst in te voeren.

Typ gewoon een apostrof voor cijfers en druk op 'Enter'. Excel laat de voorloopnullen intact, maar de (') is niet zichtbaar in het werkblad, tenzij u de cel selecteert.

Tekstfunctie gebruiken

De bovenstaande methode voegt nullen toe aan getallen terwijl je ze typt, maar als je al een lijst met getallen hebt en je wilt voorloopnullen ervoor invullen, dan is de TEKST-functie de juiste methode voor jou. Met de TEKST-functie kunt u getallen naar tekstreeksen converteren terwijl u aangepaste opmaak toepast.

De functie Syntaxis van TEKST:

= TEKST( waarde, formaat_tekst)

Waar,

• waarde - Het is de numerieke waarde die u naar tekst moet converteren en opmaak moet toepassen.
• format_text – is het formaat dat u wilt toepassen.

Met de TEKST-functie kunt u aangeven hoeveel cijfers uw nummer moet hebben. Als u bijvoorbeeld wilt dat uw getallen 8 cijfers lang zijn, typt u 8 nullen in het tweede argument van de functie: "00000000". Als u een 6-cijferig nummer in een cel heeft, voegt de functie handmatig 2 voorloopnullen toe en als u 2-cijferige getallen zoals 56 hebt, zijn de rest nullen (00000056).

Om bijvoorbeeld voorloopnullen toe te voegen en de getallen 6-cijferig te maken, gebruikt u deze formule:

=TEKST(A2,"000000")

Omdat we 6 nullen hebben in het tweede argument van de formule, converteert de functie de cijferreeks naar een tekstreeks en voegt 5 voorloopnullen toe om de reeks 6 cijfers lang te maken.

Opmerking: Vergeet niet om de formaatcodes tussen dubbele aanhalingstekens in de functie te plaatsen.

Nu kunt u dezelfde formule toepassen op de rest van de cellen door de vulgreep te slepen. Zoals je kunt zien, zet de functie de getallen om in teksten en voegt voorloopnullen toe aan de getallen, zodat het totale aantal cijfers 6 is.

TEXT-functie retourneert altijd waarde als een tekenreeks, geen getal, dus u kunt ze niet gebruiken in rekenkundige berekeningen, maar u kunt ze nog steeds gebruiken in opzoekformules zoals VERT.ZOEKEN of INDEX/VERGELIJKEN om de details van een product met behulp van product-ID's.

CONCATENATE-functie/ampersand-operator (&) gebruiken

Als u een vast aantal voorloopnullen vóór alle getallen in een kolom wilt toevoegen, kunt u de functie CONCATENATE of de ampersand-operator (&) gebruiken.

Syntaxis van de functie CONCATENATE:

= SAMENVOEGEN(tekst1, [tekst2], ...)

Waar,

Tekst 1 - Het aantal nullen dat vóór het nummer moet worden ingevoegd.

tekst2 – Het originele nummer of de celverwijzing

Syntaxis van Ampersand-operator:

=Waarde_1 & Waarde_2 

Waar,

Waarde_1 zijn de voorloopnullen die vóór het getal moeten worden ingevoegd en Waarde_2 is het getal.

Als u bijvoorbeeld slechts twee nullen vóór een getal wilt toevoegen, gebruikt u een van deze formules:

=CONCATENATE("00",A2)

Het eerste argument is twee nullen (“00”) omdat we twee nullen voor het getal in A2 willen invullen (wat het tweede argument is).

Of,

="00"&A2

Hier is het eerste argument 2 nullen, gevolgd door de '&'-operator, en het tweede argument is het getal.

Zoals u kunt zien, voegt de formule slechts twee voorloopnullen toe aan alle getallen in een kolom, ongeacht het aantal cijfers dat het getal bevat.

Beide formules voegen een bepaald aantal nullen toe vóór de originele getallen en slaan ze op als tekstreeksen.

De REPT/LEN-functie gebruiken

Als u voorloopnullen wilt toevoegen aan numerieke of alfanumerieke gegevens en de tekenreeks wilt converteren naar tekst, gebruik dan de REPT-functie. De REPT-functie wordt gebruikt om een ​​of meer tekens een bepaald aantal keren te herhalen. Deze functie kan ook worden gebruikt om vaste getallen van voorloopnullen voor het getal in te voegen.

=REPT(tekst, aantal_tijden)

Waarbij 'tekst' het teken is dat we willen herhalen (in ons geval '0') en het argument 'aantal_tijden' het aantal keren is dat we dat teken willen herhalen.

Als u bijvoorbeeld vijf nullen voor getallen wilt genereren, ziet de formule er als volgt uit:

=HERHALEN(0,5)&A2

Wat de formule doet, is 5 nullen herhalen en de getallenreeks in A2 samenvoegen en het resultaat retourneren. Vervolgens wordt de formule toegepast op cel B2:B6 met behulp van de vulgreep.

De bovenstaande formule voegt een vast aantal nullen toe voor het nummer, maar de totale lengte van het nummer varieert afhankelijk van het nummer.

Als u waar nodig voorloopnullen wilt toevoegen om een ​​specifiek teken met lange (vaste lengte) tekenreeksen te maken, kunt u de functies REPT en LEN samen gebruiken.

Syntaxis:

=REPT(tekst, aantal_tijden-LEN(tekst))&cel

Als u bijvoorbeeld vooraf ingestelde nullen wilt toevoegen aan de waarde in A2 en een tekenreeks van 5 tekens wilt maken, probeert u deze formule:

=HERHALEN(0,5-LENGTE(A2))&A2

Hier krijgt 'LEN(A2)' de totale lengte van de tekenreeks/getallen in cel A2. '5' is de maximale lengte van tekenreeksen/getallen die de cel zou moeten hebben. En het deel ‘REPT(0,5-LEN(A2))’ telt het aantal nullen op door de lengte van de string in A2 af te trekken van het maximale aantal nullen (5). Vervolgens wordt een aantal nullen samengevoegd vóór de waarde van A2 om een ​​string met een vaste lengte te maken.

Functie RECHTS gebruiken

Een andere manier om voorloopnullen vóór een tekenreeks in Excel op te vullen, is door de functie RECHTS te gebruiken.

De RECHTS-functie kan een aantal nullen toevoegen aan het begin van een getal en de meest rechtse N-tekens uit de waarde halen.

Syntaxis:

= RECHTS (tekst, aantal_tekens)

• tekst is de cel of waarde waaruit u tekens wilt extraheren.
• aantal_tekens is het aantal tekens dat uit de tekst moet worden gehaald. Als dit argument niet wordt gegeven, wordt alleen het eerste teken geëxtraheerd.

Voor deze methode voegen we het maximale aantal nullen samen met celverwijzingen die de tekenreeks in het 'tekst'-argument bevatten.

Probeer deze formule om een ​​6-cijferig getal te maken op basis van de cijferreeks in A met voorloopnullen:

=RECHTS("0000000"&A2,6)

Het eerste argument (tekst) van de formule voegt 7 nullen toe aan de waarde in A2 ("0000000"&A2) en retourneert vervolgens de meest rechtse 7 tekens, wat resulteert in enkele voorloopnullen.

Voorloopnullen toevoegen met aangepaste getalopmaak

Als u een van de bovenstaande methoden gebruikt om voorloopnullen voor de getallen te plaatsen, krijgt u altijd een tekenreeks, geen getal. En ze zullen niet veel worden gebruikt in berekeningen of in numerieke formules.

De beste manier om voorloopnullen in Excel toe te voegen, is door een aangepaste getalnotatie toe te passen. Als u voorloopnullen toevoegt door een aangepaste getalnotatie aan de cel toe te voegen, verandert dit niet de waarde van de cel, maar alleen de manier waarop deze wordt weergegeven. De waarde blijft nog steeds een getal, geen tekst.

Volg deze stappen om de getalnotatie van cellen te wijzigen:

Selecteer de cel of het celbereik waar u voorloopnullen wilt weergeven. Klik vervolgens met de rechtermuisknop ergens binnen dat geselecteerde bereik en selecteer de optie 'Cellen opmaken' in het contextmenu. Of druk op de sneltoetsen Ctrl + 1.

Ga in het venster Cellen opmaken naar het tabblad 'Nummer' en selecteer 'Aangepast' onder de categorie-opties.

Voer het aantal nullen in het vak 'Type:' in om het totale aantal cijfers op te geven dat u in een cel wilt weergeven. Als u bijvoorbeeld wilt dat het nummer 6 cijfers lang is, voert u '000000' in als de aangepaste notatiecode. Klik vervolgens op 'OK' om te solliciteren.

Hierdoor worden voorloopnullen vóór de cijfers weergegeven en als het getal minder dan 6 cijfers bevat, wordt er een nul voor ingevuld.

Getallen lijken alleen voorloopnullen te hebben, terwijl de onderliggende waarde ongewijzigd blijft. Als u een cel met aangepaste opmaak selecteert, wordt het oorspronkelijke nummer in de formulebalk weergegeven

Er zijn veel digitale tijdelijke aanduidingen die u kunt gebruiken in uw aangepaste getalnotatie. Maar er zijn slechts twee primaire tijdelijke aanduidingen die u kunt gebruiken om voorloopnullen in getallen toe te voegen.

• 0 – Het is de tijdelijke aanduiding voor cijfers die extra nullen weergeeft. Het geeft geforceerde cijfers 0-9 weer, ongeacht of het cijfer relevant is voor de waarde. Als u bijvoorbeeld 2.5 typt met de notatiecode 000.00, wordt 002.50 weergegeven.
• # – Het is de tijdelijke aanduiding voor cijfers die optionele cijfers weergeeft en geen extra nullen bevat. Als u bijvoorbeeld 123 typt met de opmaakcode 000#, wordt 0123 weergegeven.

Ook elk leesteken of ander teken dat u in de opmaakcode opneemt, wordt weergegeven zoals het is. U kunt tekens gebruiken zoals koppelteken (-), komma (,), schuine streep (/), enz.

U kunt nummers bijvoorbeeld ook opmaken als telefoonnummers door een aangepaste notatie te gebruiken.

Het dialoogvenster Code opmaken in Cellen opmaken:

Het resultaat:

Laten we deze opmaakcode toepassen in het volgende voorbeeld:

##0000

Zoals je kunt zien, voegt '0' extra nullen toe, terwijl '#' geen onbeduidende nullen toevoegt:

U kunt ook vooraf gedefinieerde indelingscodes gebruiken in het gedeelte 'Speciale indelingen' van het dialoogvenster Cellen opmaken voor postcodes, telefoonnummers en burgerservicenummers.

De volgende tabel toont getallen met voorloopnullen waar verschillende 'Speciale' formaatcodes worden toegepast op verschillende kolommen:

Voorloopnullen verwijderen in Excel

Nu hebt u geleerd hoe u voorloopnullen in Excel kunt toevoegen. Laten we eens kijken hoe u de voorloopnullen uit het aantal tekenreeksen kunt verwijderen. Soms, wanneer u gegevens uit een externe bron importeert, kunnen getallen eindigen met prefix nullen en worden ze opgemaakt als tekst. In dergelijke gevallen moet u de voorloopnullen verwijderen en ze terug converteren naar getallen, zodat u ze in formules kunt gebruiken.

Er zijn verschillende manieren waarop u voorloopnullen in Excel kunt verwijderen en we zullen ze één voor één zien.

Verwijder voorloopnullen door de celopmaak te wijzigen

Als voorloopnullen zijn toegevoegd door aangepaste getalnotatie, kunt u deze eenvoudig verwijderen door de opmaak van de cellen te wijzigen. U kunt zien of uw cellen een aangepaste opmaak hebben door naar de adresbalk te kijken (nullen zijn zichtbaar in de cel, niet in de adresbalk).

Om de vooraf ingestelde nullen te verwijderen, selecteert u de cellen met de voorloopnullen, klikt u op het vak 'Getalnotatie' en selecteert u de opmaakoptie 'Algemeen' of 'Nummer'.

Nu zijn de voorloopnullen verdwenen:

Voorloopnullen verwijderen door de tekst om te zetten in cijfers

Als uw voorloopnullen zijn toegevoegd door het celformaat te wijzigen of door apostrofs voor de getallen toe te voegen of automatisch zijn toegevoegd bij het importeren van gegevens, is de eenvoudigste manier om ze naar getallen te converteren door de optie Foutcontrole te gebruiken. Hier is hoe je het doet:

U kunt deze methode gebruiken als uw getallen links zijn uitgelijnd en uw cellen een klein groen driehoekje (een foutindicator) in de linkerbovenhoek van de cellen hebben. Dit betekent dat de getallen zijn opgemaakt als tekst.

Selecteer die cellen en u ziet een gele waarschuwing rechtsboven in de selectie. Klik vervolgens op de optie 'Converteren naar nummer' in de vervolgkeuzelijst.

Uw nullen worden verwijderd en de getallen worden terug geconverteerd naar de getalnotatie (Rechts uitgelijnd).

Voorloopnullen verwijderen door Vermenigvuldigen/delen door 1

Een andere gemakkelijke en beste manier om de voorloop te verwijderen, is door de getallen te vermenigvuldigen of te delen met 1. Het delen of vermenigvuldigen van de waarde verandert de waarde niet, het converteert de waarde gewoon terug naar een getal en verwijdert de voorloopnullen.

Typ hiervoor de formule in het onderstaande voorbeeld in een cel en druk op ENTER. De voorloopnullen worden verwijderd en de tekenreeks wordt weer geconverteerd naar een getal.

Pas deze formule vervolgens toe op andere cellen met behulp van de vulgreep.

U kunt dezelfde resultaten bereiken door de opdracht 'Plakken speciaal' te gebruiken. Hier is hoe:

Typ '1' numerieke waarde in een cel (laten we zeggen in B2) en kopieer die waarde.

Selecteer vervolgens de cellen waarin u de voorloopnullen wilt verwijderen. Klik vervolgens met de rechtermuisknop op de selectie en selecteer vervolgens de optie 'Plakken speciaal'.

Kies in het dialoogvenster Plakken speciaal onder Bewerking de optie 'Vermenigvuldigen' of 'Verdelen' en klik op 'OK'.

Dat is alles, uw voorloopnullen worden verwijderd en de tekenreeksen blijven als getallen.

Verwijder voorloopnullen met behulp van een formule

Een andere gemakkelijke manier om vooraf ingestelde nullen te verwijderen, is door de functie WAARDE te gebruiken. Deze methode kan handig zijn, ongeacht of uw voorloopnullen zijn toegevoegd door een andere formule of apostrof te gebruiken of door aangepaste opmaak.

=WAARDE(A1)

Het argument van de formule kan een waarde zijn of de celverwijzing die de waarde heeft. De formule verwijdert de voorloopnullen en converteert de waarde van tekst naar getal. Pas vervolgens de formule toe op de rest van de cellen.

Soms wilt u misschien de voorloopnullen verwijderen, maar wilt u de cijfers in de tekstindeling houden. In dergelijke gevallen moet u de functies TEXT() en VALUE () als volgt samen gebruiken:

=TEKST(WAARDE(A1),"#")

De functie WAARDE converteert de waarde in A1 naar een getal. Maar het tweede argument, '#' converteert de waarde terug naar tekstformaat zonder extra nullen. Als gevolg hiervan zou u de getallen krijgen zonder voorloopnullen, maar nog steeds in het tekstformaat (links uitgelijnd).

Verwijder voorloopnullen met de functie Tekst naar kolommen van Excel

Nog een andere manier om voorloopnullen te verwijderen, is door de functie Tekst naar kolommen van Excel te gebruiken.

Selecteer het cellenbereik met de getallen met voorloopnullen.

Ga vervolgens naar het tabblad 'Gegevens' en klik op de knop 'Tekst naar kolommen' in de groep Gegevenshulpmiddelen.

De wizard 'Tekst naar kolommen converteren' verschijnt. Selecteer in stap 1 van 3 ‘Gescheiden’ en klik op ‘Volgende’.

Schakel in stap 2 van 3 alle scheidingstekens uit en klik op 'Volgende'.

Laat bij de laatste stap de optie Kolomgegevensformaat op 'Algemeen' staan ​​en kies de bestemming (de eerste cel van het bereik) waar u uw getallen wilt hebben zonder voorloopnullen. Klik vervolgens op 'Voltooien'

En u krijgt de nummers met voorloop verwijderd in een aparte kolom, zoals hieronder weergegeven.

Dat is alles.