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 yeni oyuncaklarla, yani fonksiyonlarla oyunda.
Excel 2019 ve 365, sayısal karakterleri kaldırma ve yeni bir hücrede yalnızca sayısal olmayan değerleri alma görevini kolaylaştırabilen bazı yeni işlevler (TEXTJOIN ve SEQUENCE) sunar. Bunu yapmamıza yardımcı olabilecek formülleri daha rahat kullanacağız.
Genel Formül
| =METİN BİRLEŞTİRME("",NS,EĞER(ISERROR(ORTA(karışık_metin,SIRA(NumChars),1)+0),ORTA(karışık_metin,SIRA(NumChars),1)),")))) |
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 Karakterleri Kaldırın ve Tüm Alfabeleri ve Sayısal Olmayan Karakterleri Çıkarın.
Yani burada bazı alfanümerik metinlerimiz var. Bu metin bazı sayılar ve bazı sayısal olmayan karakterler içermektedir. D sütununda sayısal karakterlerden kurtulmam ve yalnızca alfabe ve diğer karakter değerlerini almam gerekiyor.
Karışık metindeki toplam karakter sayısının 100'den fazla olmasını beklemiyorum. Yani burada NumChars değeri 100'dür. Gerekirse bu sayıyı artırabilir veya azaltabilirsiniz.
Sayısal karakterleri çıkarmak için yukarıdaki genel formülü buraya uygulayın.
| =METİN BİRLEŞTİRME("",NS,EĞER(ISERROR(ORTA(B3,SIRA(100),1)+0),ORTA(B3,SIRA(100),1),""))) |
Giriş düğmesine bastığınızda, tüm sayısal karakterleri kaldırmış olursunuz ve yalnızca alfabetik değerler kalır. B sütunundaki tüm hücrelerden dizedeki sayıları 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ĞER(ISERROR(ORTA(B3,SIRA(100),1)+0),ORTA(B3,SIRA(100),1),""))) |
| 2-> METİN BİRLEŞTİRME("",NS,EĞER(ISERROR(ORTA("Bu1… site",{1,2,3,… 100},1)+0),ORTA(B3,SIRA(100),1),""))) |
| 3-> METİN BİRLEŞTİRME("",NS,EĞER(ISERROR({"T";"h";"i";"s";" ","1"… "";""}+0),ORTA(B3,SIRA(100),1),""))) |
| 4-> METİN BİRLEŞTİRME("",NS,EĞER(ISERROR({#DEĞER!;#DEĞER!;#DEĞER!;#DEĞER!;1…;#DEĞER!}),ORTA(B3,SIRA(100),1),""))) |
| 5-> METİN BİRLEŞTİRME("",NS,EĞER({DOĞRU;DOĞRU;DOĞRU;DOĞRU;YANLIŞ… ;DOĞRU},ORTA(B3,SIRA(100),1),""))) |
| 6-> METİN BİRLEŞTİRME("",NS,{"Bu";""… .;""}) |
| 7-> "Bu bir numaralı web sitesi" |
Okuma kolaylığı için tüm diziyi yazmadım. Uzun dizileri belirtmek için noktalar (…) kullandım.
Gördüğünüz gibi, formül içeriden çözülmeye başlıyor. İlk başta SEQUENCE işlevi çözülür. Karakter sayısı olarak 100'ü geçtiğimizden beri. 1'den 100'e 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 bir dizideki her karakteri böler. Bunu 3. adımda görebilirsiniz. Çok fazla yer kaplayacağı için tüm diziyi göstermedim. {"T";"h";"i";"s";" ","1"… "";""}
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. {#DEĞER!;#DEĞER!;#DEĞER!;#DEĞER!;1…;#DEĞER!}
Bu diziye sunulur ISERROR işlev. Bildiğiniz gibi, ISERROR işlevi hatalar için TRUE, hata olmayan değerler için FALSE döndürür. Dolayısıyla bir DOĞRU ve YANLIŞ dizisi elde ederiz. Sayısal olmayan karakterler için TRUE ve sayılar için FALSE. {DOĞRU;DOĞRU;DOĞRU;DOĞRU;YANLIŞ…;DOĞRU}.
Aynı şey IF'nin DOĞRU bölüm ifadelerine de olur (ORTA(B3,SIRA(100),1)). B3'teki alfasayısal dizenin tüm karakterlerinden oluşan bir dizi döndürür.
Şimdi, her TRUE ifadesi için, EĞER işlevi, doğru bölümün dizisinden karşılık gelen karakteri döndürür. YANLIŞ için EĞER boş ("") döndürür. Artık herhangi bir sayısal karakter içermeyen bir diziniz olacak. {"Bu";""… .;""}.
Son olarak, bu dizi METİN BİRLEŞTİRME işlev. Bu işlev, verilen metinleri boş değerleri yok sayarak verilen bir sınırlayıcı ile birbiriyle birleştirir. Bu nedenle, herhangi bir sayısal karakter içermeyen bir dize elde ederiz. Bu bir numaralı web sitesi.
Formülü Geliştirmek
Yukarıdaki formül, karakter sayısını işlemek için sabit kodlanmış bir sayı kullanır (100 aldık). Ama dinamik olmasını isteyebilirsiniz. Bu durumda, doğru tahmin ettiniz, LEN işlevini kullanabilirsiniz. İşleme için tam sayıda karakter alacaktır. Yani formül olacak.
| =METİN BİRLEŞTİR("",DOĞRU,EĞER(ISERROR(ORTA(karma_metin,SEQUENCE(UZUNLUK(karışık_metin),1)+0),ORTA(karmaşık_metin,SEQUENCE(UZUNLUK(karışık_metin),1),""))) |
Burada, UZUNLUK işlevi, alfasayısal dizedeki tam sayı karakterlerini otomatik olarak algılayacaktır. Bu, maksimum karakter sayısını belirleme yükünden kurtulacaktır.
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ĞER(ISERROR(ORTA(karmaşık_metin,SIRA(DOLAYLI("1:"&UZUNLUK(Sayı Karakterleri))),1)+0),ORTA(karmaşık_metin,SIRA(DOLAYLI("1:"&UZUNLUK(Sayı Karakterleri))),1),""))) |
DOLAYLI, metni ("1:100") gerçek aralığa dönüştürür ve ardından SATIR işlevi, 1'den 100'e kadar tüm satır numaralarını listeler. (100 sadece bir ö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 2019'da Hücrelerden Sayısal Olmayan Karakterler Nasıl Kaldırılır: Excel'deki alfasayısal bir dizeden sayısal olmayan karakterleri kaldırmak için yeni TEXTJOIN işlevini kullanıyoruz. Bir dizedeki sayısal olmayan karakterlerden oluşan şerit, daha iyi veri analizi için verilerimizi temizlememize yardımcı olabilir. İşte bunu nasıl yapacağınız
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.