det finns situationer när du behöver lägga till ledande nollor till en dataset i Excel. Detta kan vara fallet om du behåller poster i Excel, till exempel anställdas ID eller Transaktions-ID.,
till exempel kanske du vill få ett konsekvent utseende i din datauppsättning som visas nedan:
i den här handledningen 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 textfunktion
- använda REPT/LEN-funktioner
li>
- använda VBA
var och en av dessa metoder har vissa fördelar och nackdelar (som omfattas av varje avsnitt).
Låt oss se hur var och en av dessa fungerar.,
denna handledning omfattar:
Lägg till ledande nollor genom att konvertera formatet till Text
När du ska använda: när du har en liten numerisk datamängd, och du planerar att göra denna redigering manuellt.
anta att du har anställdas id: N i marknadsföringsavdelningen som visas nedan och du vill få dessa ID: n att se konsekvent ut 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 tillbaka till 1.,
detta händer som 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, Excel har sina skäl.
så, för att få arbetet gjort utan att böja Excel-regler, måste du dra nytta av det faktum att denna regel inte gäller för textformatering.
Så här är vad du behöver göra:
- Välj de celler där du vill manuellt lägga till ledande nollor.
- gå till hem → Nummergrupp och välj Text från rullgardinsmenyn.,
det är det!
Nu, när du anger ledande nollor manuellt, Excel skulle lätt följa.
varning: när du konverterar formatet till Text fungerar vissa Excel-funktioner inte korrekt. SUM / COUNT-funktionen skulle till exempel ignorera cellen eftersom den är i textformatet.
Lägg till ledande nollor genom att använda Anpassad nummerformatering
När du ska använda: när du har en numerisk datauppsättning och du vill att resultatet ska vara numeriskt (inte text).
När du visar ett tal i ett visst format ändrar det inte det underliggande värdet för numret., Till exempel kan jag visa numret 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å det 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 den här tekniken för att lägga till ledande nollor i Excel:
- Välj de celler där du vill lägga till ledande nollor.,
- gå till hem → Nummergrupp och klicka på dialogrutan startprogram (en liten lutad pil längst ner till höger). Detta öppnar dialogrutan Formatera celler. Alternativt kan du också använda kortkommandot: Control + 1.
- i dialogrutan Formatceller väljer du anpassad i kategorilistan på fliken Nummer.
- i Typfältet anger du 00000
- Klicka på OK.,
genom att göra detta visas alltid alla siffror som fem siffror, där ledande 0-tal automatiskt läggs till om numret ä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 dina data har siffror med fler siffror måste du använda formatet i enlighet med detta.
Obs! Denna teknik skulle bara fungera för en numerisk datauppsättning. Om du har anställdas ID som A1, A2, A3 och så vidare, så är dessa text och skulle inte ändras när du använder det anpassade formatet som visas ovan.,
Lägg till ledande nollor genom att använda textfunktion
När du ska använda: när du vill att resultatet ska vara text.
textfunktionen låter dig ändra värdet till önskat format.
om du till exempel vill att 1 ska visas som 001 kan du använda textfunktionen för det.
kom dock ihåg att textfunktionen 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 textfunktionen:
- Om du har siffrorna i kolumn A (säg från A2:A100) väljer du B2:B100 och anger följande formel:
=TEXT(A2,”00000″) - tryck på Control + Enter för att tillämpa formeln på alla valda celler.
detta kommer att visa alla siffror som fem siffror, där ledande 0-talet automatiskt läggs till om numret är mindre än 5 siffror.,
en fördel med att konvertera data till text är att du nu kan använda den i sökformler som VLOOKUP eller INDEX/MATCH för att hämta information om en anställd som använder hans/hennes anställdas ID.
Obs! Denna teknik skulle bara fungera för en numerisk datauppsättning. Om du har anställdas ID som A1, A2, A3 och så vidare, så är dessa text och skulle inte ändras när du använder det anpassade formatet som visas ovan.
Lägg till ledande nollor genom att använda REPT-och LEN-funktioner
När du ska använda: när du har en datauppsättning som är numerisk / alfanumerisk och du vill att resultatet ska vara text.,
nackdelen med att använda textfunktionen var att den bara skulle fungera med numeriska data. Men om du har en alfanumerisk dataset (säg A1, A2, A3 och så vidare), skulle textfunktionen misslyckas.
i sådana fall gör en kombination av REPT och LEN-funktionen tricket.
här är hur man gör det:
detta skulle göra alla nummer/strängar 5 tecken långt med ledande nollor där det behövs.
Så här fungerar formeln:
- LEN(A2) ger längden på strängen / siffrorna i cellen.
- =REPT (0,5-LEN (A2)) skulle ge antalet 0 som bör läggas till., Här har jag använt 5 i formeln eftersom det var den maximala längden på sträng / tal i min dataset. Du kan ändra detta enligt dina data.
- =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 genom att använda anpassad funktion (VBA)
om du lägger till ledande nollor i Excel är något du är skyldig att göra ganska ofta, är det en bra idé 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.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
lägg helt enkelt till den här koden i modulkodsfönstret, och du kommer att kunna använda den precis som vilken annan arbetsbladsfunktion som helst.
eller skapa ett tillägg för det och kunna dela det med dina kollegor.
Du kanske också gillar följande Excel Tutorials:
- så här tar du bort ledande nollor i Excel
- ta bort mellanslag i Excel-Ledande, efterföljande och dubbel
- sex fantastiska saker Excel anpassat nummerformat kan göra för dig.,
- 10 sätt att rensa Data i Excel.
- så här låser du celler i Excel.
- så här lägger du till decimaler i Excel (automatiskt)