Hvordan legge til ledende nuller i Excel

Denne opplæringen dekker de forskjellige metodene for å legge til eller beholde innledende nuller samt fjerne innledende nuller i Excel.

Hver gang du skriver inn eller importerer tall med en eller flere innledende nuller, som 000652, fjerner Excel automatisk disse nullene, og bare selve tallet vises i cellene (652). Dette er fordi de innledende nullene ikke er nødvendige for beregninger og ikke teller.

Det er imidlertid tider når de innledende nullene er nødvendige, for eksempel når du skriver inn ID-numre, telefonnumre, kredittkortnumre, produktkoder eller postnumre osv. Heldigvis gir Excel oss flere måter å legge til eller beholde innledende nuller på. i celler. I denne artikkelen vil vi vise deg de forskjellige måtene å legge til eller beholde innledende nuller og fjerne innledende nuller.

Legge til ledende nuller i Excel

I hovedsak er det to metoder du kan bruke for å legge til innledende nuller: en, formater nummeret ditt som "Tekst"; to, bruk tilpasset formatering for å legge til innledende nuller. Metoden du vil bruke kan avhenge av hva du vil gjøre med nummeret.

Det kan være lurt å legge til innledende null når du skriver inn unike ID-numre, kontonumre, personnummer eller postnummer osv. Men du skal ikke bruke disse tallene for beregninger eller i funksjoner, så det er best å konvertere disse tallene. tall til tekst. Du vil aldri summere eller gjennomsnittlige telefonnumre eller kontonumre.

Det er flere måter du kan legge til eller legge inn nuller før tallene ved å formatere dem som tekst:

  • Endre celleformatet til tekst
  • Legge til en apostrof (')
  • Bruke TEXT-funksjonen
  • Bruke REPT/LEN-funksjonen
  • Bruk CONCATENATE-funksjonen/Ampersand-operatoren (&)
  • Bruker HØYRE-funksjonen

Endre celleformat til tekst

Dette er en av de enkleste måtene å legge til innledende nuller til tallene dine. Hvis du bare skal skrive inn tall og du vil beholde innledende nuller mens du skriver, så er dette metoden for deg. Ved å endre celleformat fra Generelt eller Tall til Tekst, kan du tvinge Excel til å behandle tallene dine som tekstverdier, og alt du skriver i cellen vil forbli nøyaktig det samme. Slik gjør du det:

Velg cellen(e) der du vil legge til innledende nuller. Gå til 'Hjem'-fanen, klikk på 'Format'-rullegardinboksen i Numbers-gruppen, og velg 'Tekst' fra formatalternativene.

Nå når du skriver inn tallene dine, vil ikke Excel slette noen innledende null fra den.

Du kan se en liten grønn trekant (feilindikator) i øverste venstre hjørne av cellen, og når du velger den cellen, vil den vise deg et advarselsskilt som indikerer at du har lagret nummeret som tekst.

For å fjerne feilmeldingen, velg cellen(e), klikk på advarselstegnet og velg "Ignorer feil" fra listen.

Du kan også skrive inn telefonnumre med mellomrom eller bindestrek mellom nummer, Excel vil automatisk behandle disse tallene som tekst.

Bruker Leading Apostrof ( ' )

En annen måte å legge til innledende nuller i Excel er å legge til en apostrof (') i begynnelsen av tallet. Dette vil tvinge Excel til å skrive inn et tall som tekst.

Bare skriv inn en apostrof før noen tall og trykk "Enter". Excel vil la de innledende nullene være intakte, men (‘) vil ikke være synlig i regnearket med mindre du velger cellen.

Bruke tekstfunksjonen

Metoden ovenfor legger til nuller til tall etter hvert som du skriver dem, men hvis du allerede har en liste med tall og du vil legge inn innledende nuller foran dem, er TEXT-funksjonen den rette metoden for deg. TEXT-funksjonen lar deg konvertere tall til tekststrenger mens du bruker tilpasset formatering.

Syntaks for TEKST-funksjonen:

= TEXT( verdi, format_tekst)

Hvor,

  • verdi – Det er den numeriske verdien du trenger for å konvertere til tekst og bruke formatering.
  • format_tekst – er formatet du vil bruke.

Med TEXT-funksjonen kan du angi hvor mange sifre nummeret ditt skal ha. Hvis du for eksempel vil at tallene dine skal være 8 sifre, skriver du 8 nuller i funksjonens andre argument: "00000000". Hvis du har 6-sifrede tall i en celle, vil funksjonen manuelt legge til 2 innledende nuller, og hvis du har 2-sifrede tall som 56, vil resten være nuller (00000056).

For eksempel, for å legge til innledende nuller og gjøre tallene 6-sifrede, bruk denne formelen:

=TEKST(A2,"000000")

Siden vi har 6 nuller i formelens andre argument, konverterer funksjonen tallstrengen til en tekststreng og legger til 5 innledende nuller for å gjøre strengen 6 sifre lang.

Merk: Husk å sette formatkodene i doble anførselstegn i funksjonen.

Nå kan du bruke den samme formelen på resten av cellene ved å dra i fyllhåndtaket. Som du kan se, konverterer funksjonen tallene til tekster og legger til innledende nuller til tallene slik at det totale antallet sifre er 6.

TEXT-funksjonen vil alltid returnere verdi som en tekststreng, ikke et tall, så du vil ikke kunne bruke dem i aritmetiske beregninger, men du kan fortsatt bruke dem i oppslagsformler som VLOOKUP eller INDEX/MATCH for å hente detaljene til en produkt ved hjelp av produkt-ID-er.

Bruke CONCATENATE-funksjonen/Ampersand-operatøren (&)

Hvis du vil legge til et fast antall innledende nuller før alle tallene i en kolonne, kan du bruke CONCATENATE-funksjonen eller og-tegnet (&).

Syntaks for CONCATENATE-funksjonen:

=CONCATENATE(tekst1; [tekst2], ...)

Hvor,

tekst1 – Antall nuller som skal settes inn før tallet.

tekst2 – Det opprinnelige nummeret eller cellereferansen

Syntaks for ampersand-operator:

=Verdi_1 og Verdi_2

Hvor,

Verdi_1 er de innledende nullene som skal settes inn før tallet og Verdi_2 er tallet.

For eksempel, for å legge til bare to nuller før et tall, bruk en av disse formelene:

=CONCATENATE("00",A2)

Det første argumentet er to nuller ("00") fordi vi ønsker å legge to nuller foran tallet i A2 (som er det andre argumentet).

Eller, 

="00"&A2

Her er det første argumentet 2 nuller, etterfulgt av '&'-operatoren, og det andre argumentet er tallet.

Som du kan se legger formelen bare to innledende nuller til alle tallene i en kolonne uavhengig av hvor mange sifre tallet inneholder.

Begge disse formlene slår sammen et visst antall nuller før de opprinnelige tallene og lagrer dem som tekststrenger.

Bruke REPT/LEN-funksjonen

Hvis du vil legge til innledende nuller til numeriske eller alfanumeriske data og konvertere strengen til tekst, bruk REPT-funksjonen. REPT-funksjonen brukes til å gjenta et eller flere tegn et bestemt antall ganger. Denne funksjonen kan også brukes til å sette inn faste tall med innledende nuller før tallet.

=REPT(tekst; antall_ganger)

Der 'tekst' er tegnet vi ønsker å gjenta (i vårt tilfelle '0') og 'antall_ ganger'-argumentet er antall ganger vi ønsker å gjenta det tegnet.

For eksempel, for å generere fem nuller før tall, vil formelen se slik ut:

=REPT(0,5)&A2

Det formelen gjør er å gjenta 5 nuller og slå sammen tallstrengen i A2 og returnere resultatet. Deretter brukes formelen på celle B2:B6 ved hjelp av fyllhåndtaket.

Formelen ovenfor legger til et fast antall nuller før tallet, men den totale lengden på tallet varierer avhengig av tallet.

Hvis du vil legge til innledende nuller der det er nødvendig for å lage strenger med et bestemt tegn (fast lengde), kan du bruke REPT- og LEN-funksjonene sammen.

Syntaks:

=REPT(tekst; tall_ganger-LEN(tekst))&celle

For eksempel, for å legge til prefiks nuller til verdien i A2 og lage en 5-tegn lang streng, prøv denne formelen:

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

Her får 'LEN(A2)' den totale lengden på strengen/tallene i celle A2. '5' er den maksimale lengden på strengen/numrene cellen skal ha. Og 'REPT(0,5-LEN(A2))'-delen legger til antallet nuller ved å trekke lengden på strengen i A2 fra det maksimale antallet nuller (5). Deretter slås et antall 0-er sammen før verdien av A2 for å lage en streng med fast lengde.

Bruker RIGHT-funksjonen

En annen måte å legge inn foran nuller før en streng i Excel er å bruke HØYRE-funksjonen.

HØYRE-funksjonen kan legge til et antall nuller til starten av et tall og trekke ut de N-tegnene lengst til høyre fra verdien.

Syntaks:

= HØYRE (tekst, antall_tegn)
  • tekst er cellen eller verdien du vil trekke ut tegn fra.
  • antall_tegn er antall tegn som skal trekkes ut fra teksten. Hvis dette argumentet ikke er gitt, vil bare det første tegnet trekkes ut.

For denne metoden setter vi sammen det maksimale antallet nuller med cellereferanse som inneholder strengen i "tekst"-argumentet.

For å lage et 6-sifret tall basert på tallstrengen i A med innledende nuller, prøv denne formelen:

=HØYRE("0000000"&A2,6)

Det første argumentet (teksten) i formelen legger til 7 nuller til verdien i A2 ("0000000"&A2), og returnerer deretter de 7 tegnene lengst til høyre, noe som resulterer i noen innledende nuller.

Legge til ledende nuller ved hjelp av tilpasset tallformatering

Hvis du bruker noen av metodene ovenfor for å sette innledende nuller foran tallene, vil du alltid få en tekststreng, ikke et tall. Og de vil ikke være mye bruk i beregninger eller i numeriske formler.

Den beste måten å legge til innledende nuller i Excel er å bruke en tilpasset tallformatering. Hvis du legger til innledende nuller ved å legge til et tilpasset tallformat i cellen, endrer det ikke verdien til cellen, men bare måten den vises på. Verdien forblir fortsatt som et tall, ikke tekst.

Følg disse trinnene for å endre tallformateringen til celler:

Velg cellen eller celleområdet der du vil vise innledende nuller. Høyreklikk deretter hvor som helst innenfor det valgte området og velg alternativet "Formater celler" fra hurtigmenyen. Eller trykk på hurtigtastene Ctrl + 1.

I vinduet Formater celler går du til fanen "Nummer" og velger "Egendefinert" under kategorialternativene.

Skriv inn antall nuller i 'Type:'-boksen for å spesifisere det totale antallet sifre du vil vise i en celle. Hvis du for eksempel vil at nummeret skal være 6 sifre, skriver du inn "000000" som den egendefinerte formatkoden. Klikk deretter "OK" for å søke.

Dette vil vise innledende nuller før tallene, og hvis tallet er mindre enn 6 sifre, legger det null foran det.

Tall vil bare se ut til å ha innledende nuller mens den underliggende verdien forblir uendret. Hvis du velger en celle med tilpasset formatering, vil den vise deg det opprinnelige nummeret i formellinjen

Det er mange digitale plassholdere du kan bruke i ditt egendefinerte tallformat. Men det er bare to primære plassholdere du kan bruke for å legge til innledende nuller i tall.

  • 0 – Det er sifferplassholderen som viser ekstra nuller. Den viser tvungne sifre 0-9 om sifferet er relevant for verdien eller ikke. For eksempel, hvis du skriver 2.5 med formatkoden 000.00, vil den vise 002.50.
  • # – Det er sifferplassholderen som viser valgfrie sifre og inkluderer ikke ekstra nuller. For eksempel, hvis du skriver 123 med formateringskoden 000#, vil den vise 0123.

Dessuten vil ethvert skilletegn eller annet tegn du inkluderer i formatkoden vises som det er. Du kan bruke tegn som bindestrek (-), komma (,), skråstrek (/) osv.

Du kan for eksempel også formatere numre som telefonnumre ved å bruke tilpasset format.

Dialogboksen Formater kode i Formater celler:

Resultatet:

La oss bruke denne formateringskoden i følgende eksempel:

##0000

Som du kan se vil '0' legge til ekstra nuller mens '#' ikke legger til ubetydelige nuller:

Du kan også bruke forhåndsdefinerte formatkoder i delen "Spesielle formater" i dialogboksen Formater celler for postnumre, telefonnumre og personnummer.

Følgende tabell viser tall med innledende nuller der forskjellige "Spesielle" formatkoder brukes på forskjellige kolonner:

Fjerning av ledende nuller i Excel

Nå har du lært hvordan du legger til innledende nuller i Excel, la oss se hvordan du fjerner innledende nuller fra antall strenger. Noen ganger, når du importerer data fra en ekstern kilde, kan tall ende opp med prefiksnull og bli formatert som tekst. I slike tilfeller må du fjerne de innledende nullene og konvertere dem tilbake til tall, slik at du kan bruke dem i formler.

Det er forskjellige måter du kan fjerne innledende nuller i Excel, og vi ser dem én etter én.

Fjern innledende nuller ved å endre celleformateringen

Hvis innledende nuller ble lagt til ved tilpasset tallformatering, kan du enkelt fjerne dem ved å endre formatet på cellene. Du kan se om cellene dine er tilpasset formatert ved å se på adressefeltet (nuller vil være synlige i cellen ikke i adressefeltet).

For å fjerne de prefikserte nullene, velg cellene med de innledende nullene, klikk på "Tallformat"-boksen og velg "Generelt" eller "Tall" formateringsalternativ.

Nå er de innledende nullene borte:

Slett innledende nuller ved å konvertere teksten til tall

Hvis de innledende nullene ble lagt til ved å endre celleformatet eller ved å legge til apostrof før tallene eller automatisk lagt til ved import av data, er den enkleste måten å konvertere dem til tall ved å bruke alternativet for feilkontroll. Slik gjør du det:

Du kan bruke denne metoden hvis tallene dine er venstrejusterte og cellene dine har en liten grønn trekant (en feilindikator) i øverste venstre hjørne av cellene. Dette betyr at tallene er formatert som tekst.

Velg disse cellene, og du vil se en gul advarsel øverst til høyre i utvalget. Klikk deretter på "Konverter til nummer"-alternativet fra rullegardinmenyen.

Dine nuller vil bli fjernet og tallene vil bli konvertert tilbake til tallformatet (høyrejustert).

Fjerning av ledende nuller med Multipliser/Divider med 1

En annen enkel og beste måte å fjerne innledende er ved å multiplisere eller dele tallene med 1. Å dele eller multiplisere verdien endrer ikke verdien, den konverterer bare verdien tilbake til et tall og fjerner de innledende nullene.

For å gjøre dette, skriv inn formelen i eksemplet nedenfor i en celle og trykk ENTER. De innledende nullene vil bli fjernet og strengen vil bli konvertert tilbake til et tall.

Bruk deretter denne formelen på andre celler ved å bruke fyllhåndtaket.

Du kan oppnå de samme resultatene ved å bruke 'Lim inn spesial'-kommandoen. Dette er hvordan:

Skriv inn '1' numerisk verdi i en celle (la oss si i B2) og kopier den verdien.

Deretter velger du cellene der du vil fjerne de innledende nullene. Høyreklikk deretter på utvalget og velg alternativet "Lim inn spesial".

I dialogboksen Lim inn spesial, under Operasjon, velg enten "Multiply" eller "Divide" og klikk "OK".

Det er det, dine innledende nuller vil bli fjernet og etterlater strengene som tall.

Fjern ledende nuller ved å bruke en formler

En annen enkel måte å slette foranstilte nuller på er ved å bruke VERDI-funksjonen. Denne metoden kan være nyttig uansett om innledende nuller ble lagt til ved å bruke en annen formel eller apostrof eller ved egendefinert formatering.

=VERDI(A1)

Argumentet til formelen kan være en verdi eller cellereferansen som har verdien. Formelen fjerner de innledende nullene og konverterer verdien fra tekst til tall. Bruk deretter formelen på resten av cellene.

Noen ganger vil du kanskje fjerne de innledende nullene, men ønsker å beholde tallene i tekstformatet. I slike tilfeller må du bruke funksjonene TEXT() og VALUE () sammen slik:

=TEKST(VERDI(A1);"#")

VERDI-funksjonen konverterer verdien i A1 til tall. Men det andre argumentet, '#' konverterer verdien tilbake til tekstformat uten ekstra nuller. Som et resultat vil du få tallene uten noen innledende nuller, men fortsatt i tekstformatet (venstrejustert).

Fjern ledende nuller ved å bruke Excels tekst til kolonner-funksjon

Enda en måte å fjerne innledende nuller på er å bruke Excels Tekst til kolonner-funksjon.

Velg celleområdet som har tallene med innledende nuller.

Deretter går du til 'Data'-fanen og klikker på 'Tekst til kolonner'-knappen i Dataverktøy-gruppen.

'Konverter tekst til kolonner'-veiviseren vises. I trinn 1 av 3, velg 'Avgrenset' og klikk på 'Neste'.

I trinn 2 av 3, fjern merket for alle skilletegnene og klikk "Neste".

På det siste trinnet, la alternativet Kolonnedataformat være "Generelt" og velg destinasjonen (den første cellen i området) der du vil ha tallene dine uten innledende nuller. Deretter klikker du på "Fullfør"

Og du vil få tallene med innledende fjernet i en egen kolonne som vist nedenfor.

Det er alt.