Metinden (dize) yalnızca sayıları çıkarın
Excel’de, metin ve sayıların birleştirilmiş olduğu bir değerden sayıyı çıkarmak için formülleri kullanabilirsiniz. Aşağıdaki örnekte “TPS1984” değerinden 1984’ü elde etmek için bir formül kullandık.
Metinden sayıları çıkarmak için formül
=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))Bu derste bu formülü ayrıntılı olarak anlayacağız. Ve bunu anlamak için onu birkaç parçaya bölmeliyiz:
- Öncelikle değerdeki karakter sayısını almak için LEN fonksiyonumuz var.
- Bundan sonra INDIRECT, 1 ve 7’yi (LEN tarafından döndürülen) kullanarak bir hücre referansı kullanır.
- Daha sonra ROW işlevi INDIRECT’i kullanacak ve 1 ile başlayıp 7 ile biten bir sayı dizisi döndürecektir.
- Artık MID bu diziyi kullanacak ve bahsettiğiniz hücrenin değerleriyle yeni bir dizi döndürecek.
- Daha sonra, diziyi 1 ile çarpmak için basit bir çarpma formülümüz var. Bu basit çarpmayla, #DEĞER! değerini alacağınız yeni bir dizi elde edersiniz. Metin değerleri ve sayılara ilişkin hata bozulmadan kalacaktır.
- Buradan EĞERHATA fonksiyonu hata değerlerini boş değerlere dönüştürecektir.
- Ve sonunda TEXTJOIN değerleri birleştirecek ve sonuçta yalnızca sayılara sahip olacaksınız.
Yukarıdaki formül yalnızca Excel 2019 sürümü ve üzeri için geçerlidir. Bunun nedeni, önceki sürümlerde bulunmayan TEXTJOIN’i kullanmamızdır.
Formülün bir kısmı için değer istiyorsanız o kısmı seçin ve F9 kısayol tuşuna basın.
TEXTJOIN, çalışma sayfasındaki bir hücreyi güncellediğinizde değeri değiştiren geçici bir işlevdir. Bu çalışma kitabınızı biraz yavaşlatabilir.
Peki ya daha önceki bir sürümü kullanırsam (2007, 2010, 2013, 2016)
Daha önceki sürümleri kullanıyorsanız farklı bir formül kullanmanız gerekir. Bunun gibi bir formül:
=IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"") =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)Yukarıdaki iki formül farklı değer türleriyle iyi çalışır. Aşağıdaki şekilde test ettim.
Yukarıdaki A1 hücresinde metin, sayı ve sembollerden oluşan karmaşık bir değerimiz var ve sonuçta sadece sayılar var.