Så här lägger du till ledande nollor i Excel - allt du behöver veta

Det finns situationer när du behöver lägga till ledande nollor till en datamängd i Excel. Detta kan vara fallet om du behåller poster i Excel, till exempel medarbetar -id eller transaktions -id.

Till exempel kanske du vill få ett konsekvent utseende i din datamängd som visas nedan:

I den här självstudien lär du dig olika sätt att lägga till ledande nollor i Excel:

• Konvertera formatet till text
• Använda anpassad nummerformatering
• Använda textfunktionen
• Använda REPT/LEN -funktioner
• Använda VBA

Var och en av dessa metoder har några fördelar och nackdelar (täcks i varje avsnitt).

Låt oss se hur var och en av dessa fungerar.

Lägg till ledande nollor genom att konvertera formatet till text

När ska man använda: När du har en liten numerisk datamängd och du planerar att göra denna redigering manuellt.

Anta att du har medarbetar -ID: n för marknadsavdelningen enligt nedan och du vill få dessa ID: er att se konsekventa genom att lägga till ledande nollor.

Så du försöker ändra id genom att ange ledande nollor (00001 istället för 1).

Men till din förvåning konverterar Excel det till 1.

Detta händer när Excel förstår att 00001 och 1 är samma nummer och bör följa samma visningsregler.

Nu så frustrerande som det kan vara för dig, har Excel sina skäl.

Så för att få jobbet gjort utan att böja Excel -regler måste du dra nytta av att denna regel inte gäller textformatering.

Så här är vad du behöver göra:

1. Markera de celler där du vill lägga till ledande nollor manuellt.
2. Gå till Hem → Nummergrupp och välj Text från rullgardinsmenyn.

Det är allt!

Nu, när du anger ledande nollor manuellt, skulle Excel lätt följa.

Varning: När du konverterar formatet till text fungerar vissa Excel -funktioner inte korrekt. Till exempel skulle SUM/COUNT -funktionen ignorera cellen eftersom den är i textformatet.

Lägg till ledande nollor med hjälp av anpassad nummerformatering

När ska man använda: När du har en numerisk datamängd och du vill att resultatet ska vara numeriskt (inte text).

När du visar ett nummer i ett specifikt format ändrar det inte det underliggande värdet på numret. Till exempel kan jag visa talet 1000 som 1000 eller 1000 eller 1000,00 eller 001000 eller 26-09-1902 (även datum är siffror i backend i Excel).

På alla olika sätt att visa numret ändras aldrig värdet på numret. Det är bara sättet som visas som ändras.

För att lägga till ledande nollor kan vi formatera det för att visa det så, medan det underliggande värdet skulle förbli oförändrat.

Här är stegen för att använda denna teknik för att lägga till ledande nollor i Excel:

1. Markera cellerna där du vill lägga till ledande nollor.
2. Gå till Hem → Nummergrupp och klicka på dialogrutan (en liten lutad pil nere till höger). Detta öppnar dialogrutan Formatera celler. Alternativt kan du också använda kortkommandot: Control + 1.
3. I dialogrutan Formatera celler på fliken Nummer väljer du Anpassad i kategorilistan.
4. I fältet Typ anger du 00000
5. Klicka på OK.

Om du gör detta visas alltid alla siffror som fem siffror, där inledande 0: er läggs till automatiskt om siffran är mindre än 5 siffror. Så 10 skulle bli 00010 och 100 skulle bli 00100.

I det här fallet har vi använt sex nollor, men om din data har siffror med fler siffror måste du använda formatet i enlighet därmed.

Obs! Denna teknik fungerar endast för en numerisk datamängd. Om du har medarbetar -ID som A1, A2, A3 och så vidare, så är dessa text och ändras inte när du använder det anpassade formatet som visas ovan.

Lägg till ledande nollor med hjälp av TEXT -funktion

När ska man använda: När du vill att resultatet ska vara text.

Med TEXT -funktionen kan du ändra värdet till önskat format.

Om du till exempel vill att 1 ska visas som 001 kan du använda TEXT -funktionen för det.

Kom dock ihåg att TEXT -funktionen skulle ändra formatet och göra det TEXT. Det betyder att när du gör 1 som 001, behandlar Excel det nya resultatet som text med tre tecken (precis som abc eller xyz).

Så här lägger du till ledande nollor med hjälp av TEXT -funktionen:

1. Om du har siffrorna i kolumn A (säg från A2: A100), välj sedan B2: B100 och ange följande formel:
   = TEXT (A2, ”00000 ″)
2. Tryck på Control + Enter för att tillämpa formeln på alla markerade celler.

Detta visar alla siffror som fem siffror, där ledande 0: er läggs till automatiskt om siffran är mindre än 5 siffror.

En fördel med att konvertera data till text är att du nu kan använda den i uppslagsformler som VLOOKUP eller INDEX/MATCH för att hämta detaljer om en anställd med hjälp av hans/hennes anställds -id.

Obs! Denna teknik fungerar endast för en numerisk datamängd. Om du har anställdas id som A1, A2, A3 och så vidare, så är dessa text och ändras inte när du använder det anpassade formatet som visas ovan.

Lägg till ledande nollor med hjälp av REPT- och LEN -funktioner

När ska man använda: När du har en datamängd som är numerisk/alfanumerisk och du vill att resultatet ska vara text.

Nackdelen med att använda TEXT -funktionen var att den bara skulle fungera med numerisk data. Men om du har en alfanumerisk dataset (säg A1, A2, A3 och så vidare), skulle TEXT -funktionen misslyckas.

I sådana fall gör en kombination av REPT- och LEN -funktionen tricket.

Så här gör du:

1. Om du har siffrorna i kolumn A (säg från A2: A100), välj sedan B2: B100 och ange följande formel:
   = REPT (0,5-LEN (A2)) & A2
2. Tryck på Control + Enter för att tillämpa formeln på alla markerade celler.

Detta skulle göra alla siffror/strängar 5 tecken långa med ledande nollor där det behövs.

Så här fungerar den här formeln:

• LEN (A2) anger längden på strängen/siffrorna i cellen.
• = REPT (0,5-LEN (A2)) skulle ge antalet 0 som ska läggas till. Här har jag använt 5 i formeln eftersom det var den maximala längden på sträng/nummer i min datamängd. Du kan ändra detta enligt dina uppgifter.
• = REPT (0,5-LEN (A2)) & A2 skulle helt enkelt lägga till antalet nollor till cellens värde. Till exempel, om värdet i cellen är 123, skulle detta returnera 00123.

Lägg till ledande nollor med hjälp av anpassad funktion (VBA)

Om du måste lägga till ledande nollor i Excel är du ganska ofta tvungen att använda en anpassad funktion.

Här är VBA -koden som skapar en enkel funktion för att lägga till ledande nollor:

'Code by Sumit Bansal from http://trumpexcel.com Function 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 Sedan Resultat = Resultat & mitten (ref, i, 1) Annat resultat = "0" & ​​Resultat slut om nästa i AddLeadingZeroes = Resultat slut funktion

Lägg bara till den här koden i modulkodfönstret, så kan du använda den precis som alla andra kalkylbladsfunktioner.

Eller skapa ett tillägg för det och kunna dela det med dina kollegor.