Bu öğretici, Excel'de baştaki sıfırları eklemenin veya tutmanın yanı sıra baştaki sıfırları kaldırmanın farklı yöntemlerini kapsar.
000652 gibi bir veya daha fazla sıfır içeren sayıları her girdiğinizde veya içe aktardığınızda, Excel bu sıfırları otomatik olarak kaldırır ve hücrelerde yalnızca sayının kendisi gösterilir (652). Bunun nedeni, baştaki sıfırların hesaplamalar için gerekli olmaması ve sayılmaz.
Ancak, kimlik numaralarını, telefon numaralarını, kredi kartı numaralarını, ürün kodlarını veya posta kodlarını vb. girerken olduğu gibi baştaki sıfırların gerekli olduğu zamanlar vardır. Neyse ki, Excel bize baştaki sıfırları eklemenin veya tutmanın birkaç yolunu sunar. hücrelerde. Bu yazıda, baştaki sıfırları eklemenin veya tutmanın ve baştaki sıfırları kaldırmanın farklı yollarını göstereceğiz.
Excel'de Baştaki Sıfırları Ekleme
Esasen, başına sıfır eklemek için kullanabileceğiniz 2 yöntem vardır: bir, numaranızı 'Metin' olarak biçimlendirin; iki, baştaki sıfırları eklemek için özel biçimlendirmeyi kullanın. Kullanmak istediğiniz yöntem, numara ile ne yapmak istediğinize bağlı olabilir.
Benzersiz kimlik numaraları, hesap numaraları, sosyal güvenlik numaraları veya posta kodları vb. girerken başına sıfır eklemek isteyebilirsiniz. Ancak, bu sayıları hesaplamalar veya işlevler için kullanmayacaksınız, bu nedenle bunları dönüştürmek en iyisidir. metin için sayılar. Telefon numaralarını veya hesap numaralarını asla toplamaz veya ortalama yapmazsınız.
Sayıları metin olarak biçimlendirerek sayıların önüne sıfır eklemenin veya doldurmanın birkaç yolu vardır:
- Hücre biçimini Metin olarak değiştirme
- Kesme işareti (') ekleme
- METİN işlevini kullanma
- REPT/LEN İşlevini Kullanma
- BİRLEŞTİR İşlevini/Ve işaretini işlecini (&) kullan
- SAĞ işlevini kullanma
Hücre Formatını Metne Değiştirme
Bu, sayılarınıza baştaki sıfırları eklemenin en basit yollarından biridir. Sadece sayı girecekseniz ve yazarken baştaki sıfırları tutmak istiyorsanız, bu yöntem tam size göre. Hücre biçimini Genel veya Sayı'dan Metin'e değiştirerek, Excel'in sayılarınızı metin değerleri olarak ele almaya zorlayabilirsiniz ve hücreye yazdığınız her şey tamamen aynı kalacaktır. İşte bunu nasıl yapacağınız:
Başına sıfır eklemek istediğiniz hücreyi/hücreleri seçin. "Ana Sayfa" sekmesine gidin, Sayılar grubundaki "Biçim" açılır kutusuna tıklayın ve biçim seçeneklerinden "Metin"i seçin.
Artık sayılarınızı yazdığınızda, Excel ondan baştaki sıfırları silmez.
Hücrenin sol üst köşesinde küçük bir yeşil üçgen (Hata göstergesi) görebilirsiniz ve o hücreyi seçtiğinizde, numarayı metin olarak kaydettiğinizi gösteren bir uyarı işareti gösterecektir.
Hata mesajını kaldırmak için hücreyi/hücreleri seçin, uyarı işaretine tıklayın ve ardından listeden 'Hatayı Yoksay'ı seçin.
Telefon numaralarını, sayıların arasına boşluk veya kısa çizgi koyarak da yazabilirsiniz; Excel, bu numaraları otomatik olarak metin olarak değerlendirecektir.
Öncü Kullanma kesme işareti ( ' )
Excel'de baştaki sıfırları eklemenin başka bir yolu, sayının başına kesme işareti (') eklemektir. Bu, Excel'i metin olarak bir sayı girmeye zorlar.
Herhangi bir sayıdan önce bir kesme işareti yazın ve 'Enter' tuşuna basın. Excel baştaki sıfırları olduğu gibi bırakacak, ancak hücreyi seçmediğiniz sürece (') çalışma sayfasında görünmeyecektir.
Metin İşlevini Kullanma
Yukarıdaki yöntem, siz yazarken sayılara sıfırlar ekler, ancak zaten bir sayı listeniz varsa ve onlardan önce gelen sıfırları doldurmak istiyorsanız, METİN işlevi sizin için doğru yöntemdir. METİN işlevi, özel biçimlendirme uygularken sayıları metin dizelerine dönüştürmenize olanak tanır.
METİN işlevinin Sözdizimi:
= METİN(değer, biçim_metin)Neresi,
- değer - Metne dönüştürmeniz ve biçimlendirme uygulamanız gereken sayısal değerdir.
- format_text – uygulamak istediğiniz formattır.
METİN işlevi ile numaranızın kaç haneli olması gerektiğini belirleyebilirsiniz. Örneğin, sayılarınızın 8 basamak uzunluğunda olmasını istiyorsanız, işlevin ikinci argümanına 8 sıfır yazın: “00000000”. Bir hücrede 6 basamaklı numaranız varsa, işlev manuel olarak 2 başa sıfır ekler ve 56 gibi 2 basamaklı sayılar varsa, kalanlar sıfır olur (00000056).
Örneğin, baştaki sıfırları eklemek ve sayıları 6 basamaklı yapmak için şu formülü kullanın:
=METİN(A2,"000000")Formülün ikinci argümanında 6 sıfıra sahip olduğumuz için, işlev sayı dizisini bir metin dizisine dönüştürür ve dizeyi 6 basamak uzunluğunda yapmak için önüne 5 sıfır ekler.
Not: Lütfen işlevde biçim kodlarını çift tırnak içine almayı unutmayın.
Şimdi, doldurma tutamacını sürükleyerek aynı formülü hücrelerin geri kalanına uygulayabilirsiniz. Gördüğünüz gibi, fonksiyon sayıları metinlere dönüştürür ve sayıların başına sıfırlar ekleyerek toplam basamak sayısı 6 olur.
METİN İşlevi her zaman bir sayı değil, bir metin dizesi olarak değer döndürür, bu nedenle bunları aritmetik hesaplamalarda kullanamayacaksınız, ancak bunları DÜŞEYARA veya İNDEKS/KAÇINCI gibi arama formüllerinde kullanarak bir Ürün Kimliklerini kullanan ürün.
BİRLEŞTİR İşlevini/Ve İşareti Operatörünü (&) Kullanma
Bir sütundaki tüm sayıların önüne sabit sayıda sıfır eklemek istiyorsanız, BİRLEŞTİR işlevini veya ve işareti operatörünü (&) kullanabilirsiniz.
BİRLEŞTİR işlevinin sözdizimi:
=BİRLEŞTİR(metin1, [metin2], ...)Neresi,
Metin 1 - Sayıdan önce eklenecek sıfır sayısı.
metin2 – Orijinal numara veya hücre başvurusu
Ve işareti operatörünün sözdizimi:
=Değer_1 ve Değer_2 Neresi,
Değer_1, sayıdan önce eklenecek baştaki sıfırlardır ve Değer_2 sayıdır.
Örneğin, bir sayıdan önce yalnızca iki sıfır eklemek için şu formüllerden birini kullanın:
=BİRLEŞTİR("00",A2)İlk argüman iki sıfırdır (“00”) çünkü A2'deki sayıdan önce iki sıfır doldurmak istiyoruz (ikinci argüman budur).
Veya,
="00"&A2Burada, ilk argüman 2 sıfır, ardından '&' operatörü ve ikinci argüman sayıdır.
Gördüğünüz gibi formül, sayının kaç basamak içerdiğinden bağımsız olarak bir sütundaki tüm sayılara yalnızca iki baştaki sıfır ekler.
Bu formüllerin her ikisi de orijinal sayılardan önce belirli sayıda sıfırı birleştirir ve bunları metin dizeleri olarak saklar.
REPT/LEN İşlevini Kullanma
Sayısal veya alfanümerik verilere baştaki sıfırlar eklemek ve dizeyi metne dönüştürmek istiyorsanız, REPT işlevini kullanın. REPT işlevi, bir karakteri/karakterleri belirli sayıda tekrarlamak için kullanılır. Bu işlev, sayıdan önce sabit sayıda baştaki sıfır eklemek için de kullanılabilir.
=REPT(metin, sayı_kez)Burada 'metin' tekrarlamak istediğimiz karakterdir (bizim durumumuzda '0') ve 'sayı_times' argümanı bu karakteri kaç kez tekrarlamak istediğimizdir.
Örneğin, sayılardan önce beş sıfır oluşturmak için formül şöyle görünür:
=TEKRAR(0,5)&A2Formülün yaptığı, 5 sıfırı tekrarlamak ve A2'deki sayı dizesini birleştirmek ve sonucu döndürmek. Ardından formül, doldurma tutamacı kullanılarak B2:B6 hücresine uygulanır.
Yukarıdaki formül, sayıdan önce sabit sayıda sıfır ekler, ancak sayının toplam uzunluğu sayıya bağlı olarak değişir.
Belirli bir karakter uzunluğunda (sabit uzunluklu) dizeler oluşturmak için gerektiğinde baştaki sıfırlar eklemek istiyorsanız, REPT ve UZUNLUK işlevlerini birlikte kullanabilirsiniz.
Sözdizimi:
=REPT(metin, sayı_times-UZUNLUK(metin))&hücreÖrneğin, A2'deki değere ön ekli sıfırlar eklemek ve 5 karakter uzunluğunda bir dize yapmak için şu formülü deneyin:
=REPT(0,5-LEN(A2))&A2Burada 'LEN(A2)', A2 hücresindeki dizenin/sayıların toplam uzunluğunu alır. '5', hücrenin sahip olması gereken maksimum dize/sayı uzunluğudur. Ve 'REPT(0,5-LEN(A2))' kısmı, A2'deki dizenin uzunluğunu maksimum sıfır sayısından (5) çıkararak sıfır sayısını ekler. Ardından, sabit uzunlukta bir dize oluşturmak için A2 değerinden önce bir dizi 0 birleştirilir.
SAĞ İşlevini Kullanma
Excel'de bir dizeden önce sıfırları doldurmanın başka bir yolu SAĞ işlevini kullanmaktır.
SAĞ işlevi, bir sayının başına bir dizi sıfır ekleyebilir ve değerden en sağdaki N karakteri çıkarabilir.
Sözdizimi:
= SAĞ (metin, num_chars)- Metin karakter çıkarmak istediğiniz hücre veya değerdir.
- num_chars metinden çıkarılacak karakter sayısıdır. Bu argüman verilmezse, yalnızca ilk karakter ayıklanır.
Bu yöntem için, 'metin' bağımsız değişkenindeki dizeyi içeren hücre referansıyla maksimum sıfır sayısını birleştiririz.
Başında sıfırlar bulunan A'daki sayı dizesini temel alan 6 basamaklı bir sayı oluşturmak için şu formülü deneyin:
=SAĞ("0000000"&A2,6)Formülün ilk argümanı (metni), A2'deki ("0000000"&A2) değere 7 sıfır ekler ve ardından en sağdaki 7 karakteri döndürür, bu da bazı baştaki sıfırlarla sonuçlanır.
Özel Sayı Biçimlendirmesini Kullanarak Baştaki Sıfırları Ekleme
Sayıların önüne sıfır koymak için yukarıdaki yöntemlerden herhangi birini kullanırsanız, sayı değil her zaman bir metin dizesi alırsınız. Ve hesaplamalarda veya sayısal formüllerde fazla kullanılmayacaklar.
Excel'de baştaki sıfırları eklemenin en iyi yolu, özel bir sayı biçimlendirmesi uygulamaktır. Hücreye özel bir sayı biçimi ekleyerek baştaki sıfırları eklerseniz, hücrenin değeri değişmez, yalnızca görüntülenme şekli değişir. Değer, metin olarak değil, sayı olarak kalacaktır.
Hücrelerin sayı biçimlendirmesini değiştirmek için şu adımları izleyin:
Baştaki sıfırları göstermek istediğiniz hücreyi veya hücre aralığını seçin. Ardından, seçilen aralıkta herhangi bir yere sağ tıklayın ve içerik menüsünden 'Hücreleri Biçimlendir' seçeneğini seçin. Veya Ctrl + 1 kısayol tuşlarına basın.
Hücreleri Biçimlendir penceresinde, "Sayı" sekmesine gidin ve Kategori seçenekleri altında "Özel"i seçin.
Bir hücrede göstermek istediğiniz toplam basamak sayısını belirtmek için "Tür:" kutusuna sıfır sayısını girin. Örneğin, sayının 6 basamaklı olmasını istiyorsanız, özel biçim kodu olarak '000000' girin. Ardından, uygulamak için 'Tamam'ı tıklayın.
Bu, sayılardan önce baştaki sıfırları gösterecek ve sayı 6 basamaktan küçükse, önüne sıfır dolduracaktır.
Temel değer değişmeden kalırken, sayılar yalnızca baştaki sıfırlara sahip görünecek. Özel biçimlendirmeye sahip bir hücre seçerseniz, formül çubuğunda size orijinal sayıyı gösterecektir.
Özel sayı biçiminizde kullanabileceğiniz birçok dijital yer tutucu vardır. Ancak sayılarda baştaki sıfırları eklemek için kullanabileceğiniz yalnızca iki birincil yer tutucu vardır.
- 0 – Fazladan sıfırları gösteren rakam yer tutucusudur. Basamağın değerle ilgili olup olmadığına bakılmaksızın 0-9 zorunlu basamakları görüntüler. Örneğin, 000.00 format koduyla 2.5 yazarsanız, 002.50 görüntülenecektir.
- # – İsteğe bağlı rakamları görüntüleyen ve fazladan sıfır içermeyen rakam yer tutucusudur. Örneğin, 000# biçimlendirme koduyla 123 yazarsanız, 0123 görüntülenecektir.
Ayrıca, biçim koduna eklediğiniz herhangi bir noktalama işareti veya diğer karakterler olduğu gibi görüntülenecektir. Kısa çizgi (-), virgül (,), eğik çizgi (/) vb. karakterleri kullanabilirsiniz.
Örneğin, özel biçimi kullanarak sayıları telefon numarası olarak da biçimlendirebilirsiniz.
Hücreleri Biçimlendir iletişim kutusundaki Kodu Biçimlendir:
Sonuç:
Bu biçimlendirme kodunu aşağıdaki örnekte uygulayalım:
##0000
Gördüğünüz gibi '0' fazladan sıfır eklerken '#' önemsiz sıfırlar eklemez:
Posta kodları, telefon numaraları ve sosyal güvenlik numaraları için Hücreleri biçimlendir iletişim kutusunun "Özel biçimler" bölümünde önceden tanımlanmış biçim kodlarını da kullanabilirsiniz.
Aşağıdaki tablo, farklı sütunlara farklı "Özel" biçim kodlarının uygulandığı, başında sıfır olan sayıları göstermektedir:
Excel'de Baştaki Sıfırları Kaldırma
Artık Excel'de baştaki sıfırları nasıl ekleyeceğinizi öğrendiniz, şimdi dize sayısından baştaki sıfırları nasıl çıkaracağınızı görelim. Bazen, harici bir kaynaktan veri içe aktardığınızda, sayıların ön eki sıfır olabilir ve metin olarak biçimlendirilebilir. Bu gibi durumlarda, formüllerde kullanabilmeniz için baştaki sıfırları kaldırıp tekrar sayılara dönüştürmeniz gerekir.
Excel'de baştaki sıfırları kaldırmanın çeşitli yolları vardır ve bunları tek tek göreceğiz.
Hücre Biçimlendirmesini Değiştirerek Baştaki Sıfırları Kaldırın
Baştaki sıfırlar özel sayı biçimlendirmesi ile eklendiyse, hücrelerin biçimini değiştirerek bunları kolayca kaldırabilirsiniz. Adres çubuğuna bakarak hücrelerinizin özel biçimlendirilip biçimlendirilmediğini anlayabilirsiniz (sıfırlar adres çubuğunda değil hücrede görünür).
Ön ekli sıfırları kaldırmak için başında sıfır olan hücreleri seçin, "Sayı Biçimi" kutusunu tıklayın ve "Genel" veya "Sayı" biçimlendirme seçeneğini seçin.
Şimdi, baştaki sıfırlar gitti:
Metni Sayılara Dönüştürerek Baştaki Sıfırları Silin
Baştaki sıfırlarınız hücre biçimini değiştirerek veya sayıların önüne kesme işaretleri ekleyerek veya verileri içe aktarırken otomatik olarak eklendiyse, bunları sayılara dönüştürmenin en kolay yolu Hata Denetimi Seçeneğini kullanmaktır. İşte bunu nasıl yapacağınız:
Sayılarınız sola hizalıysa ve hücrelerinizin sol üst köşesinde küçük bir yeşil üçgen (Hata göstergesi) varsa bu yöntemi kullanabilirsiniz. Bu, sayıların metin olarak biçimlendirildiği anlamına gelir.
Bu hücreleri seçin ve seçimin sağ üst kısmında sarı bir uyarı göreceksiniz. Ardından, açılır menüden 'Sayıya Dönüştür' seçeneğini tıklayın.
Sıfırlarınız kaldırılacak ve sayılar tekrar sayı formatına (Sağa hizalı) dönüştürülecektir.
Baştaki Sıfırları Kaldırarak 1 ile Çarpma/Bölme
Satır satırını kaldırmanın bir başka kolay ve en iyi yolu da sayıları 1 ile çarpmak veya bölmektir. Değeri bölmek veya çarpmak değeri değiştirmez, sadece değeri tekrar bir sayıya dönüştürür ve baştaki sıfırları kaldırır.
Bunu yapmak için aşağıdaki örnekteki formülü bir hücreye yazın ve ENTER'a basın. Baştaki sıfırlar kaldırılacak ve dize tekrar bir sayıya dönüştürülecektir.
Ardından, doldurma tutamacını kullanarak bu formülü diğer hücrelere uygulayın.
Aynı sonucu 'Özel Yapıştır' Komutunu kullanarak da elde edebilirsiniz. İşte nasıl:
Bir hücreye '1' sayısal değeri yazın (B2'de diyelim) ve bu değeri kopyalayın.
Ardından, baştaki sıfırları kaldırmak istediğiniz hücreleri seçin. Ardından, seçime sağ tıklayın ve ardından 'Özel Yapıştır' seçeneğini seçin.
Özel Yapıştır iletişim kutusunda, İşlem altında, 'Çarp' veya 'Böl' seçeneğini seçin ve 'Tamam'ı tıklayın.
İşte bu kadar, baştaki sıfırlarınız dizileri sayı olarak bırakarak kaldırılacak.
Bir Formül kullanarak Baştaki Sıfırları Kaldırın
Önekli sıfırları silmenin başka bir kolay yolu DEĞER işlevini kullanmaktır. Bu yöntem, baştaki sıfırlarınız başka bir formül veya kesme işareti kullanılarak veya özel biçimlendirme kullanılarak eklenmiş olsa da yararlı olabilir.
=DEĞER(A1)Formülün bağımsız değişkeni, bir değer veya değere sahip hücre başvurusu olabilir. Formül, baştaki sıfırları kaldırır ve değeri metinden sayıya dönüştürür. Ardından formülü hücrelerin geri kalanına uygulayın.
Bazen baştaki sıfırları kaldırmak, ancak sayıları metin biçiminde tutmak isteyebilirsiniz. Bu gibi durumlarda, METİN() ve DEĞER() işlevlerini şu şekilde birlikte kullanmanız gerekir:
=METİN(DEĞER(A1),"#")DEĞER işlevi, A1'deki değeri sayıya dönüştürür. Ancak ikinci argüman olan '#', değeri fazladan sıfır olmadan tekrar metin biçimine dönüştürür. Sonuç olarak, sayıları başında sıfır olmadan ancak yine de metin biçiminde (sola hizalı) alırsınız.
Excel'in Metni Sütunlara Özelliğini kullanarak Baştaki Sıfırları Kaldırın
Baştaki sıfırları kaldırmanın başka bir yolu da Excel'in Metni Sütunlara Dönüştür özelliğini kullanmaktır.
Başında sıfır olan sayıların bulunduğu hücre aralığını seçin.
Ardından, "Veri" sekmesine gidin ve Veri Araçları grubundaki "Metni Sütunlara Dönüştür" düğmesini tıklayın.
'Metni Sütunlara Dönüştür' sihirbazı görünecektir. Adım 1/3'te 'Sınırlandırılmış'ı seçin ve 'İleri'yi tıklayın.
Adım 2/3'te tüm sınırlayıcıların işaretini kaldırın ve 'İleri'yi tıklayın.
Son adımda, Sütun veri biçimi seçeneğini 'Genel' olarak bırakın ve sayılarınızı istediğiniz hedefi (aralığın ilk hücresi) başında sıfır olmadan seçin. Ardından, 'Son'u tıklayın
Ve aşağıda gösterildiği gibi ayrı bir sütunda baştaki kaldırılmış sayıları alacaksınız.
Hepsi bu.