Vezető nullák hozzáadása az Excelben

Ez az oktatóanyag bemutatja a kezdő nullák hozzáadásának vagy megtartásának, valamint a kezdő nullák eltávolításának különböző módszereit az Excelben.

Amikor egy vagy több kezdő nullát tartalmazó számokat ír be vagy importál, az Excel automatikusan eltávolítja ezeket a nullákat, és csak maga a szám jelenik meg a cellákban (652). Ennek az az oka, hogy a kezdő nullák nem szükségesek a számításokhoz, és nem számítanak.

Előfordul azonban, hogy szükség van ezekre a kezdő nullákra, például amikor azonosítószámokat, telefonszámokat, hitelkártyaszámokat, termékkódokat, irányítószámokat stb. ír be. Szerencsére az Excel számos lehetőséget kínál a kezdő nullák hozzáadására vagy megtartására. sejtekben. Ebben a cikkben bemutatjuk a kezdő nullák hozzáadásának vagy megtartásának, valamint a kezdő nullák eltávolításának különböző módjait.

Vezető nullák hozzáadása az Excelben

Lényegében két módszer használható a kezdő nullák hozzáadására: az egyik, formázza a számot „Szöveg”-ként; Másodszor, használjon egyéni formázást a kezdő nullák hozzáadásához. A használni kívánt módszer attól függhet, hogy mit szeretne kezdeni a számmal.

Érdemes lehet a kezdő nullát hozzáadni, amikor egyedi azonosítószámokat, számlaszámokat, társadalombiztosítási számokat vagy irányítószámokat ír be. De ezeket a számokat nem fogja használni számításokhoz vagy függvényekhez, ezért a legjobb, ha ezeket konvertálja. számokat szöveggé. Soha nem összegezné vagy átlagolná a telefonszámokat vagy számlaszámokat.

Többféleképpen is hozzáadhat vagy beilleszthet nullákat a számok elé, szöveges formázással:

  • A cellaformátum módosítása szövegre
  • Aposztróf hozzáadása (')
  • SZÖVEG funkció használata
  • A REPT/LEN funkció használata
  • Használja a CONCATENATE funkciót/és jel operátort (&)
  • A RIGHT funkció használata

A cellaformátum módosítása szövegre

Ez az egyik legegyszerűbb módja a kezdő nullák hozzáadásának a számokhoz. Ha csak számokat akar beírni, és a kezdő nullákat szeretne beírni, akkor ez a módszer az Ön számára. Ha a cellaformátumot Általánosról vagy Számról szövegre változtatja, arra kényszerítheti az Excelt, hogy a számokat szöveges értékként kezelje, és bármi, amit a cellába ír be, pontosan ugyanaz marad. Íme, hogyan kell csinálni:

Jelölje ki azt a cella(ka)t, amely(ek)be kezdő nullákat szeretne hozzáadni. Lépjen a „Főoldal” fülre, kattintson a „Formátum” legördülő listára a Számok csoportban, és válassza a „Szöveg” lehetőséget a formázási lehetőségek közül.

Most, amikor beírja a számokat, az Excel nem törli a kezdő nullát.

Előfordulhat, hogy a cella bal felső sarkában egy kis zöld háromszöget (Hibajelző) láthat, és amikor kiválasztja azt a cellát, megjelenik egy figyelmeztető tábla, amely jelzi, hogy a számot szövegként tárolta.

A hibaüzenet eltávolításához válassza ki a cella(ka)t, kattintson a figyelmeztető jelre, majd válassza a „Hiba figyelmen kívül hagyása” lehetőséget a listából.

A telefonszámokat szóközzel vagy kötőjellel is beírhatja a számok közé, az Excel ezeket a számokat automatikusan szövegként kezeli.

A Leading használata Aposztróf (')

A kezdő nullák hozzáadásának másik módja az Excelben, ha a szám elejére egy aposztrófot (') adunk meg. Ez arra kényszeríti az Excelt, hogy számot írjon be szövegként.

Csak írjon be egy aposztrófot a számok elé, és nyomja meg az „Enter” billentyűt. Az Excel érintetlenül hagyja a kezdő nullákat, de a (') csak akkor lesz látható a munkalapon, ha kijelöli a cellát.

Szövegfüggvény használata

A fenti módszer nullákat ad a számokhoz, ahogy beírja őket, de ha már van számlistája, és a kezdő nullákat szeretné eléjük illeszteni, akkor a SZÖVEG funkció a megfelelő módszer az Ön számára. A SZÖVEG funkció lehetővé teszi számok szöveges karakterláncokká alakítását, miközben egyéni formázást alkalmaz.

A SZÖVEG szintaxisa függvény:

= SZÖVEG(érték, formátum_szöveg)

Ahol,

  • érték - Ez az a számérték, amelyet szöveggé kell konvertálnia és formázást kell alkalmaznia.
  • format_text – az alkalmazni kívánt formátum.

A SZÖVEG funkcióval megadhatja, hogy a szám hány számjegyű legyen. Például, ha azt szeretné, hogy a számok 8 számjegyből álljanak, akkor írjon be 8 nullát a függvény második argumentumába: „00000000”. Ha egy cellában 6 jegyű szám van, akkor a függvény manuálisan hozzáad 2 kezdő nullát, és ha 2 jegyű számok vannak, például 56, a többi nulla lesz (00000056).

Például a kezdő nullák hozzáadásához és a számok hatjegyűvé tételéhez használja ezt a képletet:

=SZÖVEG(A2"000000")

Mivel a képlet második argumentumában 6 nulla van, a függvény a számsort szöveges karakterláncsá alakítja, és 5 kezdő nullát ad hozzá, hogy a karakterlánc 6 számjegyű legyen.

Jegyzet: Kérjük, ne felejtse el a formátumkódokat idézőjelbe tenni a függvényben.

Most ugyanezt a képletet alkalmazhatja a többi cellára is a kitöltő fogantyújának húzásával. Amint láthatja, a függvény a számokat szöveggé alakítja, és a számokhoz bevezető nullákat ad, hogy a számjegyek teljes száma 6 legyen.

A SZÖVEG függvény mindig szöveges karakterláncként ad vissza értéket, nem számként, ezért nem fogja tudni használni őket aritmetikai számításokhoz, de továbbra is használhatja őket olyan keresési képletekben, mint például a VLOOKUP vagy az INDEX/MATCH, hogy lekérje a részleteket. termék azonosítók használatával.

CONCATENATE funkció/és operátor (&) használata

Ha egy oszlopban az összes szám elé fix számú kezdő nullát szeretne hozzáadni, használhatja a CONCATENATE funkciót vagy az "és" operátort (&).

A CONCATENATE függvény szintaxisa:

=ÖSSZEGZÉS(szöveg1, [szöveg2], ...)

Ahol,

szöveg1 – A szám elé beszúrandó nullák száma.

szöveg2 – Az eredeti szám vagy cellahivatkozás

Az "és" jel operátor szintaxisa:

=Érték_1 és Érték_2 

Ahol,

Az érték_1 a szám elé szúrandó kezdő nullák, az Érték_2 pedig a szám.

Ha például csak két nullát szeretne hozzáadni egy szám elé, használja a következő képletet:

=ÖSSZESÍTÉS("00",A2)

Az első argumentum két nulla ("00"), mert az A2-ben lévő szám elé szeretnénk két nullát beírni (ami a második argumentum).

Vagy,

="00"&A2

Itt az első argumentum 2 nulla, ezt követi az „&” operátor, a második argumentum pedig a szám.

Amint látható, a képlet csak két kezdő nullát ad egy oszlopban lévő összes számhoz, függetlenül attól, hogy a szám hány számjegyet tartalmaz.

Mindkét képlet bizonyos számú nullát egyesít az eredeti számok előtt, és szöveges karakterláncként tárolja azokat.

A REPT/LEN funkció használata

Ha a numerikus vagy alfanumerikus adatokhoz vezető nullákat szeretne hozzáadni, és a karakterláncot szöveggé szeretné alakítani, használja a REPT funkciót. A REPT funkció egy karakter(ek) meghatározott számú ismétlésére szolgál. Ez a funkció arra is használható, hogy a szám elé fix számokat szúrjon be kezdő nullákból.

=REPT(szöveg, szám_szorok)

Ahol a „szöveg” az a karakter, amelyet meg akarunk ismételni (esetünkben „0”), a „number_times” argumentum pedig az, hogy hányszor szeretnénk megismételni ezt a karaktert.

Például, ha számok előtt öt nullát szeretne generálni, a képlet így néz ki:

=REPT(0,5)&A2

A képlet annyit tesz, hogy megismétli 5 nullát, és összekapcsolja az A2-ben lévő számsort, és visszaadja az eredményt. Ezután a képletet a kitöltő fogantyúval alkalmazza a B2:B6 cellára.

A fenti képlet fix számú nullákat ad a szám elé, de a szám teljes hossza a számtól függően változik.

Ha egy adott karakter hosszúságú (fix hosszúságú) karakterlánc létrehozásához bevezető nullákat szeretne hozzáadni, akkor a REPT és a LEN függvényeket együtt használhatja.

Szintaxis:

=REPT(szöveg, szám_szorok-LEN(szöveg))&cella

Például, ha az A2-ben lévő értékhez előtagozott nullákat szeretne hozzáadni, és egy 5 karakter hosszúságú karakterláncot szeretne létrehozni, próbálja ki a következő képletet:

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

Itt a „LEN(A2)” megkapja az A2 cellában lévő karakterlánc/számok teljes hosszát. Az „5” a karakterlánc/számok maximális hossza a cellában. És a „REPT(0,5-LEN(A2))” rész hozzáadja a nullák számát úgy, hogy az A2-ben lévő karakterlánc hosszát kivonja a nullák maximális számából (5). Ezután néhány 0-t összekapcsolunk az A2 értéke előtt, hogy egy rögzített hosszúságú karakterláncot hozzunk létre.

A RIGHT funkció használata

Egy másik módja annak, hogy az Excelben a karakterlánc előtt nullákat töltsön ki, a JOBB funkció használata.

A RIGHT függvény számos nullát tud hozzáadni egy szám elejéhez, és kivonja a jobb szélső N karaktert az értékből.

Szintaxis:

= JOBBRA (szöveg, karakterek száma)
  • szöveg az a cella vagy érték, amelyből karaktereket szeretne kinyerni.
  • karakterek száma a szövegből kivonható karakterek száma. Ha ez az argumentum nincs megadva, akkor csak az első karakter kerül kibontásra.

Ennél a módszernél a nullák maximális számát összefűzzük a „szöveg” argumentumban szereplő karakterláncot tartalmazó cellahivatkozással.

Ha egy hatjegyű számot szeretne létrehozni az A-beli számsor alapján, kezdő nullákkal, próbálja ki a következő képletet:

=JOBBRA("0000000"&A2;6)

A képlet első argumentuma (szövege) 7 nullát ad az A2-ben lévő értékhez ("0000000" és A2), majd visszaadja a jobb szélső 7 karaktert, ami néhány kezdő nullát eredményez.

Vezető nullák hozzáadása egyéni számformázással

Ha a fenti módszerek bármelyikét alkalmazza a bevezető nullák szám elé helyezésére, mindig szöveges karakterláncot kap, nem számot. És nem sok hasznuk lesz a számításokban vagy a numerikus képletekben.

A kezdő nullák hozzáadásának legjobb módja az Excelben egyéni számformázás alkalmazása. Ha a kezdő nullákat úgy ad hozzá, hogy egyéni számformátumot ad a cellához, az nem módosítja a cella értékét, hanem csak a megjelenítési módját. Az érték továbbra is szám marad, nem szöveg.

A cellák számformátumának megváltoztatásához kövesse az alábbi lépéseket:

Válassza ki azt a cellát vagy cellatartományt, ahol a kezdő nullákat meg szeretné jeleníteni. Ezután kattintson a jobb gombbal bárhová a kiválasztott tartományon belül, és válassza ki a „Cellák formázása” lehetőséget a helyi menüből. Vagy nyomja meg a Ctrl + 1 gyorsbillentyűket.

A Cellák formázása ablakban lépjen a „Szám” fülre, és a Kategória opciók alatt válassza az „Egyéni” lehetőséget.

Írja be a nullák számát a „Típus:” mezőbe, hogy megadja a cellában megjeleníteni kívánt számjegyek teljes számát. Ha például azt szeretné, hogy a szám 6 számjegyből álljon, akkor egyéni formátumkódként írja be a „000000” kódot. Ezután kattintson az „OK” gombra az alkalmazáshoz.

Ez a bevezető nullákat jeleníti meg a számok előtt, és ha a szám kevesebb, mint 6 számjegy, akkor nullát ír be.

Úgy tűnik, hogy a számok kezdő nullákat tartalmaznak, miközben a mögöttes érték változatlan marad. Ha egyéni formázású cellát választ ki, akkor a képletsorban az eredeti szám jelenik meg

Számos digitális helyőrző használható egyéni számformátumban. De csak két elsődleges helyőrző használható a számok kezdő nulláinak hozzáadásához.

  • 0 – Ez a számjegy helyőrző, amely extra nullákat jelenít meg. Kényszerített számjegyeket jelenít meg 0-9 között, függetlenül attól, hogy a számjegy releváns-e az értékhez, vagy sem. Például, ha 000.00 formátumkóddal 2.5-öt ír be, akkor 002.50 jelenik meg.
  • # – Ez a számjegy helyőrző, amely opcionális számjegyeket jelenít meg, és nem tartalmaz extra nullákat. Ha például 123-at ír be a 000# formázási kóddal, akkor a 0123 jelenik meg.

Ezenkívül a formátumkódban szereplő írásjelek vagy egyéb karakterek úgy jelennek meg, ahogy vannak. Használhat karaktereket, például kötőjelet (-), vesszőt (,), perjelet (/) stb.

Például egyéni formátum használatával a számokat telefonszámként is formázhatja.

A Kód formázása a Cellák formázásában párbeszédpanel:

Az eredmény:

Alkalmazzuk ezt a formázási kódot a következő példában:

##0000

Amint látható, a „0” plusz nullákat ad hozzá, míg a „#” nem jelent jelentéktelen nullákat:

A Cellák formázása párbeszédpanel „Speciális formátumok” részében előre meghatározott formátumkódokat is használhat irányítószámokhoz, telefonszámokhoz és társadalombiztosítási számokhoz.

A következő táblázat azokat a számokat mutatja be nullákkal, amelyeknél különböző „Speciális” formátumú kódok vannak alkalmazva a különböző oszlopokhoz:

A vezető nullák eltávolítása az Excelben

Most megtanulta, hogyan kell kezdő nullákat hozzáadni az Excelhez, lássuk, hogyan távolíthatja el a kezdő nullákat a karakterláncok számából. Néha, amikor adatokat importál egy külső forrásból, előfordulhat, hogy a számok előtagjai nullák, és szövegként formázódnak. Ilyen esetekben el kell távolítani a kezdő nullákat, és vissza kell alakítani őket számokká, hogy felhasználhassák a képletekben.

Különféle módon távolíthatja el a kezdő nullákat az Excelben, és egyenként fogjuk látni őket.

Távolítsa el a vezető nullákat a cellaformázás megváltoztatásával

Ha a kezdő nullákat egyéni számformázással adtak hozzá, akkor a cellák formátumának megváltoztatásával könnyen eltávolíthatók. A címsorból megtudhatja, hogy a cellái egyedi formátumúak-e (a nullák a cellában, nem a címsorban fognak megjelenni).

Az előtagozott nullák eltávolításához jelölje ki a cellákat a kezdő nullákkal, kattintson a „Számformátum” mezőre, és válassza az „Általános” vagy a „Szám” formázási lehetőséget.

Most eltűntek a kezdő nullák:

Törölje a bevezető nullákat a szöveg számokká alakításával

Ha a kezdő nullákat a cellaformátum megváltoztatásával vagy aposztrófokkal adták meg a számok elé, vagy automatikusan hozzáadták az adatok importálásakor, akkor a legegyszerűbb módja a Hibaellenőrzés opció használatával számokká alakítani. Íme, hogyan kell csinálni:

Ezt a módszert akkor használhatja, ha a számok balra vannak igazítva, és a cellákban egy kis zöld háromszög (hibajelző) található a cellák bal felső sarkában. Ez azt jelenti, hogy a számok szövegként vannak formázva.

Jelölje ki ezeket a cellákat, és egy sárga figyelmeztetés jelenik meg a kijelölés jobb felső részén. Ezután kattintson a „Konvertálás számra” lehetőségre a legördülő menüben.

A nullák törlődnek, és a számok vissza lesznek konvertálva számformátumba (jobbra igazítva).

A vezető nullák eltávolítása által Szorzás/osztás 1-gyel

Egy másik egyszerű és legjobb módja az első lépés eltávolításának a számok 1-gyel való szorzása vagy osztása. Az érték osztása vagy szorzása nem változtatja meg az értéket, egyszerűen visszaváltja az értéket számmá, és eltávolítja a kezdő nullákat.

Ehhez írja be az alábbi példában szereplő képletet egy cellába, és nyomja meg az ENTER billentyűt. A bevezető nullákat eltávolítja, és a karakterláncot számmá alakítja vissza.

Ezután alkalmazza ezt a képletet más cellákra a kitöltő fogantyú segítségével.

Ugyanezt az eredményt érheti el a „Speciális beillesztés” parancs használatával. Itt van, hogyan:

Írja be az „1” számértéket egy cellába (mondjuk a B2-be), és másolja ki az értéket.

Ezután jelölje ki azokat a cellákat, amelyekből el kívánja távolítani a kezdő nullákat. Ezután kattintson a jobb gombbal a kijelölésre, majd válassza a „Speciális beillesztés” lehetőséget.

A Speciális beillesztés párbeszédpanel Művelet alatt válassza a „Szorzás” vagy „Osztás” lehetőséget, majd kattintson az „OK” gombra.

Ez az, a bevezető nullák el lesznek távolítva, így a karakterláncok számként maradnak meg.

Távolítsa el a vezető nullákat képletek segítségével

Az előtagozott nullák törlésének másik egyszerű módja az ÉRTÉK függvény használata. Ez a módszer akkor is hasznos lehet, ha a kezdő nullákat más képlet vagy aposztróf használatával, vagy egyéni formázással adta hozzá.

=ÉRTÉK(A1)

A képlet argumentuma lehet egy érték vagy az értéket tartalmazó cellahivatkozás. A képlet eltávolítja a kezdő nullákat, és az értéket szövegből számmá alakítja. Ezután alkalmazza a képletet a többi cellára.

Néha előfordulhat, hogy el szeretné távolítani a kezdő nullákat, de szeretné megtartani a számokat szöveges formátumban. Ilyen esetekben a TEXT() és VALUE () függvényeket együtt kell használni, így:

=SZÖVEG(ÉRTÉK(A1),"#")

Az ÉRTÉK függvény az A1-ben lévő értéket számmá alakítja. De a második argumentum, a „#” az értéket visszakonvertálja szöveges formátumba minden további nullák nélkül. Ennek eredményeként a számokat kezdő nullák nélkül kapja meg, de továbbra is szöveges formátumban (balra igazítva).

Távolítsa el a vezető nullákat az Excel Szöveg oszlopok funkciójával

A kezdő nullák eltávolításának egy másik módja az Excel Szöveg oszlopok funkciójának használata.

Válassza ki a cellák azon tartományát, amelyekben a számok kezdő nullákkal vannak ellátva.

Ezután lépjen az „Adatok” fülre, és kattintson a „Szöveg az oszlopokba” gombra az Adateszközök csoportban.

Megjelenik a „Szöveg konvertálása oszlopokká” varázsló. A 3/1. lépésben válassza a „Határozott” lehetőséget, és kattintson a „Tovább” gombra.

A 3/2. lépésben törölje az összes határoló jelét, és kattintson a „Tovább” gombra.

Az utolsó lépésben hagyja meg az Oszlop adatformátum opciót az „Általános” értékben, és válassza ki azt a célhelyet (a tartomány első celláját), ahová a számokat nullák nélkül szeretné megadni. Ezután kattintson a „Befejezés” gombra

És a számokat a kezdőbetűkkel együtt egy külön oszlopban kapja meg, az alábbiak szerint.

Ez minden.