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.

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , , ,
  1. HOCAM EMEĞİNİZE SAĞLIK BU ÖRNEĞİ DEFALARCA YAPMAMA RAĞMEN OLMUYOR BU ÖRNEĞİ MAİL ADRESİME GÖNDEREBİLİRMİSİNİZ

    • Merhaba,

      Formül içindeki ARA yerine BUL işlevini kullanırsanız sorun çözülecektir:
      =–SAĞDAN(B3;UZUNLUK(B3)-BUL(“#”;YERİNEKOY(B3;”-“;”#”; UZUNLUK(B3)- UZUNLUK(YERİNEKOY(B3;”-“;””)))))

Leave a Reply