Sådan tilføjes ledende nuller i Excel - alt hvad du behøver at vide

Der er situationer, hvor du skal tilføje ledende nuller til et datasæt i Excel. Dette kan være tilfældet, hvis du vedligeholder poster i Excel, f.eks. Medarbejder -id'er eller transaktions -id'er.

For eksempel kan du få et konsekvent udseende i dit datasæt som vist herunder:

I denne vejledning lærer du forskellige måder at tilføje førende nuller i Excel:

• Konvertering af format til tekst
• Brug af tilpasset nummerformatering
• Brug af tekstfunktion
• Brug af REPT/LEN -funktioner
• Brug af VBA

Hver af disse metoder har nogle fordele og ulemper (dækket i hvert afsnit).

Lad os se, hvordan hver af disse fungerer.

Tilføj ledende nuller ved at konvertere formatet til tekst

Hvornår skal man bruge: Når du har et lille numerisk datasæt, og du planlægger at foretage denne redigering manuelt.

Antag, at du har medarbejder -id'er fra marketingafdelingen som vist nedenfor, og du vil få disse Id'er til at se konsistente ud ved at tilføje førende nuller.

Så du prøver at ændre id'et ved at indtaste førende nuller (00001 i stedet for 1).

Men til din forbløffelse konverterer Excel det tilbage til 1.

Dette sker, da Excel forstår, at 00001 og 1 er de samme tal og skal følge de samme visningsregler.

Nu så frustrerende som det kan være for dig, har Excel sine grunde.

Så for at få arbejdet udført uden at bøje Excel -regler skal du drage fordel af, at denne regel ikke gælder for tekstformatering.

Så her er hvad du skal gøre:

1. Vælg de celler, hvor du vil tilføje ledende nuller manuelt.
2. Gå til Hjem → Nummergruppe, og vælg Tekst fra rullemenuen.

Det er det!

Nu, når du indtaster ledende nuller manuelt, ville Excel let overholde.

Forsigtig: Når du konverterer formatet til tekst, fungerer nogle Excel -funktioner ikke korrekt. For eksempel ignorerer SUM/COUNT -funktionen cellen, da den er i tekstformatet.

Tilføj ledende nuller ved hjælp af brugerdefineret talformatering

Hvornår skal man bruge: Når du har et numerisk datasæt, og du vil have resultatet til at være numerisk (ikke tekst).

Når du viser et tal i et specifikt format, ændrer det ikke den underliggende værdi af nummeret. For eksempel kan jeg vise tallet 1000 som 1000 eller 1.000 eller 1000.00 eller 001000 eller 26-09-1902 (lige datoer er tal i backend i Excel).

På alle de forskellige måder at vise nummeret på, ændres nummerets værdi aldrig. Det er kun måden det vises på, der ændres.

For at tilføje førende nuller kan vi formatere det for at vise det på den måde, mens den underliggende værdi forbliver uændret.

Her er trinene til at bruge denne teknik til at tilføje førende nuller i Excel:

1. Vælg de celler, hvor du vil tilføje ledende nuller.
2. Gå til Start → Nummergruppe, og klik på dialogboksen (en lille vippet pil nederst til højre). Dette åbner dialogboksen Format celler. Alternativt kan du også bruge tastaturgenvejen: Control + 1.
3. Vælg Brugerdefineret på kategorilisten i dialogboksen Formatér celler under fanen Nummer.
4. Indtast 00000 i feltet Type
5. Klik på OK.

Hvis du gør dette, vises altid alle tallene som fem cifre, hvor de førende 0’er automatisk tilføjes, hvis tallet er mindre end 5 cifre. Så 10 ville blive 00010 og 100 ville blive 00100.

I dette tilfælde har vi brugt seks nuller, men hvis dine data har tal med flere cifre, skal du bruge formatet i overensstemmelse hermed.

Bemærk: Denne teknik fungerer kun for et numerisk datasæt. Hvis du har medarbejder -id'er som A1, A2, A3 og så videre, så er disse tekst og ændres ikke, når du anvender det tilpassede format som vist ovenfor.

Tilføj ledende nuller ved hjælp af TEKST -funktion

Hvornår skal man bruge: Når du vil have resultatet til at være tekst.

Med TEKST -funktionen kan du ændre værdien til det ønskede format.

Hvis du f.eks. Ønsker, at 1 skal vises som 001, kan du bruge TEKST -funktionen til det.

Husk dog, at TEKST -funktionen ville ændre formatet og gøre det TEKST. Det betyder, at når du laver 1 som 001, behandler Excel det nye resultat som tekst med tre tegn (ligesom abc eller xyz).

Sådan tilføjes førende nuller ved hjælp af TEKST -funktionen:

1. Hvis du har tallene i kolonne A (sig fra A2: A100), skal du vælge B2: B100 og indtaste følgende formel:
   = TEKST (A2, ”00000 ″)
2. Tryk på Control + Enter for at anvende formlen på alle de markerede celler.

Dette viser alle tallene som fem cifre, hvor de førende 0'er automatisk tilføjes, hvis tallet er mindre end 5 cifre.

En fordel ved at konvertere data til tekst er, at du nu kan bruge dem i opslagsformler som VLOOKUP eller INDEX/MATCH til at hente detaljerne for en medarbejder ved hjælp af hans/hendes medarbejder -id.

Bemærk: Denne teknik fungerer kun for et numerisk datasæt. Hvis du har medarbejder -id'er som A1, A2, A3 og så videre, så er disse tekst og ændres ikke, når du anvender det tilpassede format som vist ovenfor.

Tilføj ledende nuller ved hjælp af REPT- og LEN -funktioner

Hvornår skal man bruge: Når du har et datasæt, der er numerisk/alfanumerisk, og du vil have resultatet til at være tekst.

Ulempen ved at bruge TEKST -funktionen var, at den kun ville fungere med numeriske data. Men hvis du har et alfanumerisk datasæt (f.eks. A1, A2, A3 og så videre), ville TEKST -funktionen mislykkes.

I sådanne tilfælde gør en kombination af REPT- og LEN -funktionen tricket.

Sådan gør du det:

1. Hvis du har tallene i kolonne A (sig fra A2: A100), skal du vælge B2: B100 og indtaste følgende formel:
   = REPT (0,5-LEN (A2)) & A2
2. Tryk på Control + Enter for at anvende formlen på alle de markerede celler.

Dette ville gøre alle numre/strenge 5 tegn lange med førende nuller, hvor det er nødvendigt.

Sådan fungerer denne formel:

• LEN (A2) angiver længden af ​​strengen/tallene i cellen.
• = REPT (0,5-LEN (A2)) ville give tallet 0, der skal tilføjes. Her har jeg brugt 5 i formlen, da det var den maksimale længde af streng/tal i mit datasæt. Du kan ændre dette i henhold til dine data.
• = REPT (0,5-LEN (A2)) & A2 ville blot tilføje antallet af nuller til værdien af ​​cellen. For eksempel, hvis værdien i cellen er 123, returnerer dette 00123.

Tilføj ledende nuller ved hjælp af brugerdefineret funktion (VBA)

Hvis tilføjelse af førende nuller i Excel er noget, du skal gøre ganske ofte, er det en god idé at bruge en brugerdefineret funktion.

Her er VBA -koden, der skaber en enkel funktion til tilføjelse af førende 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 Herefter Resultat = Resultat & Midt (ref, i, 1) Ellers Resultat = "0" & ​​Resultat Slut Hvis Næste i AddLeadingZeroes = Resultat Slutfunktion

Tilføj blot denne kode til modulkodevinduet, og du vil kunne bruge den ligesom alle andre regnearksfunktioner.

Eller opret et tilføjelsesprogram til det og være i stand til at dele det med dine kolleger.