Soru Bul-Değiştir Özelliğini Formül İçerisinde Kullanma

[Hattushil]

Başlıkta da belirtiğim gibi excel'in Bul-Değiştir özelliğini formül içerisinde kullanmak istiyorum.

Excel dosyasının B2 sütünunda "Ankara Bilişim ve Güvenlik Sistemleri Uygulama Merkezi" metin var ve 45 karakteri aşıyor, bu metnin karakter sayısını düşürmek için
aynı excel dosyasının farklı bir sheetinde ya da farklı bir dosyada tanımladığım kısaltma listesi var "Ank Blşm ve Gvnlk Sistemleri Uyg Mrkz".

45 karakteri aşan metinlerde belirlediğim kısaltmaları kullan tarzında bir formül yazabilir miyim?

Umarım yapmak istediğimi anlatabilmişimdir, verdiğiniz cevaplar için şimdiden teşekkür ederim.

 

[ÖmerFaruk]

Formülle yapmak için
Formülün yazılacağı bir sütun oluşturun. Mesela C
Çalışma dosyanızın boş bir sayfasına kısaltılacak tanımları ve kısaltmaları yazın, 2 ayrı sütuna

Dosyanızın C sütununa (yeni oluşturduğunuz sütun)
=Eğer(Uzunluk(B2)>45; DüşeyAra(.........);B2

DüşeyAra kısmını oluşturduğunuz yeni kısaltma sayfasına göre yapabilirsiniz.

 

[Hattushil]

Formülle yapmak için
Formülün yazılacağı bir sütun oluşturun. Mesela C
Çalışma dosyanızın boş bir sayfasına kısaltılacak tanımları ve kısaltmaları yazın, 2 ayrı sütuna

Dosyanızın C sütununa (yeni oluşturduğunuz sütun)
=Eğer(Uzunluk(B2)>45; DüşeyAra(.........);B2

DüşeyAra kısmını oluşturduğunuz yeni kısaltma sayfasına göre yapabilirsiniz.

Teşekkür ederim,
Peki hücre içerisinde bir kelimeyi kısaltma olarak değiştirmek istersem nasıl yapabilirim?

Örneğin;
Kısaltma adlı excel dosyasına A sütununa normal kelimeyi B sütununa da kelimenin kısaltmasını girdim.
Değiştirmek istediğim excel dosyamda da B sütununda metin ve C sütununa belirtiğiniz gibi formül yazdım.

 

[ÖmerFaruk]

Bu durumda makrolarla işlem yapmak zorunda kalırız

 

[Hattushil]

Bu durumda makrolarla işlem yapmak zorunda kalırız

Örnek excel dosyası https://dosya.co/78mpjdssne8z/Kitap1.xlsx.html

Sayfa1 sheet'inde listem var ve H sütunundaki metin 45 karakterden büyük ise "=EĞER(UZUNLUK(B2)>45;(DÜŞEYARA(H:H;Kısaltma!A:B;2;1));H2)" şeklinde bir formül yazdım ama bazen metinin tamamını kısaltmak yerine metin içerisindeki kelimeyi de kısaltabiliyorum.

Bunu makro ile nasıl yaparım?

https://www.excel.web.tr/threads/bul-ve-degistir.202401/ bu konudaki gibi olacak sanırım.

 

[ÖmerFaruk]

Kod kullanacaksanız I sütununa ihtiyacınız yok.
H sütununa gerekli ifadeyi girince H sütununda direkt düzeltme yapabilirsiniz.
Kısaltma sayfanız örnek dosyanızdaki gibi olur.

Aşağıdaki ikinci satıra ihtiyacınız yok. Sadece kısaltılacak ifadeyi belirtmeniz yeterli
Ceza İnfaz Kurumu ----> CİK
İzmir Kadın Ceza İnfaz Kurumu ----> İzmir Kadın CİK

Sayfa1 in Kod penceresine aşağıdaki kodu aynen yapıştırın.
Dosyanızı Makro İçerebilen Excel dosyası olarak kaydedin.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim Sh As Worksheet
    Set Sh = Worksheets("Kısaltma")
    MyArr = Sh.Range("A2:B" & Sh.Range("A" & Rows.Count).End(3).Row).Value
    For i = 1 To UBound(MyArr)
        If InStr(1, Target, MyArr(i, 1)) > 0 Then
            Target = Replace(Target, MyArr(i, 1), MyArr(i, 2))
            GoTo SonaAtla
        End If
    Next i
SonaAtla:
    Set Sh = Nothing
    Application.EnableEvents = True
End Sub

 

[Hattushil]

Kod kullanacaksanız I sütununa ihtiyacınız yok.
H sütununa gerekli ifadeyi girince H sütununda direkt düzeltme yapabilirsiniz.
Kısaltma sayfanız örnek dosyanızdaki gibi olur.

Aşağıdaki ikinci satıra ihtiyacınız yok. Sadece kısaltılacak ifadeyi belirtmeniz yeterli
Ceza İnfaz Kurumu ----> CİK
İzmir Kadın Ceza İnfaz Kurumu ----> İzmir Kadın CİK

Sayfa1 in Kod penceresine aşağıdaki kodu aynen yapıştırın.
Dosyanızı Makro İçerebilen Excel dosyası olarak kaydedin.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim Sh As Worksheet
    Set Sh = Worksheets("Kısaltma")
    MyArr = Sh.Range("A2:B" & Sh.Range("A" & Rows.Count).End(3).Row).Value
    For i = 1 To UBound(MyArr)
        If InStr(1, Target, MyArr(i, 1)) > 0 Then
            Target = Replace(Target, MyArr(i, 1), MyArr(i, 2))
            GoTo SonaAtla
        End If
    Next i
SonaAtla:
    Set Sh = Nothing
    Application.EnableEvents = True
End Sub

Dediğiniz gibi makro olarak oluşturup kodu kopyaladım,
ama çalıştırdığımda aşağıdaki gibi hata alıyorum.

 

[ÖmerFaruk]

Benim verdiğim kodda o sarı renkteki satır yok.
Silin onu.