Hur man lägger till ledande nollor i Excel

Denna handledning täcker de olika metoderna för att lägga till eller behålla inledande nollor samt ta bort inledande nollor i Excel.

När du anger eller importerar siffror med en eller flera inledande nollor, som 000652, tar Excel automatiskt bort dessa nollor, och endast själva siffran visas i cellerna (652). Detta beror på att de inledande nollorna inte är nödvändiga för beräkningar och inte räknas.

Men det finns tillfällen då de inledande nollorna är nödvändiga, som när du anger ID-nummer, telefonnummer, kreditkortsnummer, produktkoder eller postnummer, etc. Lyckligtvis ger Excel oss flera sätt att lägga till eller behålla inledande nollor i celler. I den här artikeln kommer vi att visa dig de olika sätten att lägga till eller behålla inledande nollor och ta bort inledande nollor.

Lägga till ledande nollor i Excel

I huvudsak finns det två metoder som du kan använda för att lägga till inledande nollor: en, formatera ditt nummer som "Text"; två, använd anpassad formatering för att lägga till inledande nollor. Vilken metod du vill använda kan bero på vad du vill göra med numret.

Du kanske vill lägga till inledande nolla när du anger unika ID-nummer, kontonummer, personnummer eller postnummer, etc. Men du kommer inte att använda dessa siffror för beräkningar eller i funktioner, så det är bäst att konvertera dem siffror till text. Du skulle aldrig summera eller genomsnittliga telefonnummer eller kontonummer.

Det finns flera sätt att lägga till eller lägga till nollor före siffrorna genom att formatera dem som text:

  • Ändra cellformatet till text
  • Lägga till en apostrof (')
  • Använder TEXT-funktionen
  • Använder REPT/LEN-funktionen
  • Använd CONCATENATE Function/Ampersand operator (&)
  • Använder RIGHT-funktionen

Ändra cellformatet till text

Detta är ett av de enklaste sätten att lägga till inledande nollor till dina siffror. Om du bara ska ange siffror och du vill behålla inledande nollor medan du skriver, så är det här metoden för dig. Genom att ändra cellformat från Allmänt eller Tal till Text kan du tvinga Excel att behandla dina siffror som textvärden och allt du skriver i cellen förblir exakt detsamma. Så här gör du:

Välj den eller de celler där du vill lägga till inledande nollor. Gå till fliken "Hem", klicka på rullgardinsmenyn "Format" i Nummergruppen och välj "Text" från formatalternativen.

Nu när du skriver dina siffror kommer Excel inte att ta bort någon inledande nolla från den.

Du kanske ser en liten grön triangel (felindikator) i det övre vänstra hörnet av cellen och när du väljer den cellen kommer den att visa dig en varningsskylt som indikerar att du har lagrat numret som text.

För att ta bort felmeddelandet, välj cellen/cellerna, klicka på varningstecknet och välj sedan "Ignorera fel" från listan.

Du kan också skriva telefonnummer med mellanslag eller bindestreck mellan nummer, Excel kommer automatiskt att behandla dessa nummer som text.

Använder Leading Apostrof ( ' )

Ett annat sätt att lägga till inledande nollor i Excel är att lägga till en apostrof (') i början av numret. Detta tvingar Excel att ange ett nummer som text.

Skriv bara en apostrof före eventuella siffror och tryck på "Enter". Excel lämnar de inledande nollorna intakta, men (‘) kommer inte att synas i kalkylbladet om du inte markerar cellen.

Använda textfunktionen

Ovanstående metod lägger till nollor till siffror när du skriver dem, men om du redan har en lista med siffror och du vill fylla med inledande nollor före dem, så är TEXT-funktionen rätt metod för dig. TEXT-funktionen låter dig konvertera siffror till textsträngar samtidigt som du använder anpassad formatering.

Syntax för TEXT-funktionen:

= TEXT( värde, format_text)

Var,

  • värde – Det är det numeriska värdet som du behöver för att konvertera till text och tillämpa formatering.
  • format_text – är formatet du vill använda.

Med TEXT-funktionen kan du ange hur många siffror ditt nummer ska ha. Till exempel, om du vill att dina nummer ska vara 8 siffror långa, skriv 8 nollor i det andra argumentet för funktion: "00000000". Om du har ett 6-siffrigt nummer i en cell kommer funktionen manuellt att lägga till 2 inledande nollor och om du har 2-siffriga nummer som 56 kommer resten att vara nollor (00000056).

Till exempel, för att lägga till inledande nollor och göra siffrorna 6-siffriga långa, använd den här formeln:

=TEXT(A2,"000000")

Eftersom vi har 6 nollor i formelns andra argument, konverterar funktionen nummersträngen till en textsträng och lägger till 5 inledande nollor för att göra strängen 6 siffror lång.

Notera: Kom ihåg att skriva formatkoderna inom dubbla citattecken i funktionen.

Nu kan du tillämpa samma formel på resten av cellerna genom att dra i fyllningshandtaget. Som du kan se omvandlar funktionen siffrorna till texter och lägger till inledande nollor till siffrorna så att det totala antalet siffror blir 6.

TEXT-funktionen returnerar alltid värde som en textsträng, inte ett tal, så du kommer inte att kunna använda dem i aritmetiska beräkningar men du kan fortfarande använda dem i uppslagsformler som VLOOKUP eller INDEX/MATCH för att hämta detaljerna i en produkt med produkt-ID:n.

Använda CONCATENATE Function/Ampersand Operator (&)

Om du vill lägga till ett fast antal inledande nollor före alla siffror i en kolumn kan du använda CONCATENATE-funktionen eller et-teckenoperatorn (&).

Syntax för CONCATENATE-funktionen:

=KONKATERA(text1, [text2], ...)

Var,

text1 – Antalet nollor som ska infogas före numret.

text2 – Det ursprungliga numret eller cellreferensen

Syntax för Ampersand-operator:

=Värde_1 & Värde_2

Var,

Värde_1 är de inledande nollorna att infoga före talet och Värde_2 är talet.

Om du till exempel bara vill lägga till två nollor före ett tal, använd någon av dessa formel:

=CONCATENATE("00",A2)

Det första argumentet är två nollor ("00") eftersom vi vill lägga två nollor före talet i A2 (som är det andra argumentet).

Eller, 

="00"&A2

Här är det första argumentet 2 nollor, följt av operatorn "&", och det andra argumentet är numret.

Som du kan se lägger formeln bara två inledande nollor till alla siffror i en kolumn oavsett hur många siffror talet innehåller.

Båda dessa formler sammanfogar ett visst antal nollor före de ursprungliga talen och lagrar dem som textsträngar.

Använder REPT/LEN-funktionen

Om du vill lägga till inledande nollor till numeriska eller alfanumeriska data och konvertera strängen till text, använd sedan REPT-funktionen. REPT-funktionen används för att upprepa ett eller flera tecken ett visst antal gånger. Denna funktion kan också användas för att infoga fasta antal inledande nollor före siffran.

=REPT(text; antal_ggr)

Där "text" är tecknet vi vill upprepa (i vårt fall "0") och argumentet "antal_ gånger" är antalet gånger vi vill upprepa det tecknet.

Till exempel, för att generera fem nollor före siffror, skulle formeln se ut så här:

=REPT(0,5)&A2

Vad formeln gör är att upprepa 5 nollor och sammanfoga talsträngen i A2 och returnera resultatet. Sedan appliceras formeln på cell B2:B6 med hjälp av fyllningshandtaget.

Ovanstående formel lägger till ett fast antal nollor före numret, men den totala längden på numret varierar beroende på antalet.

Om du vill lägga till inledande nollor där det behövs för att skapa en specifik tecken långa (fast längd) strängar, kan du använda REPT och LEN funktioner tillsammans.

Syntax:

=REPT(text, antal_ggr-LEN(text))&cell

Till exempel, för att lägga till prefixet nollor till värdet i A2 och göra en 5-tecken lång sträng, prova den här formeln:

=REPT(0,5-LEN(A2))&A2

Här får 'LEN(A2)' den totala längden på strängen/numren i cell A2. '5' är den maximala längden på sträng/nummer som cellen ska ha. Och 'REPT(0,5-LEN(A2))'-delen adderar antalet nollor genom att subtrahera längden på strängen i A2 från det maximala antalet nollor (5). Sedan sammanfogas ett antal nollor före värdet på A2 för att skapa en sträng med fast längd.

Använder RIGHT-funktionen

Ett annat sätt att fylla med inledande nollor före en sträng i Excel är att använda HÖGER-funktionen.

Funktionen HÖGER kan lägga till ett antal nollor i början av ett tal och extrahera de N tecknen längst till höger från värdet.

Syntax:

= HÖGER (text, antal_tecken)
  • text är cellen eller värdet du vill extrahera tecken från.
  • num_chars är antalet tecken som ska extraheras från texten. Om detta argument inte ges, kommer endast det första tecknet att extraheras.

För den här metoden sammanfogar vi det maximala antalet nollor med cellreferens som innehåller strängen i "text"-argumentet.

För att skapa ett 6-siffrigt tal baserat på nummersträngen i A med inledande nollor, prova den här formeln:

=HÖGER("0000000"&A2,6)

Det första argumentet (texten) i formeln lägger till 7 nollor till värdet i A2 ("0000000"&A2) och returnerar sedan de sju tecknen längst till höger, vilket resulterar i några inledande nollor.

Lägga till inledande nollor med anpassad nummerformatering

Om du använder någon av ovanstående metoder för att sätta inledande nollor före siffrorna, får du alltid en textsträng, inte en siffra. Och de kommer inte att vara mycket användbara i beräkningar eller i numeriska formler.

Det bästa sättet att lägga till inledande nollor i Excel är att använda en anpassad sifferformatering. Om du lägger till inledande nollor genom att lägga till ett anpassat talformat i cellen, ändras inte värdet på cellen utan bara hur det visas. Värdet kommer fortfarande att vara ett tal, inte text.

För att ändra nummerformateringen av celler, följ dessa steg:

Markera cellen eller cellintervallet där du vill visa inledande nollor. Högerklicka sedan var som helst inom det valda intervallet och välj alternativet "Formatera celler" från snabbmenyn. Eller tryck på genvägstangenterna Ctrl + 1.

I fönstret Formatera celler, gå till fliken "Nummer" och välj "Anpassad" under kategorialternativen.

Ange antalet nollor i rutan 'Typ:' för att ange det totala antalet siffror du vill visa i en cell. Om du till exempel vill att numret ska vara 6 siffror långt anger du "000000" som koden för anpassat format. Klicka sedan på "OK" för att ansöka.

Detta kommer att visa inledande nollor före siffrorna och om talet är mindre än 6 siffror, läggs noll före det.

Siffror verkar bara ha inledande nollor medan det underliggande värdet förblir oförändrat. Om du väljer en cell med anpassad formatering visar den det ursprungliga numret i formelfältet

Det finns många digitala platshållare som du kan använda i ditt anpassade nummerformat. Men det finns bara två primära platshållare du kan använda för att lägga till inledande nollor i siffror.

  • 0 – Det är sifferplatshållaren som visar extra nollor. Den visar tvingade siffror 0-9 oavsett om siffran är relevant för värdet eller inte. Om du till exempel skriver 2.5 med formatkoden 000.00 kommer den att visa 002.50.
  • # – Det är sifferplatshållaren som visar valfria siffror och som inte inkluderar extra nollor. Om du till exempel skriver 123 med formateringskoden 000# kommer den att visa 0123.

Dessutom kommer alla skiljetecken eller andra tecken som du inkluderar i formatkoden att visas som de är. Du kan använda tecken som bindestreck (-), kommatecken (,), snedstreck framåt (/) etc.

Du kan till exempel också formatera nummer som telefonnummer genom att använda anpassat format.

Dialogrutan Formatera kod i Formatera celler:

Resultatet:

Låt oss tillämpa den här formateringskoden i följande exempel:

##0000

Som du kan se kommer '0' att lägga till extra nollor medan '#' inte lägger till obetydliga nollor:

Du kan också använda fördefinierade formatkoder i avsnittet "Specialformat" i dialogrutan Formatera celler för postnummer, telefonnummer och personnummer.

Följande tabell visar siffror med inledande nollor där olika "Special"-formatkoder tillämpas på olika kolumner:

Ta bort ledande nollor i Excel

Nu har du lärt dig hur du lägger till inledande nollor i Excel, låt oss se hur du tar bort de inledande nollorna från antalet strängar. Ibland, när du importerar data från en extern källa, kan siffror sluta med prefix nollor och formateras som text. I sådana fall måste du ta bort de inledande nollorna och konvertera dem tillbaka till siffror, så att du kan använda dem i formler.

Det finns olika sätt att ta bort inledande nollor i Excel och vi kommer att se dem en efter en.

Ta bort inledande nollor genom att ändra cellformateringen

Om inledande nollor lades till genom anpassad nummerformatering, kan du enkelt ta bort dem genom att ändra formatet på cellerna. Du kan se om dina celler är anpassade formaterade genom att titta på adressfältet (nollor kommer att synas i cellen inte i adressfältet).

För att ta bort de prefixerade nollorna, markera cellerna med de inledande nollorna, klicka på rutan 'Nummerformat' och välj formateringsalternativet 'Allmänt' eller 'Nummer'.

Nu är de inledande nollorna borta:

Ta bort inledande nollor genom att konvertera texten till siffror

Om dina inledande nollor lades till genom att ändra cellformatet eller genom att lägga till apostrof före siffrorna eller automatiskt läggas till vid import av data, är det enklaste sättet att konvertera dem till siffror genom att använda alternativet för felkontroll. Så här gör du:

Du kan använda den här metoden om dina siffror är vänsterjusterade och dina celler har en liten grön triangel (En felindikator) i det övre vänstra hörnet av cellerna. Det betyder att siffrorna är formaterade som text.

Välj dessa celler och du kommer att se en gul varning längst upp till höger i markeringen. Klicka sedan på alternativet "Konvertera till nummer" i rullgardinsmenyn.

Dina nollor kommer att tas bort och siffrorna kommer att konverteras tillbaka till talformatet (högerjusterat).

Ta bort inledande nollor genom Multiplicera/Dividera med 1

Ett annat enkelt och bästa sätt att ta bort inledande är genom att multiplicera eller dividera talen med 1. Att dividera eller multiplicera värdet ändrar inte värdet, det konverterar helt enkelt tillbaka värdet till ett tal och tar bort de inledande nollorna.

För att göra detta, skriv in formeln i exemplet nedan i en cell och tryck på RETUR. De inledande nollorna kommer att tas bort och strängen konverteras tillbaka till ett tal.

Använd sedan den här formeln på andra celler med hjälp av fyllningshandtaget.

Du kan uppnå samma resultat genom att använda kommandot "Klistra in special". Här är hur:

Skriv '1' numeriskt värde i en cell (låt säga i B2) och kopiera det värdet.

Välj sedan cellerna där du vill ta bort de inledande nollorna. Högerklicka sedan på valet och välj sedan alternativet "Klistra in special".

I dialogrutan Klistra in special, under Operation, välj antingen alternativet "Multiplicera" eller "Dela" och klicka på "OK".

Det är det, dina inledande nollor tas bort och lämnar strängarna som siffror.

Ta bort inledande nollor genom att använda en formler

Ett annat enkelt sätt att ta bort nollor med prefix är att använda VÄRDE-funktionen. Den här metoden kan vara användbar oavsett om dina inledande nollor har lagts till genom att använda en annan formel eller apostrof eller genom anpassad formatering.

=VÄRDE(A1)

Formelns argument kan vara ett värde eller cellreferensen som har värdet. Formeln tar bort de inledande nollorna och konverterar värdet från text till tal. Applicera sedan formeln på resten av cellerna.

Ibland kanske du vill ta bort de inledande nollorna men vill behålla siffrorna i textformatet. I sådana fall måste du använda funktionerna TEXT() och VALUE () tillsammans så här:

=TEXT(VÄRDE(A1),"#")

Funktionen VÄRDE konverterar värdet i A1 till tal. Men det andra argumentet, '#' konverterar värdet tillbaka till textformat utan några extra nollor. Som ett resultat skulle du få siffrorna utan några inledande nollor men fortfarande i textformatet (vänsterjusterat).

Ta bort inledande nollor med hjälp av Excels funktion för text till kolumner

Ytterligare ett sätt att ta bort inledande nollor är att använda Excels text till kolumner-funktion.

Välj intervallet av celler som har talen med inledande nollor.

Gå sedan till fliken "Data" och klicka på knappen "Text till kolumner" i gruppen Dataverktyg.

Guiden "Konvertera text till kolumner" visas. I steg 1 av 3, välj "Avgränsad" och klicka på "Nästa".

I steg 2 av 3, avmarkera alla avgränsare och klicka på "Nästa".

I det sista steget, lämna alternativet Kolumndataformat som "Allmänt" och välj destinationen (den första cellen i intervallet) där du vill ha dina siffror utan inledande nollor. Klicka sedan på "Slutför"

Och du kommer att få siffrorna med inledande borttagna i en separat kolumn som visas nedan.

Det är allt.