Det er situasjoner når du trenger å legge til ledende nuller til et datasett i Excel. Dette kan være tilfelle hvis du opprettholde poster i Excel, slik som Ansatt-Id-er eller Transaksjons-Id.,
For eksempel, ønsker du kanskje å få et konsekvent utseende i datasettet som er vist nedenfor:
I denne opplæringen vil du lære ulike måter å legge til ledende nuller i Excel:
- Konvertere Formatet til Tekst
- ved Hjelp av Egendefinerte tallformatering
- ved Hjelp av Tekst-Funksjonen
- ved Hjelp av REPT/LEN funksjoner
- ved Hjelp av VBA
Hver av disse metodene har noen fordeler og ulemper (dekket i hvert avsnitt).
La oss se på hvordan hver enkelt av disse verkene.,
Denne Opplæringen Dekker:
Legg til Ledende Nuller ved å Konvertere Formatet til Tekst
Når du skal Bruke: Når du har en liten numeriske data, og du planlegger å gjøre dette manuelt redigere.
Tenk deg at du har ansatt-Id-er i markedsavdelingen, som vist nedenfor, og du ønsker å gjøre disse Idene ser samsvar ved å legge til ledende nuller.
Så du prøve å endre id ved å skrive inn ledende nuller (00001 i stedet for 1).
Men til din forundring, Excel konverterer det tilbake til 1.,
Dette skjer som Excel forstår at 00001 og 1 er de samme tallene og bør følge den samme skjermen regler.
Nå så frustrerende som det kan være noe for deg, Excel har sine grunner.
Så, for å få arbeidet gjort uten å bøye Excel-regler, vil du har til å dra nytte av det faktum at denne regelen ikke gjelder for tekstformatering.
Så her er hva du trenger å gjøre:
- Velg cellene som du ønsker å manuelt legge til ledende nuller.
- Gå til Hjem → Antall Gruppe og velger Tekst fra drop-down.,
det er det!
Nå, når du går inn i ledende nuller manuelt, Excel ville lett å overholde.
Advarsel: Når du konvertere formatet til Tekst, Excel-funksjoner vil ikke fungere på riktig måte. For eksempel, SUM/TELLER funksjonen ville ignorere cellen siden det er i tekst-format.
Legg til Ledende Nuller) ved Hjelp av Tilpassede tallformatering
Når du skal Bruke: Når du har en numerisk dataset, og du vil at resultatet skal være numerisk (ikke tekst).
Når du viser et tall i et spesifikt format, vil det ikke endre den underliggende verdien av nummeret., For eksempel kan jeg vise nummer 1000 som 1000 eller 1000, – eller 1000.00 eller 001000 eller 26-09-1902 (selv datoer er tall i backend i Excel).
I alle de forskjellige måter å vise nummeret, verdien av antallet endrer seg aldri. Det er bare slik det er vist, som er endret.
for Å legge til ledende nuller, vi kan formatere den for å vise det på den måten, mens den underliggende verdien ville forbli uendret.
Her er fremgangsmåten for å bruke denne teknikken til å legge til ledende nuller i Excel:
- Velg cellene der du vil legge til ledende nuller.,
- Gå til Hjem → Antall Gruppen, og klikk på i dialogboksen launcher (en liten vippet pilen i det nedre høyre). Dette vil åpne dialogboksen Formater Celler. Alternativt, du kan også bruke hurtigtasten: Ctrl + 1.
- I dialogboksen Formater Celler, innen kategorien Tall, velg Egendefinert i Kategori-listen.
- I Type-feltet, skriv inn 00000
- Klikk på OK.,
Gjør dette vil alltid vise alle tallene som fem sifre, der ledende 0-er er lagt til automatisk hvis tallet er mindre enn 5 sifre. Så 10 ville bli 00010 og 100 vil bli 00100.
I dette tilfellet har vi brukt seks nuller, men hvis dataene har tall med flere sifre, må du bruke format i henhold til dette.
Merk: Denne teknikken ville arbeide bare for en numerisk dataset. I tilfelle du har ansatt-id-er som A1, A2, A3 og så videre, så dette er tekst og ikke ville endre seg når du bruker det egendefinerte formatet som vist ovenfor.,
Legg til Ledende Nuller ved Hjelp av TEKST-Funksjonen
Når du skal Bruke: Når du vil at resultatet skal være tekst.
TEKST-funksjonen kan du endre verdien til ønsket format.
For eksempel, hvis du ønsker 1 vises som 001, kan du bruke TEKST-funksjonen for den.
Imidlertid, husk at TEKST-funksjonen vil endre formatet og gjøre det TEKST. Dette betyr at når du gjør 1 som 001, Excel behandler nye resultat som tekst med tre tegn (akkurat som abc eller xyz).,
Her er hvordan du kan legge til ledende nuller ved hjelp av TEKST-funksjonen:
- Hvis du har tallene i kolonne A (si fra A2:A100), og velg deretter B2:B100 og skriv inn følgende formel:
=TEXT(A2,»00000″) - Trykk Ctrl + Enter for å bruke formelen til alle de merkede cellene.
Dette vil vise alle tallene som fem sifre, der ledende 0-er er lagt til automatisk hvis tallet er mindre enn 5 sifre.,
En av fordelene med å konvertere data til tekst er at du nå kan bruke den i oppslag formler som VLOOKUP eller INDEKS/MATCH for å hente detaljer for en arbeidstaker å bruke hans/hennes ansatt id.
Merk: Denne teknikken ville arbeide bare for en numerisk dataset. I tilfelle du har ansatt-id-er som A1, A2, A3 og så videre, så dette er tekst og ikke ville endre seg når du bruker det egendefinerte formatet som vist ovenfor.
Legg til Ledende Nuller ved Hjelp av REPT og LEN Funksjoner
Når du skal Bruke: Når du har et datasett som er numerisk/alfanumeriske, og du vil at resultatet skal være tekst.,
ulempen ved hjelp av TEKST-funksjonen var at det ville bare arbeide med numeriske data. Men i tilfelle du har et alfanumerisk dataset (si A1, A2, A3, og så videre), og deretter TEKST-funksjonen vil mislykkes.
I slike tilfeller er det en kombinasjon av REPT og LEN funksjonen gjør susen.
Her er hvordan du gjør det:
Dette ville gjøre alle tall/tekst 5 tegn med ledende nuller uansett hvor det er behov.
Her er hvordan fungerer denne formelen:
- LEN(A2) gir lengden av strengen/tall i cellen.
- =REPT(0,5-LEN(A2)) ville gi antall 0 som bør legges., Her har jeg brukt 5 i formel som var den maksimale lengden av strengen/tall i mitt datasett. Du kan endre dette i henhold til dine data.
- =REPT(0,5-LEN(A2))&A2 ville bare legge til antall nuller til verdien av cellen. For eksempel, hvis verdien i celle er 123, dette ville gå tilbake 00123.
Legg til Ledende Nuller ved å Bruke Custom-Funksjonen (VBA)
Hvis du legger til ledende nuller i Excel er noe du er nødt til å gjøre ganske ofte, ved hjelp av en egendefinert funksjon er en god idé.,
Her er de VBA-kode som vil lage en enkel funksjon for å legge til ledende nuller:
'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
Bare legge til denne koden for å modul-kode-vinduet, og du vil være i stand til å bruke den akkurat som alle andre regneark funksjon.
Eller lage en add-in for den og være i stand til å dele den med dine kolleger.
Du Kan Også Gjerne Følgende Excel-Tutorials:
- Hvordan å Fjerne Ledende Nuller i Excel
- Fjern Mellomrom i Excel – Ledende, Etterfølgende, og Dobbel
- Seks Utrolige Ting Excel Egendefinert tallformat kan Gjøre for Deg.,
- 10 Måter å Rense Data i Excel-Regneark.
- Hvordan å Låse Celler i Excel.
- Hvordan å Legge Desimaler i Excel (Automatisk)