✅ Excel'de Bir Dizeden Sayıları Çıkarma (Formüller veya VBA Kullanarak)

İçindekiler

Sayıları Excel'de Dizeden Çıkarma (Excel 2016 Formülü)
Sayıları Excel'de Dizeden Çıkarma (Excel 2013/2010/2007 için)
VBA Kullanarak Excel'de Ayrı Metin ve Sayılar

Videoyu İzle - Excel'de Metin Dizesinden Sayılar Nasıl Çıkarılır (Formül ve VBA Kullanarak)

Excel'de bir hücredeki bir dizeden sayıları çıkarmak için yerleşik bir işlev yoktur (veya tam tersi - sayısal kısmı kaldırın ve metin bölümünü bir alfasayısal dizeden çıkarın).

Ancak bu, bir Excel işlevleri kokteyli veya bazı basit VBA kodları kullanılarak yapılabilir.

Önce size neden bahsettiğimi göstereyim.

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve dizeden sayıları çıkarmak istediğinizi varsayalım (aşağıda gösterildiği gibi):

Seçtiğiniz yöntem, kullandığınız Excel sürümüne de bağlı olacaktır:

Excel 2016'dan önceki sürümler için biraz daha uzun formüller kullanmanız gerekir.
Excel 2016 için, yeni tanıtılan TEXTJOIN işlevini kullanabilirsiniz.
VBA yöntemi Excel'in tüm sürümlerinde kullanılabilir

Örnek dosyayı indirmek için buraya tıklayın

Sayıları Excel'de Dizeden Çıkarma (Excel 2016 Formülü)

Bu formül, yeni tanıtılan TEXTJOIN işlevini kullandığından yalnızca Excel 2016'da çalışır.

Ayrıca bu formül, metin dizesinin başındaki, sonundaki veya ortasındaki sayıları çıkarabilir.

Bu bölümde ele alınan TEXTJOIN formülünün size tüm sayısal karakterleri bir arada vereceğini unutmayın. Örneğin metin “10 biletin fiyatı 200 USD” ise sonuç olarak size 10200 verecektir.

Aşağıda gösterildiği gibi veri kümeniz olduğunu ve her hücredeki dizelerden sayıları çıkarmak istediğinizi varsayalım:

Aşağıda, Excel'deki bir dizeden size sayısal kısım verecek formül bulunmaktadır.

=TEXTJOIN("",DOĞRU,EĞERHATA((ORTA(A2,SATIR(DOLAYLI("1:"&UZUNLUK(A2)))1)*1),""))

Bu bir dizi formülüdür, bu nedenle 'Kontrol + Üst Karakter + Enter' Enter kullanmak yerine.

Metin dizesinde sayı olmaması durumunda, bu formül bir boş (boş dize) döndürür.

Bu formül nasıl çalışır?

Bu formülü kırıp nasıl çalıştığını açıklamaya çalışayım:

SATIR(DOLAYLI(“1:”&UZUNLUK(A2))) - formülün bu kısmı birden başlayarak bir dizi sayı verir. Formüldeki UZUNLUK işlevi, dizedeki toplam karakter sayısını döndürür. “Maliyet 100 USD” olması durumunda, 19 döndürür. Formüller böylece SATIR(DOLAYLI(“1:19”) olur.SIRA işlevi daha sonra bir dizi sayı döndürür - {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
(ORTA(A2,SATIR(DOLAYLI(“1:”&UZUNLUK(A2)))1)*1) - Formülün bu kısmı bir #DEĞER dizisi döndürür! dizeye dayalı hatalar veya sayılar. Dizedeki tüm metin karakterleri #DEĞER olur! hatalar ve tüm sayısal değerler olduğu gibi kalır. Bu, MID fonksiyonunu 1 ile çarptığımız için olur.
EĞERHATA((ORTA(A2,SATIR(DOLAYLI(“1:”&UZUNLUK(A2))),1)*1),””) - EĞERHATA işlevi kullanıldığında tüm #DEĞER! hatalar ve sadece sayılar kalır. Bu bölümün çıktısı şu şekilde olacaktır - {“”;””;””;””;””;””;””;””;””;””;””;””;””; ””;””;””;1;0;0}
=TEXTJOIN(“”,TRUE,EĞERHATA((ORTA(A2,SATIR(“1:”&UZUNLUK(A2))),1)*1),””)) - METİN BİRLEŞTİR işlevi artık dize karakterlerini birleştirir kalan (yalnızca sayılardır) ve boş dizeyi yok sayar.

Profesyonel İpucu: Formülün bir bölümünün çıktısını kontrol etmek istiyorsanız, hücreyi seçin, düzenleme moduna geçmek için F2 tuşuna basın, çıktısını almak istediğiniz formülün bölümünü seçin ve F9 tuşuna basın. Sonucu anında göreceksiniz. Ardından Control + Z tuşlarına basmayı veya Escape tuşuna basmayı unutmayın. Enter tuşuna BASMAYIN.

Örnek Dosyayı İndirin

Aynı mantığı, alfasayısal bir dizeden metin bölümünü çıkarmak için de kullanabilirsiniz. Dizeden metin kısmını alacak formül aşağıdadır:

=TEXTJOIN("",TRUE,IF(ISERROR(ORTA(A2,SATIR(DOLAYLI("1:"&UZUNLUK(A2))),1)*1),ORTA(A2,SATIR(DOLAYLI("1:"&UZUNLUK) (A2)),1)),""))

Bu formüldeki küçük bir değişiklik, MID işlevinden aldığımız dizinin hatalı olup olmadığını kontrol etmek için EĞER işlevinin kullanılmasıdır. Eğer bir hata ise değeri tutar, bunun yerine bir boşluk bırakır.

Daha sonra tüm metin karakterlerini birleştirmek için TEXTJOIN kullanılır.

Dikkat: Bu formül harika çalışırken, geçici bir işlev (DOLAYLI işlev) kullanır. Bu, bunu çok büyük bir veri kümesiyle kullanmanız durumunda, sonuçları size vermenin biraz zaman alabileceği anlamına gelir. Bu formülü Excel'de kullanmadan önce bir yedek oluşturmak en iyisidir.

Sayıları Excel'de Dizeden Çıkarma (Excel 2013/2010/2007 için)

Excel 2013. 2010. veya 2007'ye sahipseniz, TEXTJOIN formülünü kullanamazsınız, bu yüzden bunu yapmak için karmaşık bir formül kullanmanız gerekecektir.

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve her hücredeki dizedeki tüm sayıları çıkarmak istediğinizi varsayalım.

Aşağıdaki formül bunu yapacaktır:

=EĞER(TOPLA(UZUNLUK(A2)-UZUNLUK(YEDEK(A2, {"0","1","2","3","4","5","6","7")), 8","9"}, "")))>0, TOPLA(ORTA(0&A2, BÜYÜK(INDEX(ESNUMARA(--ORTA(A2,SATIR(DOLAY("$1:$"&UZUNLUK(A2)))), 1))* SATIR(DOLAYLI("$1:$"&UZUNLUK(A2)),0), SATIR(DOLAYLI("$1:$"&UZUNLUK(A2))))+1,1) * 10^SATIR(DOLAYLI ("$1:$"&LEN(A2)))/10),"")

Metin dizesinde sayı olmaması durumunda, bu formül boş (boş dize) döndürür.

Bu bir dizi formülü olmasına rağmen, gerek yok Bunu kullanmak için 'Control-Shift-Enter' kullanmak için. Bu formül için basit bir giriş işe yarar.

Bu formülün kredisi harika Bay Excel forumuna gidiyor.

Yine, bu formül, konum ne olursa olsun dizedeki tüm sayıları çıkaracaktır. Örneğin, metin “10 biletin fiyatı 200 USD” ise, sonuç olarak size 10200 verecektir.

VBA Kullanarak Excel'de Ayrı Metin ve Sayılar

Metin ve sayıları ayırmak (veya metinden sayıları çıkarmak) sık sık yapmanız gereken bir şeyse, VBA yöntemini de kullanabilirsiniz.

Yapmanız gereken tek şey, Excel'de özel bir Kullanıcı Tanımlı İşlev (UDF) oluşturmak için basit bir VBA kodu kullanmak ve ardından uzun ve karmaşık formüller kullanmak yerine bu VBA formülünü kullanmaktır.

Size VBA'da iki formülün nasıl oluşturulacağını göstereyim - biri sayıları çıkarmak için, diğeri bir dizeden metin çıkarmak için.

Sayıları Excel'de Dizeden Çıkarma (VBA kullanarak)

Bu bölümde, bir dizgeden sadece sayısal kısmı almak için özel fonksiyonun nasıl oluşturulacağını göstereceğim.

Bu özel işlevi oluşturmak için kullanacağımız VBA kodu aşağıdadır:

İşlev GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Sonraki i GetNumeric = Sonuç Bitiş İşlevi

Bu işlevi oluşturma ve ardından çalışma sayfasında kullanma adımları şunlardır:

Geliştirici sekmesine gidin.
Visual Basic'e tıklayın (ALT + F11 klavye kısayolunu da kullanabilirsiniz)
Açılan VB Düzenleyici arka ucunda, çalışma kitabı nesnelerinden herhangi birine sağ tıklayın.
Ekle'ye gidin ve Modül'e tıklayın. Bu, çalışma kitabı için modül nesnesini ekleyecektir.
Modül kodu penceresinde, yukarıda belirtilen VBA kodunu kopyalayıp yapıştırın.
VB Düzenleyiciyi kapatın.

Artık çalışma sayfasında GetText işlevini kullanabileceksiniz. Tüm ağır işleri kodun kendisinde yaptığımız için tek yapmanız gereken =GetNumeric(A2) formülünü kullanmak.

Bu size anında dizenin yalnızca sayısal kısmını verecektir.

Çalışma kitabında artık VBA kodu bulunduğundan, onu .xls veya .xlsm uzantısıyla kaydetmeniz gerektiğini unutmayın.

Örnek Dosyayı İndirin

Bu formülü sık sık kullanmak zorunda kalırsanız, Kişisel Makro Çalışma Kitabınıza da kaydedebilirsiniz. Bu, bu özel formülü birlikte çalıştığınız Excel çalışma kitaplarından herhangi birinde kullanmanıza olanak tanır.

Excel'de bir Dizeden Metin Çıkarma (VBA kullanarak)

Bu bölümde, bir dizeden yalnızca metin kısmını almak için özel işlevin nasıl oluşturulacağını göstereceğim.

Bu özel işlevi oluşturmak için kullanacağımız VBA kodu aşağıdadır:

İşlev GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Sonraki i GetText = Sonuç Bitiş Fonksiyonu

Bu işlevi oluşturma ve ardından çalışma sayfasında kullanma adımları şunlardır:

Geliştirici sekmesine gidin.
Visual Basic'e tıklayın (ALT + F11 klavye kısayolunu da kullanabilirsiniz)
Açılan VB Düzenleyici arka ucunda, çalışma kitabı nesnelerinden herhangi birine sağ tıklayın.
Ekle'ye gidin ve Modül'e tıklayın. Bu, çalışma kitabı için modül nesnesini ekleyecektir.
- Zaten bir modülünüz varsa, üzerine çift tıklayın (zaten varsa yenisini eklemenize gerek yok).
Modül kodu penceresinde, yukarıda belirtilen VBA kodunu kopyalayıp yapıştırın.
VB Düzenleyiciyi kapatın.

Artık çalışma sayfasında GetNumeric işlevini kullanabileceksiniz. Tüm ağır işleri kodun kendisinde yaptığımız için tek yapmanız gereken =GetText(A2) formülünü kullanmak.

Bu size anında dizenin yalnızca sayısal kısmını verecektir.