Denne vejledning dækker de forskellige metoder til at tilføje eller beholde indledende nuller samt fjerne indledende nuller i Excel.
Når du indtaster eller importerer tal med et eller flere foranstillede nuller, f.eks. 000652, fjerner Excel automatisk disse nuller, og kun selve tallet vises i cellerne (652). Dette skyldes, at de foranstillede nuller ikke er nødvendige for beregninger og ikke tæller.
Men der er tidspunkter, hvor disse foranstillede nuller er nødvendige, som når du indtaster ID-numre, telefonnumre, kreditkortnumre, produktkoder eller postnumre osv. Heldigvis giver Excel os flere måder at tilføje eller beholde foranstillede nuller på. i celler. I denne artikel vil vi vise dig de forskellige måder at tilføje eller beholde indledende nuller og fjerne indledende nuller.
Tilføjelse af førende nuller i Excel
Grundlæggende er der 2 metoder, som du kan bruge til at tilføje foranstillede nuller: den ene, formater dit nummer som 'Tekst'; to, brug tilpasset formatering til at tilføje foranstillede nuller. Den metode, du vil bruge, kan afhænge af, hvad du vil gøre med nummeret.
Du vil måske tilføje indledende nul, når du indtaster unikke ID-numre, kontonumre, personnumre eller postnumre osv. Men du vil ikke bruge disse tal til beregninger eller i funktioner, så det er bedst at konvertere dem tal til tekst. Du ville aldrig summere eller gennemsnittet telefonnumre eller kontonumre.
Der er flere måder, du kan tilføje eller indsætte nuller før tallene ved at formatere dem som tekst:
- Ændring af celleformatet til tekst
- Tilføjelse af en apostrof (')
- Brug af TEXT-funktionen
- Brug af REPT/LEN-funktionen
- Brug CONCATENATE Function/Ampersand operator (&)
- Brug af HØJRE funktion
Ændring af celleformat til tekst
Dette er en af de enkleste måder at tilføje indledende nuller til dine tal. Hvis du bare skal indtaste tal, og du vil beholde indledende nuller, mens du skriver, så er dette metoden for dig. Ved at ændre celleformat fra Generelt eller Tal til Tekst, kan du tvinge Excel til at behandle dine tal som tekstværdier, og alt, hvad du skriver i cellen, forbliver nøjagtigt det samme. Sådan gør du:
Vælg den eller de celler, hvor du vil tilføje foranstillede nuller. Gå til fanen 'Hjem', klik på rullemenuen 'Format' i numregruppen, og vælg 'Tekst' fra formatindstillingerne.
Nu, når du skriver dine tal, sletter Excel ikke nogen indledende nul fra den.
Du kan muligvis se en lille grøn trekant (fejlindikator) i øverste venstre hjørne af cellen, og når du vælger den celle, vil den vise dig et advarselsskilt, der indikerer, at du har gemt nummeret som tekst.
For at fjerne fejlmeddelelsen skal du vælge cellen/cellerne, klikke på advarselstegnet og derefter vælge 'Ignorer fejl' fra listen.
Du kan også skrive telefonnumre med mellemrum eller bindestreg mellem tal, Excel vil automatisk behandle disse tal som tekst.
Brug af Leading Apostrof ( ' )
En anden måde at tilføje indledende nuller i Excel er at tilføje en apostrof (') i starten af tallet. Dette vil tvinge Excel til at indtaste et tal som tekst.
Bare skriv en apostrof før ethvert tal og tryk på 'Enter'. Excel vil efterlade de foranstillede nuller intakte, men (') vil ikke være synlig i regnearket, medmindre du vælger cellen.
Brug af tekstfunktion
Ovenstående metode tilføjer nuller til tal, mens du skriver dem, men hvis du allerede har en liste over tal, og du vil sætte indledende nuller foran dem, så er TEXT-funktionen den rigtige metode for dig. TEXT-funktionen giver dig mulighed for at konvertere tal til tekststrenge, mens du anvender brugerdefineret formatering.
Syntaks af TEKST-funktionen:
= TEXT( værdi, format_tekst)Hvor,
- værdi - Det er den numeriske værdi, du skal konvertere til tekst og anvende formatering.
- format_tekst – er det format, du vil anvende.
Med TEXT-funktionen kan du angive, hvor mange cifre dit nummer skal have. For eksempel, hvis du vil have dine tal til at være 8 cifre, så skriv 8 nuller i funktionens andet argument: "00000000". Hvis du har et 6-cifret tal i en celle, vil funktionen manuelt tilføje 2 foranstillede nuller, og hvis du har 2-cifrede tal som 56, vil resten være nuller (00000056).
For at tilføje indledende nuller og gøre tallene 6-cifrede, skal du f.eks. bruge denne formel:
=TEKST(A2;000000")Da vi har 6 nuller i formlens andet argument, konverterer funktionen talstrengen til en tekststreng og tilføjer 5 indledende nuller for at gøre strengen 6 cifre lang.
Bemærk: Husk at sætte formatkoderne i dobbelte anførselstegn i funktionen.
Nu kan du anvende den samme formel på resten af cellerne ved at trække i udfyldningshåndtaget. Som du kan se, konverterer funktionen tallene til tekster og tilføjer indledende nuller til tallene, så det samlede antal cifre er 6.
TEXT-funktionen returnerer altid værdi som en tekststreng, ikke et tal, så du vil ikke være i stand til at bruge dem i aritmetiske beregninger, men du kan stadig bruge dem i opslagsformler såsom VLOOKUP eller INDEX/MATCH for at hente detaljerne i en produkt ved hjælp af produkt-id'er.
Brug af CONCATENATE Function/Ampersand Operator (&)
Hvis du vil tilføje et fast antal foranstillede nuller før alle tal i en kolonne, kan du bruge CONCATENATE-funktionen eller og-tegn-operatoren (&).
Syntaks for CONCATENATE-funktionen:
=CONCATENATE(tekst1; [tekst2], ...)Hvor,
tekst1 – Antallet af nuller, der skal indsættes før tallet.
tekst2 – Det originale nummer eller cellereference
Syntaks for ampersand-operator:
=Værdi_1 & Værdi_2 Hvor,
Værdi_1 er de foranstillede nuller, der skal indsættes før tallet, og Værdi_2 er tallet.
Hvis du f.eks. kun vil tilføje to nuller før et tal, skal du bruge en af disse formler:
=SAMMENHÆNG("00",A2)Det første argument er to nuller ("00"), fordi vi vil sætte to nuller foran tallet i A2 (som er det andet argument).
Eller,
="00"&A2Her er det første argument 2 nuller, efterfulgt af '&' operator, og det andet argument er tallet.
Som du kan se, tilføjer formlen kun to foranstillede nuller til alle tallene i en kolonne, uanset hvor mange cifre tallet indeholder.
Begge disse formler forbinder et vist antal nuller før de oprindelige tal og gemmer dem som tekststrenge.
Brug af REPT/LEN-funktionen
Hvis du vil tilføje indledende nuller til numeriske eller alfanumeriske data og konvertere strengen til tekst, skal du bruge REPT-funktionen. REPT-funktionen bruges til at gentage et eller flere tegn et bestemt antal gange. Denne funktion kan også bruges til at indsætte faste tal af indledende nuller før tallet.
=REPT(tekst; antal_gange)Hvor 'tekst' er det tegn, vi ønsker at gentage (i vores tilfælde '0'), og 'antal_gange'-argumentet er antallet af gange, vi ønsker at gentage det pågældende tegn.
For eksempel, for at generere fem nuller før tal, vil formlen se sådan ud:
=REPT(0,5)&A2Det, formlen gør, er at gentage 5 nuller og forbinde talstrengen i A2 og returnere resultatet. Derefter anvendes formlen på celle B2:B6 ved hjælp af udfyldningshåndtaget.
Ovenstående formel tilføjer et fast antal nuller før tallet, men den samlede længde af tallet varierer afhængigt af tallet.
Hvis du vil tilføje foranstillede nuller, hvor det er nødvendigt for at skabe en specifik tegn lange (fast længde) strenge, kan du bruge REPT og LEN funktionerne sammen.
Syntaks:
=REPT(tekst; tal_gange-LÆN(tekst))&celleFor at tilføje nuller med præfiks til værdien i A2 og lave en 5-tegn lang streng, prøv denne formel:
=REPT(0,5-LEN(A2))&A2Her får 'LEN(A2)' den samlede længde af strengen/tallene i celle A2. '5' er den maksimale længde af streng/tal, cellen skal have. Og 'REPT(0,5-LEN(A2))'-delen tilføjer antallet af nuller ved at trække længden af strengen i A2 fra det maksimale antal nuller (5). Derefter sammenføjes et antal 0'er før værdien af A2 for at lave en streng med fast længde.
Brug af RIGHT funktion
En anden måde at udfylde indledende nuller før en streng i Excel er at bruge funktionen HØJRE.
Funktionen HØJRE kan tilføje et antal nuller til starten af et tal og udtrække de N tegn længst til højre fra værdien.
Syntaks:
= HØJRE (tekst, antal_tegn)- tekst er den celle eller værdi, du vil udtrække tegn fra.
- antal_tegn er antallet af tegn, der skal udtrækkes fra teksten. Hvis dette argument ikke er givet, vil kun det første tegn blive udtrukket.
For denne metode sammenkæder vi det maksimale antal nuller med cellereference, der indeholder strengen i 'tekst'-argumentet.
For at oprette et 6-cifret tal baseret på talstrengen i A med foranstillede nuller, prøv denne formel:
=HØJRE("0000000"&A2,6)Det første argument (tekst) i formlen tilføjer 7 nuller til værdien i A2 ("0000000"&A2), og returnerer derefter de 7 tegn længst til højre, hvilket resulterer i nogle indledende nuller.
Tilføjelse af førende nuller ved hjælp af brugerdefineret talformatering
Hvis du bruger en af ovenstående metoder til at sætte indledende nuller før tallene, vil du altid få en tekststreng, ikke et tal. Og de vil ikke være meget nyttige i beregninger eller i numeriske formler.
Den bedste måde at tilføje foranstillede nuller i Excel er at anvende en brugerdefineret talformatering. Hvis du tilføjer indledende nuller ved at tilføje et brugerdefineret talformat til cellen, ændrer det ikke værdien af cellen, men kun måden, den vises på. Værdien forbliver stadig som et tal, ikke som tekst.
Følg disse trin for at ændre talformateringen af celler:
Vælg den celle eller celleområdet, hvor du vil vise indledende nuller. Højreklik derefter et vilkårligt sted inden for det valgte område, og vælg indstillingen 'Formater celler' fra kontekstmenuen. Eller tryk på genvejstasterne Ctrl + 1.
I vinduet Formater celler skal du gå til fanen 'Nummer' og vælge 'Brugerdefineret' under kategoriindstillingerne.
Indtast antallet af nuller i boksen 'Type:' for at angive det samlede antal cifre, du vil vise i en celle. Hvis du f.eks. ønsker, at nummeret skal være på 6 cifre, skal du indtaste '000000' som den brugerdefinerede formatkode. Klik derefter på 'OK' for at ansøge.
Dette vil vise indledende nuller før tallene, og hvis tallet er mindre end 6 cifre, udfylder det nul før det.
Tal vil kun se ud til at have foranstillede nuller, mens den underliggende værdi forbliver uændret. Hvis du vælger en celle med brugerdefineret formatering, vil den vise dig det oprindelige nummer i formellinjen
Der er mange digitale pladsholdere, du kan bruge i dit brugerdefinerede talformat. Men der er kun to primære pladsholdere, du kan bruge til at tilføje foranstillede nuller i tal.
- 0 – Det er cifferpladsholderen, der viser ekstra nuller. Den viser tvungne cifre 0-9, uanset om tallet er relevant for værdien. For eksempel, hvis du skriver 2.5 med formatkoden 000.00, vil den vise 002.50.
- # – Det er cifferpladsholderen, der viser valgfrie cifre og ikke inkluderer ekstra nuller. For eksempel, hvis du skriver 123 med formateringskoden 000#, vil den vise 0123.
Ethvert tegnsætningstegn eller andet tegn, du inkluderer i formatkoden, vil også blive vist, som det er. Du kan bruge tegn som bindestreg (-), komma (,), skråstreg (/) osv.
For eksempel kan du også formatere numre som telefonnumre ved at bruge brugerdefineret format.
Dialogboksen Formatér kode i Formater celler:
Resultatet:
Lad os anvende denne formateringskode i følgende eksempel:
##0000
Som du kan se vil '0' tilføje ekstra nuller, mens '#' ikke tilføjer ubetydelige nuller:
Du kan også bruge foruddefinerede formatkoder i afsnittet 'Specialformater' i dialogboksen Formater celler til postnumre, telefonnumre og personnumre.
Følgende tabel viser tal med foranstillede nuller, hvor forskellige 'Special' formatkoder anvendes på forskellige kolonner:
Fjernelse af førende nuller i Excel
Nu har du lært, hvordan du tilføjer indledende nuller i Excel, lad os se, hvordan du fjerner de indledende nuller fra antallet af strenge. Nogle gange, når du importerer data fra en ekstern kilde, kan tal ende med at have præfiks nuller og blive formateret som tekst. I sådanne tilfælde skal du fjerne de foranstillede nuller og konvertere dem tilbage til tal, så du kan bruge dem i formler.
Der er forskellige måder, du kan fjerne foranstillede nuller på i Excel, og vi vil se dem én efter én.
Fjern indledende nuller ved at ændre celleformateringen
Hvis indledende nuller blev tilføjet ved brugerdefineret talformatering, kan du nemt fjerne dem ved at ændre formatet på cellerne. Du kan se, om dine celler er tilpasset formateret ved at se på adresselinjen (nuller vil være synlige i cellen ikke i adresselinjen).
For at fjerne de foranstillede nuller skal du markere cellerne med de foranstillede nuller, klikke på boksen 'Nummerformat' og vælge 'Generelt' eller 'Nummer' formateringsindstilling.
Nu er de foranstillede nuller væk:
Slet foranstillede nuller ved at konvertere teksten til tal
Hvis dine foranstillede nuller blev tilføjet ved at ændre celleformatet eller ved at tilføje apostrof før tallene eller automatisk tilføjet ved import af data, er den nemmeste måde at konvertere dem til tal ved at bruge indstillingen Fejlkontrol. Sådan gør du:
Du kan bruge denne metode, hvis dine tal er venstrejusterede, og dine celler har en lille grøn trekant (en fejlindikator) i øverste venstre hjørne af cellerne. Det betyder, at tallene er formateret som tekst.
Vælg disse celler, og du vil se en gul advarsel øverst til højre i markeringen. Klik derefter på indstillingen 'Konverter til nummer' fra rullemenuen.
Dine nuller vil blive fjernet, og tallene vil blive konverteret tilbage til talformatet (højrejusteret).
Fjernelse af førende nuller ved Multiplicer/Divider med 1
En anden nem og bedste måde at fjerne indledende er ved at gange eller dividere tallene med 1. At dividere eller gange værdien ændrer ikke værdien, det konverterer blot værdien tilbage til et tal og fjerner de foranstillede nuller.
For at gøre dette skal du skrive formlen i eksemplet nedenfor i en celle og trykke på ENTER. De foranstillede nuller vil blive fjernet, og strengen vil blive konverteret tilbage til et tal.
Anvend derefter denne formel på andre celler ved hjælp af udfyldningshåndtaget.
Du kan opnå de samme resultater ved at bruge kommandoen 'Indsæt speciel'. Sådan gør du:
Skriv '1' numerisk værdi i en celle (lad os sige i B2), og kopier denne værdi.
Vælg derefter de celler, hvor du vil fjerne de foranstillede nuller. Højreklik derefter på valget og vælg derefter indstillingen 'Indsæt speciel'.
I dialogboksen Indsæt speciel, under Operation, skal du vælge enten 'Multiply' eller 'Divide' og klik på 'OK'.
Det er det, dine foranstillede nuller vil blive fjernet og efterlader strengene som tal.
Fjern førende nuller ved at bruge en formler
En anden nem måde at slette foranstillede nuller på er ved at bruge VALUE-funktionen. Denne metode kan være nyttig, uanset om dine foranstillede nuller blev tilføjet ved at bruge en anden formel eller apostrof eller ved brugerdefineret formatering.
=VÆRDI(A1)Formlens argument kunne være en værdi eller den cellereference, der har værdien. Formlen fjerner de foranstillede nuller og konverterer værdien fra tekst til tal. Anvend derefter formlen på resten af cellerne.
Nogle gange vil du måske fjerne de foranstillede nuller, men ønsker at beholde tallene i tekstformatet. I sådanne tilfælde skal du bruge funktionerne TEXT() og VALUE () sammen på denne måde:
=TEKST(VÆRDI(A1);"#")Funktionen VÆRDI konverterer værdien i A1 til tal. Men det andet argument, '#' konverterer værdien tilbage til tekstformat uden ekstra nuller. Som et resultat vil du få tallene uden foranstillede nuller, men stadig i tekstformatet (venstrejusteret).
Fjern førende nuller ved hjælp af Excels tekst til kolonner-funktion
Endnu en måde at fjerne indledende nuller på er at bruge Excels tekst til kolonner-funktion.
Vælg det celleområde, der har tallene med foranstillede nuller.
Gå derefter til fanen 'Data' og klik på knappen 'Tekst til kolonner' i gruppen Dataværktøjer.
Guiden 'Konverter tekst til kolonner' vises. I trin 1 af 3 skal du vælge 'Afgrænset' og klikke på 'Næste'.
I trin 2 af 3 skal du fjerne markeringen af alle afgrænsningstegnene og klikke på 'Næste'.
På det sidste trin skal du lade indstillingen Kolonnedataformat være 'Generelt' og vælge den destination (den første celle i området), hvor du vil have dine tal uden foranstillede nuller. Klik derefter på 'Udfør'
Og du vil få numrene med indledende fjernet i en separat kolonne som vist nedenfor.
Det er alt.