Metinsel Formüller

TRIM(Metin):Trim, bir hücredeki kelimeler arasındaki tekil boşluklar dışındaki tüm boşlukları temizler. Kelimeler arasında birden fazla boşluk varsa bunları da 1e indirir. A2 hücresinde "   Volkan    Yurtseven        " yazdığını düşünelim.

=TRIM(A2) //Volkan Yurtseven

CLEAN(Metin): Clean, ekran görünmeyen ilk 32 ASCII karakteri temizler. Mesela Enter'ın kodu 10 olup bunu temizler. TRIM'le birlikte kullanıldığında temizlenecek boşluk benzeri karakter sayısı daha çoğalmış olur.

=CLEAN(TRIM(A2))

LEFT(Metin,kesilecek karakter sayısı):LEFT, bir hücrenin içindeki metinin solundan, belirtilen adette karakteri keser. Kaç karakter kesmeniz gerektiğini bilmediğiniz bazı durumlarda FIND, SEARCH, LEN gibi diğer fonksiyonlardan yararlanabilrsiniz. A2 hücresinde "TR123456" gibi, ilk iki hanesi ülke kodu olan metinler olduğunu düşünelim. Ülke kodunu almak için soldaki 2 haneyi keseriz.

=LEFT(A2;2) //TR

Mesela, bir hücre grubunda isim ve soyisimler var diyelim, siz ilk ismi almak istiyorsunuz, aşağıdaki gibi bir formül yazabilirsiniz. A2 hücresinde "Volkan Yurtseven" yazdığını düşünelim.(FIND fonksiyonunu biraz aşağıda göreceğiz.)

=LEFT(A2;FIND(" ";A2)-1) //Volkan

RIGHT(Metin,kesilecek karakter sayısı):RIGHT, bir hücrenin içindeki metinin sağından, belirtilen adette karakteri keser. Yine, kaç karakter kesmeniz gerektiğini bazı durumlarda FIND, SEARCH, LEN gibi diğer fonksiyonlardan yararlanabilrsiniz. A2 hücresinde "TRM456" yazdığını düşünelim.sadece sağdaki 3 hane olan sayıları almak istiyoruz.

=RIGHT(A2;3) //456

Yine bir hücre grubunda isim ve soyisimler var diyelim, siz soyismi almak istiyorsunuz, Bunu yapmak LEFT ile isim almak kadar kolay değil, çünkü 2 isimli kişiler işleri zorlaştırmaktadır. Buna ait örneği en alttaki Çeşitli Örnekler bölümünde bulabilirsiniz. Biz yine de sadece bir isimli kişilerin olduğu bir listede bunu nasıl yapabiliriz, buna bi bakalım. A2 hücresinde "Volkan Yurtseven" yazdığını düşünelim.

=RIGHT(A2;LEN(A2)-FIND(" ";A2)) //Yurtseven

MID(Metin,kesmeye başlanacak yer,kesilecek karakter sayısı):MID bir hücrenin içindeki metinin ortasından bir yerden belirtilen adette karakteri keser. Nereden başlayacağınızı ve kaç karakter kesmeniz gerektiğini bilmediğiniz bazı durumlarda FIND, SEARCH, LEN gibi diğer fonksiyonlardan yararlanabilrsiniz.

Mesela elinizde 25 haneli hesap numaraları var diyelim. Bunların 12 ile 15 arasındaki karakterleri "şube kodu" olsun. Şube kodunu buradan almak için 12'den başlayıp, 4 karakter almamız gerekir.

=MID(A2;12;4)

FIND(bulunacak metin, neyin içinde,[nerden başlanacak]):FIND, bir metin içinde başka bir metin/karakter arayıp onu bulduğu yerin konumunu(kaçında karakter olduğunu) gösterir. Aranan karakterden çok sayıda bulunsa bile ilk bulunduğu yerin konumu gelir. Üçüncü parametre opsiyonel olup default(varsayılan) değeri 1'dir. Bu fonksiyon, büyük küçük harf ayrımına duyarlıdır. Aşağıdaki örnekte, hücredeki ilk boşluk karakterinin konumu gelir.

=FIND(" ";A2)

Bu yöntem, her hücrenin ikinci kelimesini bulmak için kullanılabilir. Bunun için üçüncü parametre olan arama pozisyonunu, ilk boşluğu bulduğumuz yer + 1 şeklinde belirleyeriz. Ancak sonrasında kaç karakter keseceğimizin cevabı biraz daha karışıktır. Önce formüle bakalım, sonra nasıl işlediğine.

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

İlk parametre basit, bunu geçelim. İkinci parametreyi FIND(" ";A2)+1 diyerek belirledik. Son parametre FIND(" ";A2;FIND(" ";A2)+1)-FIND(" ";A2)-1 formülüyle belirledik. Bunu da parçalara ayıralım. Öncelikle ikinci boşluk karakterini bulmamız lazım, ikinci boşluğu bulmak için de aramaya ilk boşluğu bulduğumuz konumdan sonra başlamalıyız. ilk boşluğu nasıl bulmuştuk, FIND(" ";A2) ile, buna 1 ekliyoruz. Şimdi elimizde ikinci boşluğu bulduğumuz FIND(" ";A2;FIND(" ";A2)+1) formülü var. Ama bu kadar karakter kesersek beklenmeyen bir sonuç olur. O yüzden son olarak, ilk boşluğun konumu bulduğumuz formülü de son formülümüzden çıkarmamız lazım: FIND(" ";A2;FIND(" ";A2)+1)-FIND(" ";A2)-1

Böyle uzun formüllerde F9 tuşu ile formülün hangi parçasının ne döndürdüğünü görmek oldukça pratiklik sağlamaktadır. Bu özelliği sık sık kullanmanızı tavsiye ederim.

Şimdi diyeceksiniz ki, ikinci kelimeyi bulduk da, üçüncü/dördüncü/v.s kelimeleri nasıl buluruz? Böyle sürekli 3.boşluğu, 4.boşluğu bul, öçncekinin konumunda çıkar vs. yoluna girersek formülümüz saçma derecede uzar ve pratik bir yöntem de olmaz. Bunu nasıl yapacağımızı, Çeşitli Örnekler bölümünde göreceğiz. Bir dieğr altenratif de UDF kullanmak veya Excelin sonraki sürümlerini beklemek olacaktır. Ben hala kelime sayma, kelime seçme gibi temel formüllerin 2016 sürümünde bile olmamasını hayretle karşılıyorum. Neyseki çözümsüz değiliz, UDF'ler bize bu konularda büyük kolaylıklar sağlıyor.

SEARCH(bulunacak metin, neyin içinde,[nerden başlanacak]):FIND gibi çalışır. FIND'dan farkı küçük-büyük harf duyarlılığı yoktur, ayrıca joker karakterleri(? ve *) kullanmanıza izin verir.

Bu fonksiyon, filtrelemeye alternatif olarak kullanılabilir ve Filtrelemeden daha esnektir. Mesela aşağıdaki formülle bir hücrede Volkan kelimesi var mı diye bakıyorum.

=ISNUMBER(SEARCH("Volkan";A2))

Bu da FIND gibi başka fonksiyonlarla kombine bir şekilde kullanılır.

REPLACE(değişiklik yapılacak metin, kaçıncı karakterden başlanacak, kaç karakter değişecek, neyle değiştirilecek):Konum bilgilerinden yararlanmayı gerektiren bu fonksiyon ile bir metin içinde belirli bir karakteri veya karakter grubunu başka bir karakter veya karakter grubu ile yer değiştirtiriz. Değiştirilen karakter ile yerine konan karakterler aynı uzunlukta olmak zorunda değildir. Mesela bir hücredeki ilk 10 karakteri sadece "?" karakteri ile değiştirebilirsiniz. Örneği de şöyle olacaktır. Metnimiz bir kredi kartı numarası olsun: 1111 2222 3333 4444

=REPLACE(A2;1;10;"?") //?3333 4444

FIND ve REPLACE sıkça beraber kullanılır. Nereden başlanacak ve kaç karakter değiştirilecek gibi soruları bulurken FIND fonksiyonundan faydalanırız. Mesela bir hücre grubu içinde - işareti olan yere kadarki tüm metni silmek istesek ne yapardık? Yolumuz şu olurdu:"-" işaretinin konumunu FIND ile bul, 1'den başlayıp bu konuma kadar olan tüm karakterleri "" ile yer değiştir(Bir metni "" ile yer değiştirmek onu silmektir). Metnimiz "Fatih-İstanbul" olsun.

=REPLACE(A2;1;FIND("-";A2);"") //İstanbul
//Tam tersi işi yapmak içinse - işaretini bulduğumuz yerden başlayıp, 'metnin tüm uzunluğu - işaret konumu+1' kadar gidip bunları yokederiz
=REPLACE(A2;FIND("-";A2);LEN(A2)-FIND("-";A2)+1;"")//Fatih

Konumdan ziyade doğrudan içerik değiştirmekle ilgileniyorsak bir alttaki fonksiyona bakmamız gerekir.

SUBSTITUTE(değişiklik yapılacak metin, neyi değiştireceğiz, neyle değiştireceğiz, [kaçıncı]):Bu fonksiyon ile bir metindeki belirli karakterleri(veya metinleri) başka karakterlerle değiştiriyoruz. Son parametre seçimlik olup kaçıncı eşleşmeden sonra değişklik yapılması gerektiğini belirtmiş olursunuz, belirtmezseniz tüm eşleşmeler için işlem yapılır. Basit bir örneğe bakalım.

=SUBSTITUTE(A2;"ı";"i") //Tüm ı'lar i yapılır
=SUBSTITUTE(A2;"0";"",2) //İlk sıfır dışındaki tüm sıfırları yok eder.

NOT:Bu fonksiyon küçük/büyük harf ayrımına duyarlıdır.

Yukarda CLEAN ve TRIM fronkisyonlarının birlikte kullanımı durumunda boşluk ve ilk 31 ascii kodunsa sahip görünmez karaterleri silebildiğini söylemiştik. Ancak görünmeyen başka karakterler de vardır ve bunlar için SUBSTITUTE'lu formül veya UDF yazmak gerekir. Mesela birçok durumda karşınıza çıkabilecek olan non-breaking-space karakterinin kodu 160 olup bunu CLEAN ve TRIM ile yokedemezsiniz, SUBSTITUTE ile yoketmeniz gerekir. Tüm böyle gereksiz karakterleri yoketmek için açağıdaki gibi bir formül yazılabilir.

=TRIM(CLEAN(SUBSTITUTE(A2;CHAR(160);"")))

CHAR(Sayı):Kendisine verilen sayı parametresi ile ilgili Ascii karakterlerini döndürür. Sayı 1-255 arası değer alabilir. Özellikle formüller içinde Enter veya tırnak kullanmak için kullanılır. Özellikle bir hücreden mail body'si okurken paragraflar arasına Enter koymak amacıyla kullanılır.

=CHAR(10) //Enter görevi görür. 
=CHAR(34) //Tırnak işaretidir.

A kolonunda A1 hücresinden başlayıp aşağı doğru 1'den 255e kadar sayıları yazıp, yanlarına bu formülü yazarak hangi sayı ile hangi kodun döndüğünü görebilirsiniz.

CODE(Karakter):CHAR'ın tersi gibi çalışır

=CODE("@") //64 döndürür

UNICODE ve UNICHAR(2013):CHAR ve CODE'un 1-255in dışındaki sayılarla da çalışmasını sağlayacak şekilde genişletilmiş halleridir. Kendiniz deneyip görebilrsiniz. Bunlarla her tür karakteri basabilir, veya ilgili karakterin kodunu elde edebilirsiniz.

LOWER(Metin):Verilen metnin tüm harflerini küçük harf yapar.

=LOWER("MERHABA VOLKAN") //merhaba volkan

UPPER(Metin):Verilen metnin tüm harflerini büyük harf yapar.

=LOWER("merhaba volkan") //MERHABA VOLKAN

PROPER(Metin):Verilen metnin içindeki kelimelerin sadece ilk harflerini büyük, diğerlerini küçük harf yapar

=PROPER("MERHABA VOLKAN") //Merhaba Volkan

TEXT(Metin,DönüştürmekİstediğinizFormat):Elinizdeki metni daha okunaklı ve anlaşılır hale getirmek için belirli bir formata sokmaya yarar. Özellikle başka formüllerle birarada kullanıldığında anlamlıdır.

Mesela aşağıdaki resimde görüldüğü üzere, A1 hücresinde =TODAY() formülü var, yani dosyayı açtığımızda değişen bir tarih içeriği ile karşı karşıyayız. Biz bu tarihi B2 hücresinde kullanmak istiyoruz, "XXX tarihi Performans Raporu" diye de raporu bastıracağız. Formülü aşağıdaki gibi yazarsak;

=A1 & " Performans Raporu"

görüntüsü şöyle olur, ki bunu istemeyiz. O yüzden bu tarihi formatlamamız gerekir. İşte burada TEXT formülü devreye girer.

=TEXT(A1;"dd.mm.yyyy") & " Performans Raporu"

Sonuç istediğimiz gibi olacaktır:

Burada tarih formatlarını nasıl kullanacağınızla ilgili detay bilgi bulunmaktadır. (Bölgesel ayarlara göre dd.mm.yyyy veya gg.aa.yyyy şeklide bir pattern girilmesi gerekebilir. İlki İngilizce day day.month month.year year year year kelimelerinin ilk harfleri; ikincisi Türkçe gün gün.ay ay.yıl yıl yıl yıl kelimelerinin ilk harfleridir)

Örnek 2:Şube isimleriyle şubelerin Hedef Gerçekleştirme(HG%) oranlarını birleştirmek istiyorsunuz diyelim. A kolonunda şube isimleri var, B kolonunda HG% oranları, ama HG% oranları şu şekilde: 0,98745210023. Siz mesela bu değeri %98,7 görmek istiyorsunuz. Formülümüz şöyle olacaktır

=A1&"-"&TEXT(B1;"%0,0")

Burada da sayı formatlarıyla ilgili detaylı bilgi bulunmaktadır.

VALUE(Metin/Tarih):Parametre olarak verilen ifade sayıya çevrilebilecek bir metinse veya tarihse bunu sayıya çevirir. Özellikle başka bir veri kaynağından gelen datalarda sayılar bazen metin formatında sola dayalı şekilde gelir. Bunları sayıya çevirmede kullanılabilir.

=VALUE("500") //500

CONCAT(Alan)-(2016):Belirli bir alandaki hücre içeriklerini aralarında bir ayraç olmadan birleştirir.

=CONCAT(A2:C2)

CONCATENATE(Metin1,Metin2,....):Parametre olarak girilen metinleri birleştirir. CONCAT'tan farklı olarak metinler/hücreler tek tek girilir. Ayraç belirtmek istenirse de her metin arasına tek tek ayraçlar da girilmek zorundadır.

=CONCATENATE(A2;" ";B2;" ";C2)

Bu yöntem, ilgili metinleri & işareti ile birleştirmeye benzer.

=A2&" "&B2&" "&C2)

TEXTJOIN(Ayraç,BoşHücrelerDikkateAlınsınmı,Alan)-(2016):Belirli bir alandaki hücre içerikleri, aralarında belirlenen ayraç olacak şekilde birleştirir. Boş hücreler dikkate alınsın mı, alınmasın mı, bunun için de TRUE/FALSE şeklinde bir parametre girilir.

=TEXTJOIN(" ",TRUE,A2:C2

EXACT() :İki hücrenin aynı olup olmadığını test eder. Bu kontrolü yaparken küçük/büyük harf ayrımını da dikkate alır. Mesela A1'de Volkan ve B1'de VOLKAN yazıyor olsun.

=A1=B1 //TRUE döndürürken
=EXACT(A1;B1) //FALSE döndürür

Bu fonksiyon, başka fonksiyonlarla birarada kullanılabilir ve bu şekilde kullanımı daha yaygındır.

=INDEX(Z:Z;MATCH(EXACT(A2);Y:Y;0))

LEN(Metin):Bir hücredeki metnin karakter sayısını verir. Ör:A kolonundaki metinlerden sadece 5 haneli olanları seçmek istiyoruz diyelim, şu formülü yazıp en sona kadar aşağı çekelim ve sonra da 5 olanları filtreleyelim.

=LEN(A2)

Bu formül de kendi başına kullanımdan ziyade, aşağıdaki Çeşitli Örnekler bölümünde göreceğiniz üzere diğer fonksiyonlarla birlikte daha çok kullanılır.

REPT(metin,tekrarsayısı): Bu fonksiyon ile belirli bir metni(genelde tek bir karakter) belirtilen sayıda tekrar ettiriz. Bu fonksiyon da genelde başka fonksiyonlarla birlikte kullanılır.

Diyelim ki elinizde farklı uzunluklarda müşteri numaraları var, 2 haneden 8 haneye kadar değişkenlik gösteriyor. BT ekibinize bu müşteri numaralarını içeren bir liste göndereceksiniz, ancak BT ekibi diyor ki "bana bu müşteri numaralarını 10 haneli gönder. Başlarında 0 olsun(BT ekipleri bu tür taleplerle gelirken genelde sayısal verilerin önünde 0, metinsel verilerin önünde/sonunda boşluk isterler). Bu durumda formülümüz şöyle olacak:

=REPT(0;10-LEN(A2)) & A2

Önce LEN(A2) ile kaç karakter olduğunu buluyoruz. Sonra bunu 10'dan çıkararak kaç tane 0 ekleyeceğimzi buluyoruz, REPT ile bu kadar 0 üretiyoruz, ve son olarak da bunu metnin kendisi ile birleştiriyoruz.

Bir hücredeki kelimeleri saymak

=LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))+1

Bir hücredeki metinden son kelimeyi almak

=SUBSTITUTE(RIGHT(SUBSTITUTE(A1;" ";REPT("_";30));30);"_";"")

X=Mustafa______________________________Kemal______________________________Atatürk
Y=_______________________Atatürk
Sonuç=Atatürk

Bir hücredeki metinden n. kelimeyi almak

=SUBSTITUTE(MID(SUBSTITUTE(A1;" ";REPT("_";100));101;100);"_";"")

X=Mustafa____________________________________________________________________________________________________Kemal____________________________________________________________________________________________________Atatürk
Y=_____________Atatürk
Sonuç=Atatürk

=TRIM(MID(SUBSTITUTE(A1;" ";REPT(" ";100));101;100))

Bir hücredeki metinden ilk n kelimeyi almak

=SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(A1;" ";REPT("_";100));200);REPT("_";100);" ");"_";"")

X=Batı____________________________________________________________________________________________________karedeniz____________________________________________________________________________________________________bölge____________________________________________________________________________________________________müdürlüğü
Y=Batı____________________________________________________________________________________________________karedeniz_______________________________________________________________________________________
Z=Batı karedeniz_______________________________________________________________________________________ //bunda 87 _ işareti var
Sonuç=Batı karadeniz

=ilknkelime(A1;2)

Function ilknkelime(hucre As Range, kaç As Byte, Optional ayrac As String = " ")
    'normal bir cümlede ayrac boşluk olacğaı için ayracı girmene gerek yok, zaten default olarak " " atadım.
    'ama atıyroum içeriği / ile ayrılmış bir hücre varsa 3.parametreyi / olarak girersin
    Dim kelimeler As Variant
    Dim i As Byte
    
    kelimeler = Split(hucre.Value2, ayrac)
    
    For i = 0 To kaç - 1
        geçici = geçici & kelimeler(i) & ayrac
    Next i
    
    ilknkelime = Mid(geçici, 1, Len(geçici) - 1)
    
End Function

Bir hücredeki metinden sondan x kelime almak

=SUBSTITUTE(SUBSTITUTE(RIGHT(SUBSTITUTE(A1;" ";REPT("_";100));200);REPT("_";100);" ");"_";"")

X=Batı____________________________________________________________________________________________________karedeniz____________________________________________________________________________________________________bölge____________________________________________________________________________________________________müdürlüğü
Y=____________________________________________________________________________________________bölge____________________________________________________________________________________________________müdürlüğü
Z=____________________________________________________________________________________________bölge müdürlüğü
Sonuç=bölge müdürlüğü

Bir hücredeki metinden sondan x kelime hariç almak

=SUBSTITUTE(SUBSTITUTE(REPLACE(SUBSTITUTE(A1;" ";REPT("_";100));LEN(SUBSTITUTE(A1;" ";REPT("_";100)))-200+1;200;"");REPT("_";100);" ");"_";"")

X=Batı____________________________________________________________________________________________________karedeniz____________________________________________________________________________________________________bölge____________________________________________________________________________________________________müdürlüğü
U=321
Y=Batı____________________________________________________________________________________________________karedeniz________
Z=Batı karedeniz________
Sonuç=Batı karedeniz

=sonxkelimehariç(A1;2)

Function sonxkelimehariç(hucre As Range, kaç As Byte, Optional ayrac As String = " ")
    'normal bir cümlede ayrac boşluk olacğaı için ayracı girmene gerek yok, zaten default olarak " " atadım.
    'ama atıyroum içeriği / ile ayrılmış bir hücre varsa 3.parametreyi / olarak girersin
    Dim kelimeler As Variant
    Dim i As Byte
    
    kelimeler = Split(hucre.Value2, ayrac)
    
    For i = 0 To UBound(kelimeler) - kaç
        geçici = geçici & kelimeler(i) & ayrac
    Next i
    
    sonxkelimehariç = Mid(geçici, 1, Len(geçici) - 1)
        
End Function

Bir hücredeki belirli bir karakterden kaç tane geçtiğini bulmak

X=LEN(A1)
Y=LEN(SUBSTITUTE(A1;"-";""))
Çözüm=X-Y

=LEN(A1)-LEN(SUBSTITUTE(A1;"-";""))

Hem 1 hem 2 isimli kişilerin olduğu listelerle uğraşmak

İlk formülümüz B1de olsun:
=LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))+1

İkinci formülümüz C1'de olsun:
=IF(B1=2;LEFT(A1;FIND(" ";A1)-1);TRIM(MID(SUBSTITUTE(A1;" ";REPT(" ";100));100;100)))