I den här artikeln lär vi oss olika sätt att lägga till nollor (0s) framför i Excel.
Scenario:
Lägger till noll framför siffran i Excel. Standard Excel tar inte nollor framför siffran i cellen. Har du någonsin försökt mata in data som 000123 i Excel? Du kommer förmodligen snabbt att märka att Excel automatiskt tar bort de ledande nollorna från inmatningsnumret. Detta kan vara riktigt irriterande om du vill ha de ledande nollorna i data och inte vet hur du gör att Excel behåller dem. Lyckligtvis finns det en hel del sätt att lägga in dina nummer med nollor i början. I den här artikeln kommer jag att förklara olika sätt att lägga till eller behålla de ledande nollorna i dina nummer.
Olika funktioner för att lägga till noll (0) framför
Som vi vet är TEXT -funktionen den mest använda funktionen för att lägga till 0 framför men här ska jag förklara fler sätt att lägga till nollor (0s) till siffror.
- TEXT -funktion
- HÖGER -funktion
- BASE -funktion
- Lägg till 0 i pivottabellvärden
Formatera som text
En av lösningarna är att konvertera talets format till text för om vi matar in 00xyz, anser excel detta värde som text och håller dess 0 framför. Det finns olika sätt att konvertera en talcell till textformat.
- Använda anpassat format
- Använda TEXT -funktionen
- Använda Apostrophe (') i början av värdet vid inmatning
Anpassat format
Låt oss förstå dessa metoder en efter en. Att använda ett anpassat format för att ändra formatet är det enklaste sättet att ändra de redan befintliga siffrorna inom intervallet. Välj bara intervallet och använd genväg Ctrl + 1 eller välj Mer nummerformat från rullgardinsmenyn på fliken Hem som visar Allmänt.
Ändra formatet från Allmänt till Text.
- Välj det cellintervall du vill ange ledande nollor i.
- Gå till fliken Hem.
- Klicka på Format -rullgardinsmenyn i avsnittet Nummer.
- Välj Text från formatalternativen.
Om du nu försöker ange siffror med ledande nollor försvinner de inte eftersom de anges som textvärden istället för siffror. Excel -användare kan lägga till en anpassad formatering för att formatera nummer med ledande nollor.
De verkar dock bara ha ledande nollor. Den underliggande informationen kommer inte att ändras till text med de nollor som läggs till.
Lägg till ett anpassat format för att visa ledande nollor.
- Välj det cellintervall du vill lägga till ledande nollor till och öppna dialogrutan Formatera celler.
- Högerklicka och välj Formatera celler.
- Använd kortkommandot Ctrl + 1.
- Gå till fliken Nummer.
- Välj Anpassad från kategorialternativen.
- Lägg till ett nytt anpassat format i typinmatningen. Om du vill att det totala antalet siffror inklusive eventuella inledande nollor ska vara 6 lägger du till 000000 som det anpassade formatet.
- Tryck på OK -knappen.
TEXT -funktion
Med TEXT -funktionen kan du tillämpa en anpassad formatering på valfri taldata som redan finns i kalkylarket.
= TEXT (värde, format)
- Värde - Detta är det värde du vill konvertera till text och tillämpa formatering på.
- Format - Detta är den formatering som ska tillämpas.
= TEXT (B3, "000000")
Om du ville lägga till nollor till ett tal i cell B3 så att det totala antalet siffror är 6 kan du använda ovanstående formel.
Använda Apostrophe (') i början
Du kan tvinga Excel att ange ett tal som text med hjälp av en ledande apostrof.
Det betyder att du kommer att kunna hålla dessa nollor framför dig när du anger dina data.
Denna metod är snabb och enkel när du anger data. Skriv bara ett tecken före alla siffror. Detta kommer att berätta för Excel att data är avsedda att vara text och inte ett nummer.
När du trycker på Enter förblir de främsta nollorna synliga i kalkylbladet. 'Kommer inte att synas i kalkylbladet, men finns fortfarande kvar och kan ses i formelfältet när den aktiva cellmarkören finns på cellen.
HÖGER Funktion
Ett annat sätt att få fram dina nollor med en formel är att använda HÖGER -funktionen. Du kan sammanfoga en sträng med nollor till numret och sedan skära av tillbehören med hjälp av HÖGER -funktionen.
HÖGER -funktionen kommer att extrahera de högsta N -tecknen från ett textvärde.
= HÖGER (Text, [Number])
- Text - Det här är texten som du vill extrahera tecken från.
- Siffra (Frivillig)- Detta är antalet tecken som ska extraheras från texten. Om detta argument inte matas in extraheras bara det första tecknet.
= HÖGER ("000000" & B3, 6)
Ovanstående formel sammanfogar flera nollor till början av ett tal i cell B3, sedan returnerar det de högst 6 tecknen vilket resulterar i några ledande nollor.
BASE -funktion
Du kommer att märka att den här artikeln beskriver 9 sätt att lägga till ledande nollor, men min YouTube -video visar bara 8 sätt.
Det beror på att jag inte visste att du kunde använda BASE -funktionen för att lägga till ledande nollor tills någon nämnde det i videokommentarer.
Med BASE -funktionen kan du konvertera ett tal till en textrepresentation med en given bas.
De siffror du vanligtvis använder är bas 10, men du kan använda den här funktionen för att konvertera ett bas 10 -tal till en bas 2 (binär) representation.
= BASE (antal, bas, [minlängd])
- Antal - Detta är det nummer du vill konvertera till ett textvärde i en annan bas.
- Bas - Detta är basen du vill konvertera värdet till.
- Minlängd (Frivillig) - Detta är den minsta längden på tecknen i det konverterade textvärdet.
Använd formeln
| = BASE (B3,10,6) |
Ovanstående formel kommer att konvertera ett tal i cell B3 till bas 10 (det är redan ett bas 10 -tal men det kommer att behålla det som bas 10) och konvertera det till ett textvärde med minst 6 tecken. Om det konverterade värdet är mindre än 6 tecken, lägger det in värdet med nollor för att få minst 6 tecken.
Definitivt en cool ny användning för en funktion som jag annars aldrig använder.
Lägg till noll (0) bara framför siffror, inte på textvärden
Här kommer vi att överväga scenariot som täcker både text- och talvärden i samma lista. Nu anser Excel att varje cell är olika när det gäller format. Nu hur man låter excel veta att om cellen har ett talvärde lägger du till 0 framför eller om cellen har text eller något annat värde, låt den vara som den är.
För detta kommer vi att använda en kombination av två funktioner IF och ISNUMBER -funktionen. Här har vi några värderingar att testa.
Den generiska formeln fortsätter som
= OM (ISNUMBER (cell_ref), "0" & cell_ref, cell_ref)
Här
cell_ref: cellreferens för motsvarande cell
Använd formeln i cellen
| = OM (ISNUMBER (F3), "0" & F3, F3) |
Förklaring: ISNUMBER -funktionen kontrollerar cellen efter nummer. OM funktionen kontrollerar om cellen har ett tal lägger den till noll framför talet annars returnerar den bara cellvärdet.
Som du kan se på bilden ovan läggs nollan (0) framför talet. Låt oss kontrollera om den här funktionen fungerar på andra värden. Kopiera formeln i andra celler, välj cellerna som tar den första cellen där formeln redan tillämpas, använd genvägstangenten Ctrl+D
det kommer att stoppa värdet med nollor för att få minst en nolla framför tecken. Definitivt en cool ny användning för en funktion som jag annars aldrig använder.
Power Pivot beräknad kolumn
Det finns ett annat alternativ för att lägga till nollor i pivottabellen. Du kan lägga till dem i en beräknad kolumn i Power Pivot. På så sätt kan du använda den nya kolumnen i området Filter, rader eller kolumn i en pivottabell.
= FORMAT (Numbers [Number], "000000")
I tillägget Power Pivot kan du lägga till en ny kolumn och använda FORMAT-funktionen för att skapa ledande nollor i kolumnen med en formel som ovan. En beräknad kolumn beräknar ett värde för varje rad, så det är inte nödvändigt att linda funktionen inuti en cell. Nu kan du använda den här nya kolumnen i en pivottabells radområde precis som alla andra datakolumner.
Sammanfattning
Det kan vara frustrerande att se alla dina nollor försvinna när vi inte vet varför det händer eller hur vi kan förhindra det. Som du kan se finns det många sätt att se till att alla dessa nollor fastnar i dina data. Oavsett ditt krav finns det säkert en bra lösning för dig.
Här är alla observationsanteckningar med hjälp av formeln i Excel
Anmärkningar:
- Formeln kan användas för både texter och siffror.
- Sök och ersätt texten bör sorteras i en tabell. När vi använder index för att hitta och ersätta, se till att "hitta" text har samma index som "ersätt" text. Det är bättre att ha båda listorna sida vid sida som vi har i det andra exemplet.
- Den generiska formeln har två SUBSTITUTE -funktioner. Du kommer att behöva lika många ersättningsfunktioner som många du har ersättare att göra.
- jokertecken hjälper till att extrahera värde från underlaget.
- Funktionen stöder logiska operatorer som,, = men dessa används med dubbla citattecken ( " ) med argument. Men om du använder cellreferens för kriterierna kan citat ignoreras.
- Kriterier argument bör använda citattecken (") för texter (som" Gillen ") eller siffror (som"> 50 ").
Hoppas att den här artikeln om olika sätt att lägga till nollor (0s) framför i Excel är förklarande. Hitta fler artiklar om beräkning av värden och relaterade Excel -formler här. Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen.
Excel REPLACE vs SUBSTITUTE -funktion: Funktionerna REPLACE och SUBSTITUTE är de mest missförstådda funktionerna. För att hitta och ersätta en given text använder vi ersättningsfunktionen. Där REPLACE används för att ersätta ett antal tecken i strängen.
Ersätt text från slutet av en sträng med början från variabel position: För att ersätta text från slutet av strängen använder vi funktionen REPLACE. REPLACE -funktionen använder textens position i strängen för att ersätta.
Så här kontrollerar du om en sträng innehåller en av många texter i Excel: För att kontrollera om en sträng innehåller någon av flera texter använder vi denna formel. Vi använder SUMM -funktionen för att summera alla matchningar och utför sedan en logik för att kontrollera om strängen innehåller någon av de flera strängarna.
Räkna celler som innehåller specifik text: En enkel COUNTIF -funktion gör magin. För att räkna antalet flera celler som innehåller en given sträng använder vi jokerteckenoperatorn med funktionen RÄNTA.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet : Bli snabbare på dina uppgifter i Excel. Dessa genvägar hjälper dig att öka din arbetseffektivitet i Excel.
Så här använder du VLOOKUP -funktionen i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.