Excel'de bir hücredeki bir dizeden sayıları çıkarmak için yerleşik bir işlev yoktur (ve 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şlevi veya bazı basit VBA kodları kullanılarak yapılabilir.
Önce neden bahsettiğimi anlatayım.
Aşağıdaki gibi bir veri kümeniz olduğunu ve dizelerden sayıları çıkarmak istediğinizi varsayalım (aşağıdaki gibi):
Seçtiğiniz yöntem, kullandığınız Excel sürümüne de bağlıdır:
- Excel'in 2016'dan önceki sürümleri için biraz daha uzun bir formül kullanmanız gerekir.
- Excel 2016 için yeni tanıtılan TEXTJOIN işlevini kullanabilirsiniz.
- Excel'in tüm sürümleri için kullanılabilir VBA yöntemi
İçerik
Excel'deki dizelerden sayıları ayıklayın (Excel 2016 formülleri)
Bu formül, yeni tanıtılan TEXTJOIN işlevini kullandığından yalnızca Excel 2016'da çalışır.
Ek olarak, bu formül bir metin dizesinin başındaki, sonundaki veya ortasındaki sayıları çıkarabilir.
Bu bölümde açıklanan TEXTJOIN formülünün tüm sayısal karakterleri bir araya getirdiğini unutmayın.Örneğin, metin "10 bilet 200 TL'dir" ise sonuç 10200'dür.
Aşağıdaki gibi bir veri kümeniz olduğunu ve her hücredeki dizelerden sayıları çıkarmak istediğinizi varsayalım:
Aşağıdaki formül size Excel'deki bir dizenin sayısal bölümünü verecektir.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Bu bir dizi formülüdür, bu yüzden " Kontrol + Üst Karakter + Enter " Enter'ı kullanmak yerine.
Metin dizesinde sayı yoksa bu formül boş (boş dize) döndürür.
Bu formül nasıl çalışır?
Bu formülü parçalayayım ve 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ı verecektir.Formüldeki UZUNLUK işlevi, dizedeki toplam karakter sayısını döndürür.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {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!Dize tabanlı hatalar veya sayılar.Dizedeki tüm metin karakterleri #DEĞER olur!Hatalar ve tüm değerler olduğu gibi kalır.Bu, MID işlevini 1 ile çarptığımızda olur.
- EĞERHATA((ORTA(A2,SATIR(DOLAYLI(“1:”&UZUNLUK(A2)))),1)*1),””) – EĞERHATA işlevi kullanılırken, tüm #DEĞER'ler kaldırılır!Hata, sadece sayılar kalacak.Bu bölümün çıktısı şu şekildedir - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2)),1)*1),"")) – TEXTJOIN işlevi artık sadece dize karakterlerini birleştirir Hala orada (yalnızca sayılar) ve boş dizeleri yok sayar.
Profesyonel ipucu:Formülün bir bölümünün çıktısını kontrol etmek istiyorsanız, hücreyi seçin, düzenleme moduna girmek için F2'ye basın, formülün çıktısını almak istediğiniz bölümünü seçin ve F9'a basın.Sonuçları hemen göreceksiniz.Ardından Control + Z tuşlarına veya Escape tuşuna basmayı unutmayın.Enter'a basmayın.
Aynı mantığı, alfasayısal dizelerden metnin bölümlerini çıkarmak için de kullanabilirsiniz.Dizeden metin bölümünü almak için formül:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Bu formülde yapılan küçük bir değişiklik, MID işlevinden aldığımız diziyi hatalar için kontrol etmek için EĞER işlevinin kullanılmasıdır.Bir hata ise, değeri koruyun, aksi takdirde boş ile değiştirin.
Ardından, tüm metin karakterlerini birleştirmek için TEXTJOIN'i kullanın.
注意: Bu formül geçerli olmasına rağmen, geçici bir işlev (DOLAYLI işlev) kullanır.Bu, onu çok büyük bir veri kümesiyle kullanıyorsanız, size sonuç vermenin biraz zaman alabileceği anlamına gelir.Bu formülü Excel'de kullanmadan önce bir yedek oluşturmak en iyisidir.
Excel'deki dizelerden sayıları ayıklayın (Excel 2013/2010/2007 için)
Excel 2013. 2010. veya 2007'niz varsa, TEXTJOIN formüllerini kullanamazsınız, bu yüzden bunu yapmak için karmaşık formüller kullanmanız gerekir.
Aşağıdaki gibi bir veri kümeniz olduğunu ve her hücredeki dizelerdeki tüm sayıları çıkarmak istediğinizi varsayalım.
Aşağıdaki formül bunu yapacaktır:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Metin dizesinde sayı yoksa bu formül boş (boş dize) döndürür.
Bu bir dizi formülü olmasına rağmen,Gerek yokKullanmak için "Control-Shift-Enter" kullanın.Bu formül için basit bir giriş çalışır.
Bu formül için kredi inanılmaz gidiyorBay Excel Forumu.
Yine, bu formül, konumdan bağımsız olarak dizedeki tüm sayıları çıkaracaktır.Örneğin, metin "10 bilet 200 TL'dir" ise sonuç 10200'dür.
注意: Bu formül geçerli olmasına rağmen, geçici bir işlev (DOLAYLI işlev) kullanır.Bu, onu çok büyük bir veri kümesiyle kullanıyorsanız, size sonuç vermenin biraz zaman alabileceği anlamına gelir.Bu formülü Excel'de kullanmadan önce bir yedek oluşturmak en iyisidir.
VBA ile Excel'de ayrı metin ve sayılar
Metin ve sayıları sık sık ayırmanız (veya metinden sayıları çıkarmanız) gerekiyorsa, VBA yöntemini de kullanabilirsiniz.
Tek yapmanız gereken, basit VBA kodunu kullanarak Excel'de özel bir kullanıcı tanımlı işlev (UDF) oluşturmak ve ardından uzun ve karmaşık bir formül 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ı ayıklamak, diğeri dizelerden metin çıkarmak için.
Excel'deki dizelerden sayıları ayıklayın (VBA kullanarak)
Bu bölümde, bir dizeden yalnızca sayısal kısmı almak için özel bir işlevin nasıl oluşturulacağını göstereceğim.
Bu özel işlevi oluşturmak için kullanacağımız VBA kodu:
İş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 bir çalışma sayfasında kullanma adımları şunlardır:
Artık çalışma sayfasında GetText işlevini kullanabileceksiniz.Kodun kendisinde zaten tüm ağır işleri yaptığımız için, tek yapmanız gereken =GetNumeric(A2) formülünü kullanmak.
Bu size hemen dizenin sayısal kısmını verecektir.
Çalışma kitabı artık VBA kodu içerdiğinden, onu bir .xls veya .xlsm uzantısıyla kaydetmeniz gerekeceğini unutmayın.
Bu formülü sık sık kullanmanız gerekiyorsa, kişisel makro çalışma kitabınıza da kaydedebilirsiniz.Bu, bu özel formülü kullandığınız herhangi bir Excel çalışma kitabında kullanmanıza olanak tanır.
Excel'deki dizeden metin ayıklayın (VBA kullanarak)
Bu bölümde, bir dizeden yalnızca metin bölümünü almak için özel bir işlevin nasıl oluşturulacağını göstereceğim.
Bu özel işlevi oluşturmak için kullanacağımız VBA kodu:
İş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 bir çalışma sayfasında kullanma adımları şunlardır:
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 hemen dizenin sayısal kısmını verecektir.
Çalışma kitabı artık VBA kodu içerdiğinden, onu bir .xls veya .xlsm uzantısıyla kaydetmeniz gerekeceğini unutmayın.
Bu formülü sık sık kullanmanız gerekiyorsa, kişisel makro çalışma kitabınıza da kaydedebilirsiniz.Bu, bu özel formülü kullandığınız herhangi bir Excel çalışma kitabında kullanmanıza olanak tanır.
