Excel’de Bir Metinden Sayı Nasıl Çıkarılır: Doğru Yöntemi Bulma

Excel’de veri temizleme yaparken, alfasayısal dizelerden sayı çıkarmanız gereken görevlerle karşılaşabilirsiniz. Bu sorun, ürün kodları veya anket yanıtları gibi sayılar metinlerin içine gömülü olduğunda yaygındır.

Bu yazıda, farklı kullanım durumları için RIGHT(), LEN() ve FIND() gibi formülleri kullanarak metinlerden sayıları çıkarmanın çeşitli yöntemlerini göstereceğim. Bazı temel kavramlara aşina değilseniz, Excel’de sağlam bir temel oluşturmak için Introduction to Excel kursumuzu almanızı öneririm. Ayrıca ham veriyi dönüştürmeyi öğrenmek için Data Preparation in Excel kursumuza da göz atmalısınız.

Bir Metinden Sayı Çıkarmak için Excel Formülleri

Excel, metinlerden sayıları çıkarmak için farklı formül tabanlı yöntemler sunar. Her yöntem, sayının dizenin neresinde bulunduğuna bağlı olarak farklı ihtiyaçlara uygundur. Zamanınız kısıtlıysa, en acil ihtiyacınızı çözen bölüme doğrudan atlayabilirsiniz.

Excel’de bir dizenin başındaki sayıları çıkarma

Bazen, sayıların dizenin başında olduğu senaryolarla karşılaşabilirsiniz. Bu gibi durumlarda sayıları çıkarmak için LEFT() ve FIND() kullanırsınız. İşlevleri bir formülde birleştirdiğinizde, sayılar aşağıdaki mantıkla çıkarılır:

• FIND(): Dizgedeki ilk harfin konumunu bulur.

• LEFT(): Dizgenin başından ilk harfin konumuna kadar (bir eksiği) karakterleri çıkarır.

Aşağıdaki formülde ilk bölüm, dizgedeki ilk harfi bulur. Ardından ikinci bölüm, o harften önceki her şeyi çıkarır ve yalnızca baştaki sayılar kalır. Hiç harf yoksa, tüm dizgeyi döndürür.

=LEFT(A2, FIND(MID(A2, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)

Excel’de bir dizenin başından sayıları çıkarma. Görsel: Yazar.

Bu yöntem, sipariş numaraları, üye kimlikleri ve parti numaraları gibi sayısal öneklerin yaygın olduğu veri kümeleriyle çalışırken uygundur.

Excel’de bir dizenin sonundaki sayıları çıkarma

Excel’de bir dizenin sonundan sayıları çıkarmak istiyorsanız RIGHT(), LEN() ve FIND() işlevlerini kullanmanız gerekir. Excel sayfasında aşağıdaki verilere sahip olduğunuzu varsayın. İşlevleri şu şekilde birleştirerek sayıyı çıkarırsınız:

• FIND(): Dizgedeki ilk rakamın konumunu bulur.

• LEN(): İlk rakamdan dizgenin sonuna kadar olan karakter sayısını hesaplar.

• RIGHT(): İlk rakamdan dizgenin sonuna kadar olan karakterleri çıkarır.

Aşağıdaki formül tüm baştaki metni kaldırır ve dizgedeki ilk sayıyı, ardından gelen her şeyle birlikte döndürür.

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)

Excel’de bir dizenin sonundan sayıları çıkarma. Görsel: Yazar.

Bu yöntem, ürün SKU’ları, sipariş kimlikleri ve seri numaraları gibi tutarlı alfasayısal karakterlere sahip veri kümelerinde en kullanışlı olanıdır.

Excel’de bir dizenin herhangi bir konumundaki sayıları çıkarma

Sayıların dizenin herhangi bir yerinde göründüğü bir veri kümeniz varsa, sayıları çıkarmak için TEXTJOIN(), MID() ve ROW() kullanan gelişmiş formüllere ihtiyacınız olacaktır.

Bu işlevleri bir formülde birleştirmek, bir metinden sayıyı aşağıdaki mantıkla çıkarır: 

• ROW() ve MID(): Her bir karakteri dolaşarak sayısal olup olmadığını belirler.

• IFERROR(): Belirlenen sayıları diğer karakterlerden izole etmek için mantıksal sınama yapar.

• TEXTJOIN(): Çıkarılan tüm sayısal karakterleri tek bir sayı dizgesinde birleştirir.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Bir dizenin herhangi bir konumundan sayı çıkarma. Görsel: Yazar.

Bu yöntem, sayıların dizenin herhangi bir yerinde görünebildiği alfasayısal karakterlere sahip veri kümeleri için en uygunudur. Not: Bu işlevlerin tümü Excel’in eski sürümlerinde mevcut değildir. En yeni Excel sürümlerinde bulunan işlevler ve formüller hakkında daha fazla bilgi edinmek için Excel Fundamentals yetkinlik yolumuza göz atın.

İstenmeyen karakterleri filtreleyerek sayıları çıkarma

Bazen veriniz dağınık olabilir. Neyse ki SUBSTITUTE() ve TEXTJOIN() işlevlerini birleştirebilirsiniz. Şu adımları izleyin:

• SUBSTITUTE(): SUBSTITUTE() işlevi, bir dizgedeki tüm sayısal olmayan karakterleri bir ayırıcıyla (örneğin boşluk veya boş dizge) değiştirmek için kullanılabilir. Örneğin, harfleri ve özel karakterleri boşluklarla değiştirebilirsiniz.

• TEXTJOIN(): Ardından TEXTJOIN() işlevi, geriye kalan karakterleri (artık yalnızca sayılar olmalıdır) tek bir dizgede birleştirmek için kullanılabilir.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Karakterleri filtreleyerek bir metinden sayı çıkarma. Görsel: Yazar.

Excel’de Ne Zaman Bir Metinden Sayı Çıkarmanız Gerekebilir

Şimdi, metinlerden sayıları çıkarmayı biraz pratik etmek için iki küçük vaka çalışmasına bakalım.

Örnek 1: Ürün kodlarından sayıları çıkarma

Her bir öğenin benzersiz bir ürün koduyla tanımlandığı bir ürün stoğunu yönettiğinizi varsayın. Bu ürün kodları "PROD1234", "ITEM5678" veya "SKU91011" gibi alfasayısal dizelerdir.

Dolayısıyla, alfasayısal ürün kodlarından sayısal Ürün Kimliği’ni çıkarmak için sayının konumuna dayalı işlevler kullanırız. Unutmayın, sayılar sonda ise RIGHT(), LEN() ve FIND() uygulayın. Baştaysa LEFT() ve FIND() kullanın.

Aşağıdaki örnek, ürün kodlarının sayısal bölümünü kodun sağ tarafından çıkarır.

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))

Excel’de ürün kodlarından sayıları çıkarma. Görsel: Yazar.

Örnek 2: Gömülü sayılar içeren anket verileri

Anket verileri bazen "10 üzerinden 5 puan", "Puan: 8" veya "3 yıldız" gibi hem metin hem sayı içerebilir. Ortalama hesaplamadan veya eğilimleri belirlemeden önce biraz çalışma yapmamız gerekir.

Sayılar metnin herhangi bir yerinde görünebileceğinden, TEXTJOIN(), MID() ve ROW() kullanan dinamik bir formül gereklidir.

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))

Excel’de herhangi bir konumdaki sayıları çıkarma. Görsel: Yazar.

Özel Durumlar için Kopyala-Yapıştır Kod Örnekleri

Son bölümde, farklı ve özgül senaryoları ele alan birkaç ek formül vermek istedim. Kopyalayıp yapıştırmaktan çekinmeyin; umarım yaşadığınız belirli bir sorunu çözer. 

İlk bitişik sayı bloğunu çıkarma

Dizeniz birden fazla sayısal dizi içeriyor ancak yalnızca ilk gruba ihtiyacınız varsa, LET() ve SEQUENCE() işlevlerini kullanan bu çözümü deneyin:

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)

Excel’de bir dizgeden ilk bitişik blok çıkarma. Görsel: Yazar.

Parantez içinde yer alan sayıları çıkarma

Sayıların parantez içinde göründüğü durumlar için şunu kullanın:

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)

Excel’de parantez içinde yer alan sayıları çıkarma. Görsel: Yazar.

Belirli bir karakterden sonra gelen sayıları çıkarma

Veriniz iki nokta üst üste gibi bir ayırıcı kullanıyorsa (örneğin, "Puan: 8"), sayıları şununla yakalayın:

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))

Excel’de özel bir karakterden sonra gelen sayıyı çıkarma. Görsel: Yazar.

Bu formül, iki nokta üst üsteden sonraki her şeyi çeker. Sayısal bir değere ihtiyacınız varsa sonucu VALUE() ile sarmalayın. Farklı bir ayırıcı varsa iki noktayı değiştirin.

Tüm sayıları çıkarıp tek bir değerde birleştirme

Tüm rakam olmayan karakterleri kaldırıp bir dizgedeki her sayısal karakteri birleştirmek için şunu kullanın:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))

Excel’de tüm sayıları çıkarıp tek bir değerde birleştirme. Görsel: Yazar.

İpucu: Excel’in eski sürümlerinde bunu bir dizi formülü olarak girin (Ctrl+Shift+Enter).

Ondalık noktaları içeren sayıları çıkarma

Ondalık içerebilen sayısal dizeler (ör. "Fiyat: 45.99") için hem rakamları hem ondalık noktayı korumak üzere bu formülü kullanın:

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))

Excel’de ondalık noktaları da dahil ederek sayıları çıkarma. Görsel: Yazar.

Not: Bu formül yalnızca bir ondalık noktasının bulunduğunu varsayar.

Sonuç

Metinlerden sayı çıkarmak, veri temizleme için önemli bir tekniktir. Bir veri analisti olarak, bu yazıda ele alınan farklı formülleri uygulamayı öğrenmenizi öneririm.

Excel becerilerinizi ilerletmek istiyorsanız, veri analizi ve sunumda ustalaşmak için Data Analysis in Excel ve Data Visualization in Excel kurslarımızı almanızı öneririm. Ayrıca birçok kapı açabilecek  Financial Modeling in Excel kursumuzu da deneyin.