1. Amaç
Bazı durumlarda farklı mecralardan Excel’e aktardığınız metin değerleri birleşik olarak gelir ve ana metindeki alt metinleri ayırmak için de “-“, “/” gibi ayraç karakterleri kullanılır. Siz bu metin dizisinden en son ayraç karakterinin konumunu bularak metnin sağ tarafındaki alt metni çekmek isteyebilirsiniz. Bu durumda size kullanılan ayracın en son konumu gerekir. Bu yazımda bunu nasıl yapabileceğinizi anlatmak istiyorum.
Bu örnek için kullanacağım değerleri aşağıdaki tabloda bulabilirsiniz. Bunları kendi Excel sayfanıza yapıştırabilir veya var ise kendi değerlerinizi kullanabilirsiniz. Eğer bu değerleri kullanmak istiyorsanız ben değerleri B2 hücresinden itibaren yapıştırıyorum ve formülleri de buna göre yazacağım. Farklı bir hüceye yapıştırırsanız formülleri de buna göre güncellemeye dikkat edin.
| Tarih-MüşteriKodu-Fatura |
| 01.01.2021-10012412-1256,25 |
| 03.01.2021-10013555-5247,55 |
| 05.01.2021-10012115-11253,2 |
| 05.01.2021-10010114-10125,52 |
| 07.01.2021-10011252-8546,2 |
2. Uygulama
Burada yapmak istediğim toplamda kaç tane ayraç karakteri olduğunu bulmak, sonuncu ayraç karakteri farklı bir karakter ile değiştirmek, bu farklı karakterin hangi konumda olduğunu bulmak.
a. Kaç Ayraç Karakteri Olduğunu Bulma
Bunun için metin içindeki tüm ayraç karakterlerini metinden çıkaracağım, sonra bu metnin uzunluğunu, ilk metnin uzunluğundan çıkaracağım. Bu iki metnin uzunlukları arasındaki fark, toplam kaç tane ayraç karakteri olduğunu gösterecektir.
Ayraç karakterlerini metin içinden çıkarmak için YERİNEKOY işlevi kullanacağım:
=YERİNEKOY(B3;”-“;””)
Bu durumda metin içindeki tüm ayraç karakterleri uzunluğu sıfır olan bir metin ile değiştirilir ve elimde aşağıdaki şekilde bir tablo oluşur.
| Tarih-MüşteriKodu-Fatura |
| 01.01.2021100124121256,25 |
| 03.01.2021100135555247,55 |
| 05.01.20211001211511253,2 |
| 05.01.20211001011410125,52 |
| 07.01.2021100112528546,2 |
Şimdi bu yeni metnin uzunluğunu bulalım:
=UZUNLUK(YERİNEKOY(B3;”-“;””))
Bu uzunluk ile ilk metnimizin uzunluğu arasındaki farkı hesaplayalım:
=UZUNLUK(B3)- UZUNLUK(YERİNEKOY(B3;”-“;””))
Sonuç olarak 2 değerini bulacağız. Yani metin içinde toplamda 2 tane ayraç karakteri var.
b. Son Ayraç Karakterini Değiştirme
Toplam kaç tane ayraç karakteri olduğunu bulduğumuza göre sonrasında en son ayraç karakterini metin içinde yer almayan, benzersiz bir harf ile değiştireceğiz ki bu tek harfin konumunu bulabilelim. Yine YERİNEKOY işlevini kullanacağız ancak bu sefer kaçıncı ayracı değiştirmek istediğimizi de belirteceğiz:
=YERİNEKOY(B3;”-“;”#”; UZUNLUK(B3)- UZUNLUK(YERİNEKOY(B3;”-“;””)))
Artık elimdeki değerler aşağıdaki hale gelmiş oldu. Dikkat ederseniz son ayraç artık “-” yerine “#” karakteri.
| Tarih-MüşteriKodu-Fatura |
| 01.01.2021-10012412#1256,25 |
| 03.01.2021-10013555#5247,55 |
| 05.01.2021-10012115#11253,2 |
| 05.01.2021-10010114#10125,52 |
| 07.01.2021-10011252#8546,2 |
b. Metnin En Sağındaki Değeri Çekme
Bu son metin içinde kullandığımız özel karakterin konumunu bulmak için MBUL veya BUL komutlarını kullanabiliriz:
=BUL(“#”;YERİNEKOY(B3;”-“;”#”; UZUNLUK(B3)- UZUNLUK(YERİNEKOY(B3;”-“;””))))
=MBUL(“#”;YERİNEKOY(B3;”-“;”#”; UZUNLUK(B3)- UZUNLUK(YERİNEKOY(B3;”-“;””))))
Eğer örnek değerleri kullanırsanız bu işlevler size 20 değerini verecektir. Metnin en sağındaki değeri çekmek için ise SAĞDAN işlevini kullanacağım:
=SAĞDAN(B3;UZUNLUK(B3)- ARA(“#”;YERİNEKOY(B3;”-“;”#”; UZUNLUK(B3)- UZUNLUK(YERİNEKOY(B3;”-“;””)))))
Gördüğünüz üzere metnin sonundaki rakamları alabildik ancak bunlar metin olarak geldi. Metin olarak gelen rakamları rakama çevirmek için değer değiştirmeyen bir matematik işlemi yapabilir veya DEĞER işlevini kullanabiliriz.
=–SAĞDAN(B3;UZUNLUK(B3)- ARA(“#”;YERİNEKOY(B3;”-“;”#”; UZUNLUK(B3)- UZUNLUK(YERİNEKOY(B3;”-“;””)))))
=SAĞDAN(B3;UZUNLUK(B3)- ARA(“#”;YERİNEKOY(B3;”-“;”#”; UZUNLUK(B3)- UZUNLUK(YERİNEKOY(B3;”-“;””)))))+0
=SAĞDAN(B3;UZUNLUK(B3)- ARA(“#”;YERİNEKOY(B3;”-“;”#”; UZUNLUK(B3)- UZUNLUK(YERİNEKOY(B3;”-“;””)))))*1
Yukarıdaki işlevlerin hepsi metin olarak gelen rakamları, rakama çevirecektir.
Bu formülü aşağıya doğru uzattığımda elimde aşağıdaki tablodaki değerler olacaktır.
| Fatura |
| 1256,25 |
| 5247,55 |
| 11253,2 |
| 10125,52 |
| 8546,2 |
c. Office 365 Dizi Formülü İle
Office 365 ile gelen yeni bir özellik olan dizi formülü özelliği ile bu işlevi bir adım daha ileri taşıyabiliriz. Ben değerlerimin olduğu tabloyu bir CTRL + L ile bir Excel Tablosu haline döndüreceğim ve adını da “Faturalar” olarak değiştireceğim. Formülü de aşağıdaki gibi tablonun tüm satırlarını içerecek şekilde güncelliyorum.
=–SAĞDAN(Faturalar[Tarih-MüşteriKodu-Fatura];UZUNLUK(Faturalar[Tarih-MüşteriKodu-Fatura])-BUL(“#”;YERİNEKOY(Faturalar[Tarih-MüşteriKodu-Fatura];”-“;”#”;UZUNLUK(Faturalar[Tarih-MüşteriKodu-Fatura])-UZUNLUK(YERİNEKOY(Faturalar[Tarih-MüşteriKodu-Fatura];”-“;””)))))
Formülde bir aralık kullandığımız için Excel bu formülü dinamik dizi formülü olarak algılayacak ve tablodaki tüm değerleri alarak formül sonucunu alt alta yazacaktır.
Bunun avantajı siz tabloya yeni değerler eklediğinizde formülünüz de otomatik olarak bu yeni değerleri dikkate alarak ek bir müdahaleye gerek kalmadan yeni değerlere de formülü uygularak sonucu ilgili aralığa ekleyecektir.
