Metinsel Formüller

Excel her ne kadar sayılarla uğraşan bir program görüntüsü verse de, çok sayıda metin manipülasyonu da yapmamız gerekecektir. Excel'in bu konuda oldukça büyük bir metin fonksiyon kütüphanesi var. Bazen bunları tekli bazen de içiçe birkaçını birden kullanmak gerekecektir. Hatta bazı formüller tahmininizeden çok daha uzun olabilecektir.
Bununla birlikte bazı durumlarda Excelin metin formüllkerinin yetersiz kaldığını göreceksiniz. Veya yazdığınız formüller aşırı uzun olacak, bunları her ihtiyaç duyduğunuzda böyle uzun uzun yazmak çok zor olabilecektir. İşte böyle durumlarda UDF oluşturma zamanı gelmiştir. Ancak siz yine de uzun formüllerle başa çıkmak için çeşitli pratik yöntemleri kullanabilirsiniz.
Son olarak, bazı durumlarda formül yazmak yerine Filling(Doldurma) işlemi yapmanız da sizi uzun formüllerden kurtarabilir.
Şimdi bu kategorideki önemli formüllere bakalım. Fonksiyonları belirli alt kategoriler altında topladım, buna göre ilerleyeceğiz.

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 255'e 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-255'in dışındaki sayılarla da çalışmasını sağlayacak şekilde genişletilmiş halleridir. Kendiniz deneyip görebilirsiniz. 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.

                        
                =UPPER("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ğiniz Format): Elinizdeki metni daha okunaklı ve anlaşılır hale getirmek için belirli bir formata sokmaya yarar. Özellikle başka formüllerle bir arada 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 bir arada 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. Örneğin: 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 ettiririz. 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." 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ğimizi buluyoruz, REPT ile bu kadar 0 üretiyoruz, ve son olarak da bunu metnin kendisi ile birleştiriyoruz.

Bir hücredeki kelimeleri saymak: İzlenecek yol: Hücredeki toplam karakter sayısını LEN ile bulalım, buna X diyelim. Sonra hücredeki boşlukları SUBSTITUTE ile yok edip kalan kısmın uzunluğunu bulalım, buna da Y diyelim. X-Y+1 aradığımız sorunun cevabı olacaktır.

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

Bir hücredeki metinden son kelimeyi almak: İzlenecek yol: Hücredeki bütün boşlukları 30 adet _ ile değiştiriyoruz. Buna X diyelim. X'in sağdan 30 karakterini alalım, buna Y diyelim. Sonra da _ karakterlerini "" değiştirelim yani bunları uçuralım.

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

Aşama aşama bakalım: Hücre içeriği şu olsun: Mustafa Kemal Atatürk

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

Neden 30 sayısını kullandık. Çünkü bir kelimenin 30 harften daha uzun olacağını sanmıyorum. Sizin elinizde daha uzun kelimelerden oluşan bir liste varsa 30 yerine 50 veya 100 de kullanabilirsiniz.

Bir hücredeki metinden n. kelimeyi almak: İzlenecek yol: Hücredeki bütün boşlukları 100 adet _ ile değiştiriyoruz. Buna X diyelim. X'in ortadan (n-1)*100+1. karakterinden seçmeye başlayıp 100 karakter alalım, buna Y diyelim. Sonra da _ karakterlerini "" değiştirelim yani bunları uçuralım.

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

Aşama aşama bakalım: Hücre içeriği yine aynı olsun: Mustafa Kemal Atatürk. Bu sefer ikinci kelimeyi seçeceğiz. (2-1)*100+1 = 101. karakterden başlayıp 100 karakter seçeceğiz ve ilerleyeceğiz.

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

Burada 100'ü kullanma sebebim, n'in değerine göre kolay çarpım yapma isteğimdendir.

Gerek bunu gerek bir üsttekini, en dıştaki SUBSTITUTE yerine TRIM ile de yapabilirdik, tabi _ yerine boşluk kullanarak. Sadece son örneği yapalım.

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

Bir hücredeki metinden ilk n kelimeyi almak: İzlenecek yol: Önceki yöntemlere benzer olarak, hücredeki bütün boşlukları 100 adet _ ile değiştiriyoruz. Buna X diyelim. X'in soldan n*100. karakterini alalım, buna Y diyelim. Sonra da Y içindeki 100 tane _ karakterini "" ile değiştirelim yani bunları uçuralım, yeni değerimiz Z olsun. Z içinde de en sonda kalan 100'den az sayıdaki _ işaretlerini uçuralım.

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

Aşama aşama bakalım: Hücre içeriği şu olsun: Batı Karadeniz Bölge Müdürlüğü. İlk 2 kelimeyi seçeceğiz. Boşlukların 100 adet _'e çevrildiği metinden 2*100 = 200 karakteri soldan kesip ilerleyeceğiz.

X = Batı____________________________________________________________________________________________________karedeniz____________________________________________________________________________________________________bölge____________________________________________________________________________________________________müdürlük
Y = Batı____________________________________________________________________________________________________karedeniz
Z = Batı Karadeniz
Sonuç = Batı Karadeniz

Farkettiniz mi, bu sefer formül iyice komplike oldu. Bu noktada gerçekten UDF kullanmak en iyi çözüm olacaktır. Aşağıda gördüğünüz gibi oldukça basit bir kullanımı var. Daha önce belirttiğim gibi Microsoft bu tür fonksiyonları neden hala metin fonksiyonları içine almıyor, anlamış değilim. Neyse ki UDF teknolojisi var.

                        
                =ilknkelime(A1;2)

İlgili UDF de aşağıdaki gibi yazılabilir. Detaylarını burada girmiyorum, gerek VBA temellerini, gerek dizileri gerek fonksiyon konusunu bilmeniz gerekiyor. Bu detayları ilgili VBA sayfalarında bulabilirsiniz.

                        
                Function ilknkelime(hucre As Range, kaç As Byte, Optional ayrac As String = " ")
                    'normal bir cümlede ayrac boşluk olacağı 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: Bir önceki örneğin neredeyse aynısı mantıkta hazırlanır. Sadece soldan n*100 yerine sağdan n*100 _ işareti alınır.

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

Aşama aşama bakalım: Hücre içeriği yine aynı olsun: Batı Karadeniz Bölge Müdürlüğü. Sondan 2 kelimeyi seçeceğiz. Boşlukların 100 adet _'e çevrildiği metinden 1*100 = 100 karakteri sağdan kesip ilerleyeceğiz.

X = Batı____________________________________________________________________________________________________karedeniz____________________________________________________________________________________________________bölge____________________________________________________________________________________________________müdürlük
Y = ______________________________________________________________________________________________bölge____________________________________________________________________________________________________müdürlük
Z = ______________________________________________________________________________________________bölge Müdürlük
Sonuç = Bölge Müdürlük

Bir hücredeki metinden sondan x kelime hariç almak: Bu örnekte formül biraz daha uzayacak. En azından benim aklıma gelen yöntem bu olmuştur. Daha kısa yazılabilir mi bilmiyorum, üzerine eğilmek gerekir ancak kesinlikle UDF kullanmak en akıl karı iş olacaktır. Biz yine de Excel içindeki yerleşik fonksiyonlarla halletmeye çalışalım.

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

Aşama aşama bakalım: Hücre içeriği yine aynı olsun: Batı Karadeniz Bölge Müdürlüğü.

X = Batı____________________________________________________________________________________________________karedeniz____________________________________________________________________________________________________bölge____________________________________________________________________________________________________müdürlük
U = 321
Y = Batı____________________________________________________________________________________________________karedeniz________
Z = Batı Karadeniz________
Sonuç = Batı Karadeniz

Yukarıda belirttiğim gibi formül daha da komplike oldu. Bunun için yazacağımız UDF ise aşağıdaki gibi olacaktır.

                        
                =sonxkelimehariç(A1;2)

                        
                Function sonxkelimehariç(hucre As Range, kaç As Byte, Optional ayrac As String = " ")
                    'normal bir cümlede ayrac boşluk olacağı 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: "-" karakterini sayacağız.

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

Hem 1 hem 2 isimli kişilerin olduğu listelerle uğraşmak: Diyelim ki, 2 isimli kişilerin ikinci ismini, 1 isimlilerin ise doğal olarak ilk ismini alacaksınız.

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

                        
                =IF(B1=2;LEFT(A1;FIND(" ";A1)-1);TRIM(MID(SUBSTITUTE(A1;" ";REPT(" ";100));100;100)))