Excel’de bir dizedeki ilk veya tüm sayıları nasıl bulabilirim?

Bu yöntem, metin dizesindeki ilk sayıyı saptayıp çıkarmakla kalmaz; aynı zamanda bu ilk sayısal karakterin hücredeki tam konumunu belirlemek için kullanılabilecek formüller de sunar. Söz konusu formüller, özellikle ürün kodları veya açıklayıcı metinle birlikte gömülü kimlikler gibi sayıların konumu değişken olan standartlaştırılmış dizeleri analiz etmek zorunda olan kullanıcılar için son derece değerlidir.

Dizi formülüyle metin dizesindeki ilk sayıyı bulun ve çıkarın

Metin dizesinden ilk sayıyı almak için boş bir hücre seçin, ardından içine şu formülü girin: =MID(A2,MIN(IF((ISNUMBER(MID(A2,ROW(INDIRECT("1:"&,LEN(A2))),1)+0)*ROW(INDIRECT("1:"&,LEN(A2)))),ISNUMBER(MID(A2,ROW(INDIRECT("1:"&,LEN(A2))),1)+0)*ROW(INDIRECT("1:"&,LEN(A2))))),1)+0 (Burada A2, ilk sayıyı arayacağınız metin hücresidir.) Ardından aynı anda Ctrl+Shift+Enter tuşlarına basın. Bu dizi girişi, formülünüzün dizedeki her karakteri ayrı ayrı işlemesini sağlar.
Onayladıktan sonra, formülü gerektiği kadar aşağıya uygulamak için hücrenin Doldurma Kulpu’nu sürükleyin.

Uygulandıktan sonra, her metin dizesinin ilk sayıları aşağıdaki gibi bulunup çıkarılır:

Formüllerle metin dizesinde ilk sayının konumunu bulun

İlk rakamın konumunu döndürmek için boş bir hücre seçin ve aşağıdaki formülü girin:=MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))) (buradaki A2, kontrol etmek istediğiniz hücredir). Bu formül, metindeki herhangi bir rakamın (0–9) en erken konumunu bulur. Gerekirse formülü aşağı doğru sürükleyerek bitişik hücreleri doldurun.

Not: Alternatif olarak aşağıdaki dizi formülünü kullanabilirsiniz:=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&,{0,1,2,3,4,5,6,7,8,9})). Formülü onaylamak için Ctrl+Shift+Enter tuşlarına basmayı unutmayın. Formülleri kopyalarken, yapı değişirse sabit hücre başvurularını güncellemeyi ihmal etmeyin. Dize herhangi bir sayı içermiyorsa bu formüller hata verir. Böyle durumlarda özel mesajlar görüntülemek için formülü EĞERHATA ile sarmalayabilirsiniz.

Amacınız bir hücreden sayısal olmayan tüm karakterleri çıkararak yalnızca sayıları elde etmekse, Kutools for Excel’in Belirli Karakterleri Sil aracı kullanışlı bir çözüm sunar. Bu özellik, özellikle içe aktarılmış verilerle sık sık çalışan, hücreleri hızlı ve toplu şekilde temizlemek isteyen ve karmaşık formüller yerine doğrudan, kullanıcı dostu bir arayüz tercih eden kullanıcılar için idealdir.

1. İşlemek istediğiniz aralığı seçin ve menüye gidin: Kutools > Metin > Belirli Karakterleri Sil. Yönlendirme için ekran görüntüsüne bakın:

2. Görüntülenen Belirli Karakterleri Sil iletişim kutusunda yalnızca Sayısal Olmayan onay kutusunun işaretli olduğundan emin olun, ardından ilerlemek için Tamam düğmesine tıklayın.

Sonuç olarak, araç seçili hücrelerden tüm sayısal olmayan karakterleri çıkarır ve her hücrede yalnızca sayısal bileşenler kalır.

Bu işlemi çalıştırmadan önce geri dönmek isterseniz diye aralığınızın bir yedek kopyasını alın. Harflerle ayrılmış sayılar içeren metin dizeleri (örneğin "A23B17") söz konusu olduğunda tüm sayılar birleştirilerek tek bir değer haline getirilir (“2317”). Farklı bir düzenleme bekliyorsanız, bu özelliği diğer işlevlerle birleştirmeyi düşünün.

Metin hücresindeki tüm sayıları (biçim fark etmeksizin) doğrudan tek bir değer olarak çıkarmak istiyorsanız, Kutools for Excel içindeki EXTRACTNUMBERS işlevi, kullanıcı dostu ve son derece verimli bir çözüm sunar. Bu özellik, özellikle toplu işlem yapmanız gereken durumlarda, çalışma sayfasında esnekçe uygulanabilen formül tabanlı bir yaklaşım arayan kullanıcılar için büyük kolaylık sağlar.

1. Çıkarılan sayıların görünmesini istediğiniz boş bir hücre seçin, ardından şuraya tıklayın: Kutools > İşlevler > Metin > EXTRACTNUMBERS. Aşağıdaki çizime bakın:

2. İşlev Bağımsız Değişkenleri iletişim kutusunda ilgili metin hücresini Txt alanına atayın ve sayısal bir değer döndürmesi için DOĞRU değerini N alanına girin. Ardından, onaylamak ve Sayıları Çıkartmak için Tamam düğmesine tıklayın.

Notlar:
(1) DOĞRU, YANLIŞ girebilir veya N alanını boş bırakabilirsiniz. DOĞRU belirtmek sayısal bir değer döndürür; YANLIŞ ya da boş bırakmak ise sayıları metin olarak döndürür (baştaki sıfırların korunması için kullanışlıdır).
(2) Bu özellik aynı zamanda çalışma sayfasında doğrudan bir formül olarak da kullanılabilir: =EXTRACTNUMBERS(A2,TRUE). Tercih ettiğiniz çıktı hücresine bu formülü girin.
(3) Formülü bitişik satırlara kopyalarken, hücre başvurularının gerektiği gibi güncellendiğinden emin olun.

Her metin hücresinden çıkarılan sayılar aşağıdaki gibi görüntülenir:

Formüller ve yerleşik özellikler yetersiz kalıyorsa—örneğin bir dizeden yalnızca ilk sayıyı, ilk tam sayı dizisini veya tüm ayrı sayıları ayıklamak istiyorsanız—VBA kodu size daha fazla esneklik ve otomasyon imkanı sunar. Bu yöntem, özellikle büyük veri kümelerinde benzersiz çıkarma gereksinimleriyle düzenli olarak çalışan ya da süreci yeniden kullanılabilir makrolarla otomatikleştirmek isteyen kullanıcılar için idealdir. Unutmayın: Makroların etkinleştirilmesi gerekir ve büyük veri kümelerinde kodu çalıştırmadan önce dosyanızı mutlaka yedekleyin.

1. Ana Excel penceresinde, Geliştirici Araçları > Visual Basic seçeneğine tıklayarak VBA düzenleyicisini açın. Microsoft Visual Basic for Applications penceresinde, Ekle > Modül seçeneğini tıklayın. Aşağıdaki kodu yeni modüle kopyalayıp yapıştırın:

İlk sayıları çıkar: Her dizede yer alan ilk ardışık sayısal diziyi çıkarır (örneğin, "abc12def45" ifadesinden 12 sonucunu verir).

Sub ExtractFirstNumber()
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    Dim i As Integer
    Dim sNum As String
    Dim FirstNum As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    For Each cell In rng
        txt = cell.Value
        sNum = ""
        FirstNum = ""
        
        For i = 1 To Len(txt)
            If Mid(txt, i, 1) Like "[0-9]" Then
                sNum = sNum & Mid(txt, i, 1)
                
                If i = Len(txt) Or Not (Mid(txt, i + 1, 1) Like "[0-9]") Then
                    FirstNum = sNum
                    Exit For
                End If
            ElseIf sNum <> "" Then
                FirstNum = sNum
                Exit For
            End If
        Next i
        
        cell.Offset(0, 1).Value = FirstNum
    Next cell
End Sub

Tüm sayıları çıkar: Tüm sayıları sırayla çıkarır ve ayrı sayı bloklarını boşluklarla ayırır (örneğin, "abc12def45" ifadesi "12 45" sonucunu verir).

Sub ExtractAllNumbers()
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    Dim i As Integer
    Dim arrNums As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    For Each cell In rng
        txt = cell.Value
        arrNums = ""
        
        For i = 1 To Len(txt)
            If Mid(txt, i, 1) Like "[0-9]" Then
                arrNums = arrNums & Mid(txt, i, 1)
            Else
                If arrNums <> "" Then
                    arrNums = arrNums & " "
                End If
            End If
        Next i
        
        arrNums = WorksheetFunction.Trim(arrNums)
        cell.Offset(0, 1).Value = arrNums
    Next cell
End Sub

2. VBA düzenleyicide Çalıştır düğmesine tıklayın veya kodu çalıştırmak için F5 tuşuna basın. Hedef aralığı seçmenizi isteyen bir iletişim kutusu açılacaktır. Kod, çıkarılan sayıyı (veya sayıları) orijinal verilerin hemen sağındaki sütuna yazar.

Verilerinizin hemen sağındaki sütunları zaten kullanıp kullanmadığınızdan emin olun; üzerine yazmayı önlemek için. Kod çalışmıyor gibi görünüyorsa, çalışma kitabınızda makroların etkin olup olmadığını kontrol edin ve metin değerlerinizin gerçekten sayılar içerdiğini doğrulayın.

Excel’in yerleşik Hızlı Doldurma özelliği, örnek kalıplardan öğrenerek metin dizelerinden otomatik olarak sayıları çıkarabilir. Bu yöntem, özellikle iyi yapılandırılmış veriler üzerinde hızlıca manuel işlemler yapmak istediğinizde ya da karmaşık mantık kurmadan basit, formül içermeyen bir çözüm aradığınızda son derece kullanışlıdır.

Bir dizeden sayı çıkarmak için Hızlı Doldurma özelliğini kullanmak üzere:

1. Verilerinizin yanındaki bir Boş sütun içinde, ilk hücre için istediğiniz çıktıyı manuel olarak yazın. Örneğin, A2 hücresi "OrderA12B3" içeriyorsa, çıkarma sonucunu göstermek üzere B2 hücresine "123" yazın.
2. Örneğinizin altındaki hücreye gidin (örneğin B3) ve bir sonraki satır için beklenen sonucu yazmaya başlayın. Excel, tanıdığı kalıbın bir önizlemesini gösterebilir. Önizleme amacınızla uyumluysa, yalnızca Enter tuşuna basın. Aksi takdirde, beklenen kalıbı daha net belirlemek için birkaç ek giriş yapmaya devam edin.
3. Alternatif olarak, ilk hücreye örneği girdikten sonra tüm hedef Liste Yerleştirme Alanı’nı seçin ve şuraya gidin: Veri > Hızlı Doldurma(veya)Ctrl + E tuşlarına basın). Excel, öğrenilen kalıba göre sayıları otomatik olarak dolduracaktır.

Hızlı Doldurma, tutarlı girdi ve çıktı ilişkilerini tanır; bu nedenle örneklerinizin açık ve doğru olduğundan emin olun. Bu özellik, veri biçimi düzenli olduğunda en iyi şekilde çalışır—veri yapınız daha değişken ise ya da hassas bir denetim ihtiyacınız varsa, bir formül veya makro çözümü kullanmayı düşünün.

Hızlı Doldurma görünmüyorsa, etkin olduğundan emin olun: Dosya > Seçenekler > Gelişmiş > Düzenleme seçenekleri bölümünde Otomatik Hızlı Doldurma seçeneğinin işaretli olduğundan emin olun.