Örnek çalışma kitabını indirin
Bu eğitim Excel ve Google E-Tablolar'da bir metin dizesinin içinden bir sayıyı nasıl bulacağınızı ve çıkaracağınızı gösterin.
Sayıyı Dizeden Bul ve Çıkar
Bazen verileriniz sayılar ve metin içerebilir ve sayısal verileri çıkarmak istersiniz.
Sayı kısmı dizenin sağında veya solundaysa, sayıyı ve metni bölmek nispeten kolaydır. Ancak, sayılar dizenin içindeyse, yani iki metin dizesi arasındaysa, çok daha karmaşık bir formül kullanmanız gerekecektir (aşağıda gösterilmiştir).
TEXTJOIN - Excel 2016+'da Sayıları Ayıkla
Excel 2016'da, metin dizesinin herhangi bir yerinden sayıları çıkarabilen TEXTJOIN işlevi tanıtıldı. İşte formül:
| 1 | =TEXTJOIN("",DOĞRU,EĞERHATA((ORTA(B3,SATIR(DOLAYLI("1:"&UZUNLUK(B3))),1)*1),"")) |
Bu formülün nasıl çalıştığını görelim.
SATIR, DOLAYLI ve UZUNLUK İşlevleri, alfasayısal dizenizdeki her karaktere karşılık gelen bir dizi sayı döndürür. Bizim durumumuzda, “Monday01Day” 11 karaktere sahiptir, bu nedenle SATIR işlevi daha sonra {1;2;3;4;5;6;7;8;9;10;11} dizisini döndürür.
| 1 | =TEXTJOIN("",DOĞRU,EĞERHATA((ORTA(B3, {1;2;3;4;5;6;7;8;9;10;11},1)*1),"")) |
Daha sonra, MID İşlevi metin dizesinden her karakteri çıkararak orijinal dizenizi içeren bir dizi oluşturur:
| 1 | =TEXTJOIN("",DOĞRU,EĞERHATA({"M";"o";"n";"d";"a";"y";"0";"1";"D";"a ";"y"}*1),")) |
Dizideki her bir değeri 1 ile çarpmak, dizi karakteri metin ise, hatalar içeren bir dizi oluşturur:
| 1 | =TEXTJOIN("",DOĞRU,EĞERHATA(({#DEĞER!;#DEĞER!;#DEĞER!;#DEĞER!;#DEĞER!;#DEĞER!;0;1;#DEĞER!;#DEĞER!;#DEĞER) !}),"")) |
Ardından EĞERHATA İşlevi hata değerlerini kaldırır:
| 1 | =TEXTJOIN("",DOĞRU,{"";"";"";"";"";"";0;1;"";"";""}) |
Yalnızca kalan sayıları bir araya getiren TEXTJOIN İşlevini bırakarak.
Formülün, dizenizdeki tüm sayısal karakterleri size vereceğini unutmayın. Örneğin, alfanümerik diziniz Monday01Day01 ise, sonuç olarak size 0101'i verecektir.
Sayıları Ayıkla - Excel 2016'dan Önce
Excel 2016'dan önce, metinden sayıları çıkarmak için çok daha karmaşık bir yöntem kullanabiliyordunuz. BUL işlevini, EĞERHATA ve MIN işlevleriyle birlikte, sayı bölümünün hem ilk konumunu hem de numaradan sonraki metin bölümünün ilk konumunu belirlemek için kullanabilirsiniz. Daha sonra MID fonksiyonu ile sayı kısmını çıkarıyoruz.
| 1 | =ORTA(B3,DAK(EĞERHATA(BUL({0,1,2,3,4,5,6,7,8,9};B3),999999999))),DAK(EĞERHATA(ARA({"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" },B3,MIN(EĞERHATA(BUL({0,1,2,3,4,5,6,7,8,9},B3),999999999))),999999999))-MIN(EĞERHATA(BUL(BUL)( {0,1,2,3,4,5,6,7,8,9},B3),999999999))) |
Not: Bu bir Dizi formülüdür, formülü sadece ENTER yerine CTRL + SHIFT + ENTER tuşlarına basarak girmelisiniz.
Bu formülün nasıl çalıştığını adım adım görelim.
Yumruk Numarasını Bul
Sayının başlangıç konumunu bulmak için BUL işlevini kullanabiliriz.
| 1 | = MIN(EĞERHATA(BUL({1,2,3,4,5,6,7,8,9,0};B3),999999999)) |
BUL işlevinin metin bul bağımsız değişkeni için, BUL işlevinin her değer için ayrı bir arama yapmasını sağlayan {0,1,2,3,4,5,6,7,8,9} dizi sabitini kullanırız. dizi sabiti.
Bizim durumumuzdaki BUL işlevinin metin içi argümanı, 1'in 8 konumunda ve 0'ın 7 konumunda bulunabileceği Monday01Day'dir, dolayısıyla sonuç dizimiz şöyle olacaktır: {8,#DEĞER,#DEĞER,#DEĞER, #DEĞER,#DEĞER,#DEĞER, #DEĞER, #DEĞER,7}.
EĞERHATA işlevini kullanarak #DEĞER hatalarını 999999999 ile değiştiririz. Ardından bu dizideki minimumu ararız ve bu nedenle ilk sayının (7) yerini alırız.
Lütfen yukarıdaki formülün bir dizi formülü olduğunu unutmayın, tetiklemek için Ctrl+Shift+Enter tuşlarına basmanız gerekir.
Numaradan Sonra İlk Metin Karakterini Bul
Dize içindeki ilk sayıyı bulmaya benzer şekilde, fonksiyonun start_num argümanını da iyi bir şekilde kullanarak, sayıdan sonra metnin tekrar nerede başladığını belirlemek için BUL işlevini kullanırız.
| 1 | =MIN(EĞERHATA(BUL({"a","b","c","d","e","f","g","h","I","j","k","k)) ","l","m","n","o","p","q","r","s","t","u","v","w", "x","y","z"},B3,C3),999999999)) |
Bu formül, ilk sayıyı bulmak için kullanılan bir öncekine çok benzer şekilde çalışır, dizi sabitimizde yalnızca harfleri kullanırız ve bu nedenle sayılar #DEĞER hatalarına neden olur. Lütfen aramanın dizenin başından değil, yalnızca ilk sayı için belirlenen konumdan (bu başlangıç_sayısı argümanı olacaktır) sonra başladığımızı unutmayın.
Yukarıdaki formülü kullanmak için Ctrl+Shift+Enter tuşlarına basmayı unutmayın.
Bunların hepsini bir araya getirmekten başka bir şey kalmadı.
İki Metinden Sayı Çıkarma
Sayı bölümünün başlangıç konumuna ve bundan sonra metin bölümünün başlangıcına sahip olduğumuzda, istenen sayı bölümünü çıkarmak için MID işlevini kullanırız.
| 1 | =ORTA(B3,C3,D3-C3) |
Diğer Yöntem
Daha detaylı anlatmadan, aşağıdaki karmaşık formülü kullanarak bir dizgenin içinden sayıyı da alabilirsiniz.
| 1 | =TOPLA(ORTA(0&B3,BÜYÜK(INDEX(ESNUMARA)--ORTA(B3,SATIR(DOLAYLI("1:"&UZUNLUK(B3)))))*SATIR(DOLAYLI("1:"&UZUNLUK(B3)) )),0),SATIR(DOLAYLI("1:"&UZUNLUK(B3))))+1,1)*10^SATIR(DOLAYLI("1:"&UZUNLUK(B3)))/10) |
Lütfen, dizede sayı bulunmadığında yukarıdaki formülün size 0 vereceğini ve baştaki sıfırların bırakılacağını unutmayın.
Google E-Tablolarda Sayıyı Dizeden Metne Bul ve Çıkar
Yukarıda gösterilen örnekler, Google E-Tablolar'da Excel'de olduğu gibi çalışır.
