Excel 2016 ve daha eski sürümlerde bir hücreden sayısal değerlerin nasıl çıkarılacağını öğrendik. Kullandığımız formüller biraz karmaşıktı ama artık Excel 2019 ve 365 oyunda.
Excel 2019 ve 365, sayısal olmayan karakterleri kaldırma ve yeni bir hücrede yalnızca sayısal değerleri alma görevini kolaylaştıran bazı yeni işlevler sunar. Bunu yapmamıza yardımcı olabilecek formülleri daha rahat kullanacağız.
Genel Formül
| =METİN BİRLEŞTİRME("",NS,EĞERHATA(ORTA(karışık_metin,SIRA(NumChars),1)+0,"")) |
karışık_metin: Bu, tüm sayısal değerleri çıkarmak istediğiniz kaynak metindir.
Sayı Karakterleri: Bu, işlemek istediğiniz toplam karakter sayısıdır. Karmaşık_metin, bu sayıdan daha fazla karakter içermemelidir (karakterler ve sayısallar bir arada).
İşleri netleştirmek için bir örnek görelim.
Örnek: Sayısal Olmayan Karakterleri Kaldırın ve Tüm Sayıları Çıkarın
Yani burada bazı karışık metinlerimiz var. Bu metin bazı sayılar ve bazı sayısal olmayan karakterler içermektedir. Sayısal olmayan karakterlerden kurtulmam ve yalnızca D sütununda sayısal değerler almam gerekiyor.
Karışık metindeki toplam karakter sayısının 20'den fazla olmasını beklemiyorum. Yani burada NumChars değeri 20'dir. Gerekirse bu sayıyı artırabilirsiniz.
Sayısal olmayan karakterleri çıkarmak için yukarıdaki genel formülü buraya uygulayın.
| =METİN BİRLEŞTİRME("",NS,EĞERHATA(ORTA(C3,SIRA(20),1)+0,"")) |
Ve enter düğmesine bastığınızda. Sayısal olmayan tüm karakterleri kaldırmış olursunuz. C3 sütunundaki tüm hücrelerden dizedeki karakterleri kaldırmak için bu formülü aşağı sürükleyin.
O nasıl çalışır?
Öncelikle bu formülün adım adım nasıl çözüldüğünü görelim.
| 1-> METİN BİRLEŞTİR("",NS,EĞERHATA(ORTA(C3,SIRA(20),1)+0,"")) |
| 2-> METİN BİRLEŞTİRME("",NS,EĞERHATA(ORTA("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> METİN BİRLEŞTİRME("",NS,EĞERHATA({"1";"2";"a";"s";"w";"1";"2";"w";"1";"2";"3";"";"";"";"";"";"";"";"";""}+0,"")) |
| 4-> METİN BİRLEŞTİRME("",NS,EĞERHATA({1;2;#DEĞER!;#DEĞER!;#DEĞER!;1;2;#DEĞER!;1;2;3;#DEĞER!;#DEĞER!;… ;#DEĞER!}+0,"")) |
| 5-> METİN BİRLEŞTİRME("",NS,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Gördüğünüz gibi, formül içeriden çözülmeye başlıyor. İlk başta SEQUENCE işlevi çözülür. 20'yi geçtiğimizden beri. 1'den 20'ye kadar bir dizi sayı döndürür.
Bu dizi, MID İşlevine başlangıç numarası olarak sunulur. Mid işlevi, dizedeki her dizine gider ve her karakteri böler. Bunu 3. adımda görebilirsiniz.
Sonra her karaktere 0 ekliyoruz. Excel'de sayısal olmayan karakterlere sayı eklemeye çalışırsanız, #DEĞER! Hata. Böylece bir dizi sayı elde ederiz ve #DEĞER! Hatalar. Sayısal olmayan karakterler artık yok.
Sonraki EĞERHATA işlevi, tüm #DEĞER hatalarını "" (boş) ile değiştirir. Şimdi sayısal değerler ve boşluklar kaldı.
Son olarak, bu dizi TEXTJOIN İşlevine sunulur. TEXTJOIN işlevi onları birleştirir ve içinde yalnızca sayıları içeren bir dize elde ederiz.
Formülü Geliştirmek
Yukarıdaki formül, karakter sayısını işlemek için sabit kodlanmış bir sayı kullandı (yirmi aldık). Ama dinamik olmasını isteyebilirsiniz. Bu durumda doğru tahmin ettiniz, LEN işlevini kullanacağız. İşleme için tam sayıda karakter alacaktır. Yani formül olacak.
| =TEXTJOIN("",DOĞRU,EĞERHATA(ORTA(karmaşık_metin,SEQUENCE(UZUNLUK(karışık_metin)),1)+0,"")) |
Burada, UZUNLUK işlevi, alfasayısal dizedeki tam sayı karakterlerini otomatik olarak algılayacaktır. Bu, maksimum karakter sayısını belirleme yükünü hafifletecektir.
SEQUENCE işlevinin alternatifi
SIRA işlevini kullanmak istemiyorsanız, sıralı sayılar üretmek için SATIR ve DOLAYLI işlevinin bir kombinasyonunu kullanabilirsiniz.
| =METİN BİRLEŞTİR("",DOĞRU,EĞERHATA(ORTA(karmaşık_metin,SIRA(DOLAYLI("1:"&UZUNLUK(karışık_metin))),1)+0,"")) |
DOLAYLI, metni ("1:20") gerçek aralığa dönüştürür ve ardından SATIR işlevi, 1'den 20'ye kadar tüm satır numaralarını listeler. (20 sadece örnektir. Herhangi bir sayı olabilir).
Evet arkadaşlar, Excel'deki alfasayısal bir dizeden sayısal olmayan karakterleri bu şekilde koparabilirsiniz. Umarım yeterince açıklayıcı olabilmişimdir ve bu makale size yardımcı olmuştur. Bu konuyla veya başka bir Excel/VBA konusuyla ilgili herhangi bir sorunuz varsa. O zamana kadar Mükemmel olmaya devam edin.
Excel 2016 ve Daha Eski Sürümlerde Sayıları ve Metni Dizeden Böl: TEXTJOIN fonksiyonumuz olmadığında, sayısal ve sayısal olmayan karakterleri bir dizgeden ayırmak için diğer bazı fonksiyonlarla birlikte LEFT ve RIGHT fonksiyonlarını kullandık.
Excel'in SOL ve SAĞ İşlevini Kullanarak Excel'deki Bir Dizeden Metin Çıkarma: Excel'deki metni dizeden çıkarmak için excel'in SOL ve SAĞ işlevini kullanabiliriz. Bu işlevler, dizeleri dinamik olarak kesmemize yardımcı olur.
Excel'de metnin başındaki ve sonundaki boşlukları kaldırın: Öndeki ve sondaki boşlukları görsel olarak tanımak zordur ve verilerinizi karıştırabilir. Bu karakterleri dizeden çıkarmak, veri temizlemede temel ve en önemli görevdir. Excel'de kolayca nasıl yapabileceğiniz aşağıda açıklanmıştır.
Karakterleri Sağdan Kaldır: Excel'de bir dizenin sağındaki karakterleri kaldırmak için SOL işlevini kullanırız. Evet, SOL işlevi. SOL işlevi, SOL'dan verilen sayıda karakteri korur ve sağındaki her şeyi kaldırır.
Excel'de istenmeyen karakterleri kaldırın: Excel'deki bir dizeden istenmeyen karakterleri kaldırmak için YERİNEKEK işlevini kullanırız. SUBSTITUTE işlevi, verilen karakterleri verilen başka bir karakterle değiştirir ve değiştirilmiş yeni bir dize oluşturur.
Excel'de Bir Konumdan Başlayarak Excel'de Metin Nasıl Kaldırılır: Metni bir dizedeki başlangıç konumundan kaldırmak için Excel'in DEĞİŞTİR işlevini kullanırız. Bu işlev, başlangıç konumunu ve çıkarılacak karakter sayısını belirlememize yardımcı olur.
Popüler Makaleler:
Verimliliğinizi Artıracak 50 Excel Kısayolu | Görevinizde daha hızlı olun. Bu 50 kısayol, Excel'de daha da hızlı çalışmanızı sağlayacaktır.
Excel DÜŞEYARA İşlevi nasıl kullanılır?| Bu, farklı aralıklardan ve sayfalardan değer aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel nasıl kullanılır EĞERSAY İşlevi| Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli değerleri saymak için verilerinizi filtrelemeniz gerekmez. Gösterge tablonuzu hazırlamak için Countif işlevi gereklidir.
Excel'de SUMIF İşlevi Nasıl Kullanılır | Bu, başka bir gösterge panosu temel işlevidir. Bu, belirli koşullardaki değerleri özetlemenize yardımcı olur.