Slik legger du til ledende nuller i Excel - alt du trenger å vite

Det er situasjoner når du må legge til ledende nuller til et datasett i Excel. Dette kan være tilfelle hvis du opprettholder poster i Excel, for eksempel medarbeider -ID -er eller transaksjons -ID -er.

For eksempel kan det være lurt å få et konsistent utseende i datasettet som vist nedenfor:

I denne opplæringen lærer du forskjellige måter å legge til ledende nuller i Excel:

• Konvertering av format til tekst
• Bruke egendefinert nummerformatering
• Bruker tekstfunksjon
• Bruke REPT/LEN -funksjoner
• Bruker VBA

Hver av disse metodene har noen fordeler og ulemper (dekket i hver seksjon).

La oss se hvordan hver av disse fungerer.

Legg til ledende nuller ved å konvertere formatet til tekst

Når skal du bruke: Når du har et lite numerisk datasett, og du planlegger å gjøre denne redigeringen manuelt.

Anta at du har ansatt -ID -er for markedsføringsavdelingen som vist nedenfor, og du vil få disse ID -ene til å se konsistente ut ved å legge til ledende nuller.

Så du prøver å endre ID -en ved å skrive inn ledende nuller (00001 i stedet for 1).

Men til din overraskelse konverterer Excel den tilbake til 1.

Dette skjer ettersom Excel forstår at 00001 og 1 er de samme tallene og bør følge de samme visningsreglene.

Så frustrerende som det kan være for deg, Excel har sine grunner.

Så for å få jobben gjort uten å bøye Excel -regler, må du dra fordel av at denne regelen ikke gjelder tekstformatering.

Så her er hva du trenger å gjøre:

1. Velg cellene der du vil legge til ledende nuller manuelt.
2. Gå til Hjem → Nummergruppe og velg Tekst fra rullegardinmenyen.

Det er det!

Nå, når du skriver inn ledende nuller manuelt, ville Excel lett overholde.

Forsiktig: Når du konverterer formatet til tekst, vil noen Excel -funksjoner ikke fungere som de skal. For eksempel vil SUM/COUNT -funksjonen ignorere cellen siden den er i tekstformatet.

Legg til ledende nuller ved å bruke egendefinert tallformatering

Når skal den brukes: Når du har et numerisk datasett, og du vil at resultatet skal være numerisk (ikke tekst).

Når du viser et tall i et bestemt format, endrer det ikke den underliggende verdien av tallet. For eksempel kan jeg vise tallet 1000 som 1000 eller 1000 eller 1000,00 eller 001000 eller 26-09-1902 (jevne datoer er tall i backend i Excel).

På alle de forskjellige måtene å vise nummeret, endres aldri verdien av tallet. Det er bare måten det vises på som er endret.

For å legge til ledende nuller, kan vi formatere det for å vise det slik, mens den underliggende verdien vil forbli uendret.

Her er trinnene for å bruke denne teknikken til å legge til ledende nuller i Excel:

1. Velg cellene der du vil legge til ledende nuller.
2. Gå til Hjem → Nummergruppe og klikk på dialogboksen (en liten vippet pil nederst til høyre). Dette åpner dialogboksen Formater celler. Alternativt kan du også bruke hurtigtasten: Control + 1.
3. Velg Custom i kategorilisten i dialogboksen Format Cells i kategorien Number.
4. I feltet Type skriver du inn 00000
5. Klikk OK.

Hvis du gjør dette, vises alltid alle tallene som fem sifre, der ledende 0’er legges til automatisk hvis tallet er mindre enn 5 sifre. Så 10 ville bli 00010 og 100 ville blitt 00100.

I dette tilfellet har vi brukt seks nuller, men hvis dataene dine har tall med flere sifre, må du bruke formatet deretter.

Merk: Denne teknikken fungerer bare for et numerisk datasett. Hvis du har medarbeider -ID -er som A1, A2, A3 og så videre, er disse tekst og vil ikke endres når du bruker det tilpassede formatet som vist ovenfor.

Legg til ledende nuller ved å bruke TEKST -funksjon

Når skal du bruke: Når du vil at resultatet skal være tekst.

TEKST -funksjonen lar deg endre verdien til ønsket format.

For eksempel, hvis du vil at 1 skal vises som 001, kan du bruke TEKST -funksjonen til det.

Husk imidlertid at TEKST -funksjonen vil endre formatet og gjøre det TEKST. Dette betyr at når du lager 1 som 001, behandler Excel det nye resultatet som tekst med tre tegn (akkurat som abc eller xyz).

Slik legger du til ledende nuller ved hjelp av TEKST -funksjonen:

1. Hvis du har tallene i kolonne A (si fra A2: A100), velg deretter B2: B100 og skriv inn følgende formel:
   = TEKST (A2, ”00000 ″)
2. Trykk på Control + Enter for å bruke formelen på alle de valgte cellene.

Dette vil vise alle tallene som fem sifre, der ledende 0’er legges til automatisk hvis tallet er mindre enn 5 sifre.

En fordel med å konvertere data til tekst er at du nå kan bruke dem i oppslagsformler som VLOOKUP eller INDEX/MATCH for å hente detaljene til en ansatt ved å bruke hans/hennes ansatt -ID.

Merk: Denne teknikken fungerer bare for et numerisk datasett. Hvis du har medarbeider -ID -er som A1, A2, A3 og så videre, er dette tekst og vil ikke endres når du bruker det tilpassede formatet som vist ovenfor.

Legg til ledende nuller ved å bruke REPT- og LEN -funksjoner

Når skal du bruke: Når du har et datasett som er numerisk/alfanumerisk, og du vil at resultatet skal være tekst.

Ulempen med å bruke TEKST -funksjonen var at den bare ville fungere med numeriske data. Men hvis du har et alfanumerisk datasett (si A1, A2, A3, og så videre), ville TEKST -funksjonen mislykkes.

I slike tilfeller gjør en kombinasjon av REPT- og LEN -funksjonen utslaget.

Slik gjør du det:

1. Hvis du har tallene i kolonne A (si fra A2: A100), velg deretter B2: B100 og skriv inn følgende formel:
   = REPT (0,5-LEN (A2)) og A2
2. Trykk på Control + Enter for å bruke formelen på alle de valgte cellene.

Dette vil gjøre alle tallene/strengene 5 tegn lange med ledende nuller hvor det er nødvendig.

Slik fungerer denne formelen:

• LEN (A2) gir lengden på strengen/tallene i cellen.
• = REPT (0,5-LEN (A2)) vil gi tallet 0 som skal legges til. Her har jeg brukt 5 i formelen da det var maksimal lengde på streng/tall i datasettet mitt. Du kan endre dette i henhold til dataene dine.
• = REPT (0,5-LEN (A2)) og A2 vil ganske enkelt legge til antall nuller til verdien av cellen. For eksempel, hvis verdien i cellen er 123, vil dette returnere 00123.

Legg til ledende nuller ved å bruke egendefinert funksjon (VBA)

Hvis du legger til ledende nuller i Excel er noe du må gjøre ganske ofte, er det en god idé å bruke en egendefinert funksjon.

Her er VBA -koden som vil lage en enkel funksjon for å legge til ledende nuller:

'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 Deretter Resultat = Resultat & Midt (ref, i, 1) Annet Resultat = "0" & ​​Resultat Slutt Hvis Neste i AddLeadingZeroes = Resultat Sluttfunksjon

Bare legg til denne koden i modulkodevinduet, så kan du bruke den akkurat som alle andre regnearkfunksjoner.

Eller opprett et tillegg for det og kan dele det med dine kolleger.