Excel'de bir metin dizgesinden yalnızca sayıları nasıl çıkarabilirim?
Excel ile çalışırken, "Sipariş 1234" veya "Puan 95,6" gibi metin ve sayı içeren hücrelerle sıkça karşılaşılır. Bu tür metin dizelerinden yalnızca sayısal değerleri ayıklamak, daha derinlemesine analiz, hesaplama veya raporlama yapmak için genellikle gereklidir. Sayılar metnin başında, sonunda ya da içinde gömülü olsun veya yalnızca ondalık sayıları mı hedefliyorsunuz, Excel bu görevi yerine getirmek üzere çeşitli pratik çözümler sunar.
Bu kılavuzda, Excel'de metin dizelerinden sayı çıkarmak için farklı senaryoları (sayıların herhangi bir konumdan çıkarılması, ondalık sayıların işlenmesi, metin dizgesinin başından veya sonundan sayı alınması) kapsayan çeşitli yöntemleri inceleyeceğiz. Her yöntem, nasıl çalıştığı, kullanım kolaylığı ve etkili uygulama için özel ipuçları açısından açıklanacaktır.
Metin dizgesinden herhangi bir konumdaki sayıyı çıkar
Metin dizgesinden yalnızca ondalık sayıyı çıkar
Metin dizgesinin sonundan sayıyı çıkar
Metin dizgesinin başından sayıyı çıkar
Metin dizgesinden herhangi bir konumdaki sayıyı çıkar
Metin dizileri içinde herhangi bir yerde gömülü sayıları çıkarmak, özellikle çeşitli kaynaklardan içe aktarılan verileri temizlerken veya birden fazla sayfadan bilgileri birleştirirken sıkça karşılaşılan bir gereksinimdir. Bu senaryo için Excel sürümünüzle ve formüllere ya da eklentilere olan aşinalık düzeyinize göre farklı kullanım durumlarına uygun, birkaç pratik ve verimli çözüm bulunmaktadır.
♦ Metin dizgesinden herhangi bir konumdaki sayıyı Flash Fill ile çıkar
Flash Fill, Excel 2013 ve sonraki sürümlerde bulunan, metin dizgesinden bir örnekle gösterdiğiniz desene göre sayı çıkarmaya yarayan hızlı ve kullanışlı bir özelliktir. Bu çözüm, formül veya makro gerektirmediği için basit senaryolar için pratiktir:
1. Metin dizisinizin yanındaki bir hücreye, çıkarılmasını istediğiniz sayıyı elle yazın (örneğin, A2 hücresi "Sipariş123A" içeriyorsa, B2'ye "123" yazın).
2. Elle girdiğiniz hücrenin altındaki aktif hücreye tıklayın ve Ctrl + Etuşlarına basın (veya)Veri sekmesi > Flash Fill yolunu izleyin). Excel, ilk örneğinize dayanarak benzer hücrelerden sayıları çıkarıp aşağıya doğru otomatik olarak dolduracaktır.
Bu yöntem, yapısı tutarlı küçük ila orta boyutlu tek seferlik veri kümeleri için mükemmeldir. Flash Fill’in desen tanıma özelliğine dayandığını unutmayın—verileriniz çok tutarsızsa ya da sayı/metin sırası karışık ise, beklenen şekilde çıkarım yapılamayabilir. Flash Fill otomatik olarak algılamazsa, Dosya>Seçenekler>Gelişmiş>Otomatik Flash Fill seçeneğinin etkinleştirilip etkinleştirilmediğini kontrol edin.
♦ Metin dizgesinden herhangi bir konumdaki sayıyı formüllerle çıkar
Formüller kullanmak, metin dizelerinden sayı çıkarmak için esnek bir yoldur. Bu yöntem, yerleşik veya dinamik dizi işlevlerinden yararlanır ve özellikle kaynak veri değiştiğinde otomatik olarak güncellenmesini istediğiniz çözümler için idealdir.
Çıkarılan sayının görüntülenmesini istediğiniz boş bir hücre seçin. Ardından, Excel sürümünüzle uyumlu aşağıdaki formüllerden birini girin. Formülü girdikten sonra, diğer hücrelere uygulamak için doldurma tutamacını aşağı doğru sürükleyin. Detaylı bilgi için aşağıdaki çizime göz atın:
● Tüm Excel sürümleri:
=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),"")Bu formül, tüm modern Excel sürümleriyle uyumludur ve Excel 365 ya da Excel 2021 kullanmıyorsanız güvenilir bir tercihtir. Büyük veri kümelerinde birden fazla işlev içeren formüllerin performansı olumsuz etkileyebileceğini unutmayın. Ayrıca, metin dizelerinin en az bir sayısal karakter içerdiğinden emin olun; aksi takdirde formül boş sonuç verebilir veya hata döndürebilir.
● Excel 365 veya Excel 2021 ve sonraki sürümler:
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))Bu formül, yeni dinamik dizi işlevlerinden yararlanarak Office 365 veya Excel 2021 ve sonraki sürümleri kullanan kullanıcılar için çıkarım sürecini büyük ölçüde basitleştirir. Hem basit hem de daha karmaşık alfanümerik karışımları verimli şekilde işler. Doğru sürümü kullandığınızdan emin olun; aksi takdirde formül beklenen şekilde çalışmayabilir.
İpucu: Karışık veya negatif sayılarla ya da özel sembolleri içeren sayılarla formüller kullanırken, çıkarılan sonuçların doğruluğunu kontrol edin. Ayrıca formüller, varsayılan olarak ondalık noktalarını tanımaz; bu nedenle ondalık sayıları çıkarmak istiyorsanız lütfen aşağıdaki özel bölüme başvurun. Beklenmeyen bir sonuç veya hata alırsanız, hücre başvurularınızı ve formül sözdizimini tekrar kontrol edin.
♦ Metin dizgesinden herhangi bir konumdaki sayıyı Kutools for Excel ile çıkar
Kutools for Excel, metin dizgesi içindeki herhangi bir konumdan sayı çıkarmak için düzgün bir yöntem sunar. Özellikle formüller yerine etkileşimli bir arayüz tercih eden veya manuel çaba gerektirmeden geniş bir aralığı işlemek isteyen kullanıcılar için uygundur. Bu yöntem, Zamanı Kaydet ve formülleri ezberlemek veya sorunlarını çözmek zorunluluğunu ortadan kaldırarak görevleri kolaylaştırır.
- Kutools > Metin > Metni Çıkart seçeneğine aşağıda gösterildiği gibi tıklayın.
- Metni Çıkart iletişim kutusunda:
(1.) Metin dizelerinizi içeren hücre listesini seçin;
(2.) Yalnızca sayısal kısımların alınmasını sağlayan Sayıları Çıkart seçeneğini belirleyin;
(3.) Çıkarılan sayıların Kaynak Veri değişiklikleriyle birlikte otomatik olarak güncellenmesini istiyorsanız, Formül Olarak Ekle kutusunu işaretleyin (aksi takdirde sonuçlar sabit metin olacaktır);
(4.) Tamam'a tıklayın.
- İstendiğinde, çıktının yerleştirileceği bir hücre seçin. Mevcut verilerin üzerine yazılmaması için sonuçların sığacağı yeterli boş alana sahip olduğundan emin olun.
- Tamam’a tıklayın. Seçilen her hücredeki sayılar anında çıkarılacaktır.
Bu yaklaşım, kullanıcı dostudur ve formül hatalarını veya yanlış uygulanmış hücre başvurularını azaltır. Ancak Kutools'un bir eklenti olduğunu ve standart Excel kurulumunda bulunmadığını unutmayın. Toplu işlemler için ve karmaşık işlev sözdiziminden veya VBA'dan kaçınmak isteyen kullanıcılar için uygundur.
Sayılar beklendiği gibi çıkarılmazsa, Orijinal Metin içinde karışık biçimlendirme (gizli karakterler veya standart olmayan rakamlar gibi) olup olmadığını tekrar kontrol edin. Ayrıca toplu değişiklikler yapmadan önce çalışma sayfanızın bir yedek kopyasını mutlaka alın.
♦ Metin dizgesinden herhangi bir konumdaki sayıyı VBA koduyla çıkar
Formüller ve eklentiler yeterli olmayan daha deneyimli kullanıcılar veya özel durumlar için VBA, Excel’de metin dizelerinden sayı çıkarmak üzere esnek bir çözüm sunar. Bu yaklaşım yüksek düzeyde özelleştirilebilir olup, özellikle tekrarlayan görevleri otomatikleştirmek veya standart dışı veri kümelerini işlemek için son derece kullanışlıdır.
- Alt + F11 tuşlarına basarak VBA düzenleyicisini açın. Microsoft Visual Basic for Applications penceresinde, Ekle > Modül’e tıklayın. Yeni modül penceresine aşağıdaki kodu yapıştırın:
- VBA kodu: Metin dizgesinden yalnızca sayıyı çıkar:
Sub ExtrNumbersFromRange() Dim xRg As Range Dim xDRg As Range Dim xRRg As Range Dim nCellLength As Integer Dim xNumber As Integer Dim strNumber As String Dim xTitleId As String Dim xI As Integer xTitleId = "KutoolsforExcel" Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8) If TypeName(xDRg) = "Nothing" Then Exit Sub Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8) If TypeName(xRRg) = "Nothing" Then Exit Sub xI = 0 strNumber = "" For Each xRg In xDRg xI = xI + 1 nCellLength = Len(xRg) For xNumber = 1 To nCellLength If IsNumeric(Mid(xRg, xNumber, 1)) Then strNumber = strNumber & Mid(xRg, xNumber, 1) End If Next xNumber xRRg.Item(xI) = strNumber strNumber = "" Next xRg End Sub - Kodu ekledikten sonra F5 tuşuna basın veya Çalıştır düğmesine tıklayın. İşlenecek metin dizelerinin aralığını seçmenizi isteyen bir iletişim kutusu görünecektir.
- Aralığınızı seçtikten sonra Tamam'a tıklayın. Ardından, çıktının başlangıç hücresini belirtmenizi isteyen ikinci bir iletişim kutusu açılacaktır.
- Tekrar Tamam'a tıklayın. Orijinal seçimde gömülü tüm sayılar, belirttiğiniz çıktı alanına çıkarılıp yerleştirilecektir.
VBA yönteminin avantajları arasında büyük veri kümelerini verimli işleme ve temel VBA bilgisiyle davranışı özelleştirme (yalnızca ilk sayıyı çıkarma, ondalıkları işleme veya belirli sembolleri hariç tutma gibi) yeteneği yer alır. Önlemler: VBA makroları geri alınamaz ve seçilen Liste Yerleştirme Alanı orijinal verilerinizle çakışırsa verilerin üzerine yazabilir; bu nedenle her zaman önce yedek bir dosyada test edin. Excel'de makroların etkin olduğundan emin olun.
Metin dizisinden yalnızca ondalık sayıyı çıkarın
Bazen metin dizileriniz, ondalık noktalar içeren sayılar (örneğin "ağırlık:15,25 kg") içerebilir. Özellikle kayan nokta dahil olmak üzere ondalık sayıları çıkarmak istiyorsanız, standart formüller bunları doğru şekilde yakalayamayabilir. Aşağıdaki çözümler bu tür durumlar için tasarlanmıştır:
Aşağıdaki formüller, metin dizilerinden ondalık sayıları verimli bir şekilde çıkarmanıza yardımcı olur. Uygun formülü verilerinizin yanındaki boş bir hücreye girin ve gerektiğinde diğer satırlara kopyalamak için doldurma tutamacını kullanın.
● Tüm Excel sürümleri:
=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&,"1023456789")),999),ROW(INDIRECT("1:999"))))Bu formül, Excel’in çoğu sürümünü destekler ve çıkarım sırasında ondalık noktaları sayının bir parçası olarak doğru şekilde tanımlamak üzere tasarlanmıştır. Metninizde fazladan nokta bulunmadığından emin olmak için verilerinizi dikkatle gözden geçirin. Ondalık sayılar beklenen şekilde çıkarılmıyorsa, orijinal dizide standart olmayan karakterler veya biçimlendirme sorunları olup olmadığını kontrol edin.
● Excel 365:
=REGEXEXTRACT(A2,"[\d.]+")Excel 365 ile dinamik diziler, karmaşık metin dizilerinden çıkarılan ondalık sayıları daha akıllıca işleyip döndürebilir. Formülü hedef hücreye girin, Enter tuşuna basın ve ardından gerekirse formülü aşağı doğru genişletin.
İpucu: Sayılar yerine sürekli sıfır veya hata alıyorsanız, verilerinizin geçerli ondalık sayılar içerdiğinden ve hücre başvurularının doğru olduğundan emin olun. Sayılarınız hem negatif değerler hem de ondalık kısımlar içeriyorsa, formüllerin ayarlanması gerekebilir.
Metin dizgesinin sonundan sayıyı çıkar
Sayılar her zaman metin dizilerinizin sonunda yer alıyorsa (örneğin "221_ÜrünKimliği_446" veya "923Bilet#2021"), yalnızca sondaki sayıyı ("446" veya "2021") çıkarmak isteyebilirsiniz. Burada verilen formül tam olarak bu senaryo için tasarlanmıştır; böylece yalnızca metnin sonundaki sayısal diziler alınır.
Aşağıdaki formülü boş bir hücreye kopyalayıp yapıştırın. Eski Excel sürümlerinde formülü dizi formülü haline getirmek için Ctrl + Shift + Enter tuşlarına basın. Dinamik dizilere sahip yeni Excel sürümlerinde ise sadece Enter tuşuna basın:
=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&,LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&,LEN(A2))), 0)))Diğer satırlara da uygulamak için doldurma tutamacını aşağı doğru sürükleyin. Bu yöntem, her dizide yalnızca sondaki sayının gerekli olduğu ve ortada ya da başta yer alan sayıların göz ardı edildiği tutarlı biçimler için en iyisidir.
Not: Verilerinizde boşluklar, özel semboller veya konum farklılıkları varsa, formülü ayarlayın veya yukarıda açıklanan daha genel bir yöntemi kullanın. Çıktının beklendiği gibi olduğundan özellikle Metinle Biter sayıya benzeyen sayısal olmayan karakterler içeriyorsa emin olun.
Metin dizgesinin başından sayıyı çıkar
Metin dizilerinizin en başında yer alan sayıları kaldırmak istiyorsanız, yalnızca bu kısmı ayırmak için özel olarak tasarlanmış bir formülden yararlanabilirsiniz.
Formülü boş bir hedef hücreye yerleştirin ve eski Excel sürümleri için Ctrl + Shift + Entertuşlarına basarak bir dizi formülü oluşturun (Excel 365/2021’te sadece)Entertuşuna basın):
=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&,LEN(A2)+1)), 1) *1), 0) -1)Ardından, çıkarım yapılması gereken tüm satırlar için formülü aşağı doğru sürükleyin. Bu yaklaşım, dizinin başında yer alan sayıları döndürür—diğer yerlerde daha fazla sayı olsa bile yalnızca baştaki sayısal kısım alınır.
İpucu: Dizenizin ilk karakteri sayısal değilse, bu formül boş bir sonuç veya hata döndürebilir. En iyi sonuçları elde etmek için orijinal verilerinizi kontrol edip gerektiğinde temizleyin.
Metin dizilerinden sayı çıkarmak, Excel veri hazırlama süreçlerinde yaygın ve çoğu zaman kritik bir görevdir. Doğru yöntem, veri yapınız, Excel sürümünüz ve formüllerle veya eklentilerle çalışma konusundaki rahatlık düzeyinize bağlıdır. Senaryonuz yukarıdaki çözümlere uymuyorsa, yöntemlerin bir kombinasyonunu kullanmayı veya gelişmiş ihtiyaçlar için diğer özel eklentileri incelemeyi düşünün. Çıkarılan sonuçları tutarsızlıklara karşı kontrol etmek, yedek kopyalar saklamak ve küçük veri örnekleriyle başlamak tipik sorunlardan kaçınmanıza yardımcı olur.
İlgili Makaleler:
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— birkaç dakikada hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— ayrı ayrı eklenti satın almaya göre tasarruf sağlar