SUBSTITUTE ile Metni Değiştirme

Verilerde formüllerin içinde sık sık bulma ve değiştirme işlemi gerekir: telefon numaralarındaki tireleri kaldırmak, bir kelimeyi değiştirmek veya araya karışmış simgeleri silmek gibi.

SUBSTITUTE işlevi, bir hücredeki metnin bir bölümünü bulur ve her oluşumunu yeni metinle değiştirir; üstelik özgün hücreyi değiştirmez.

Bu işlev, Bul ve Değiştir özelliğinin formül tabanlı karşılığıdır; ancak verileriniz değiştikçe otomatik olarak güncellenir.

SUBSTITUTE en fazla dört bağımsız değişken alır:

• metin özgün dize veya hücre
• eski_metin bulunacak metin
• yeni_metin yerine konulacak metin
• oluşum_numarası (isteğe bağlı) değiştirilecek oluşum

Biçimi şöyledir: =SUBSTITUTE(text, old_text, new_text, [instance_num]). Son bağımsız değişkeni atlarsanız, tüm oluşumlar değiştirilir.

Yaygın bir yöntem, bir şeyi silmek için onu hiçbir şeyle değiştirmektir. Bunun için new_text değerini boş bir dize olan "" olarak ayarlarsınız.

A2 hücresinde 555-123-4567 varsa aşağıdaki formül tüm tireleri kaldırarak 5551234567 sonucunu döndürür.

Bu kalıp telefon numaralarını, kimlikleri ve noktalama işareti içeren diğer değerleri temizler.

SUBSTITUTE yalnızca tek tek karakterlerle değil, kelimelerin tamamıyla da çalışır.

A2 hücresinde Yıllık rapor 2023 varsa kelimeyi değiştirerek yılı güncelleyebilirsiniz:

Sonuç Yıllık rapor 2024 olur. Hücredeki eşleşen her oluşum değiştirilir.

Önemli: SUBSTITUTE, büyük/küçük harf kullanımı da dâhil olmak üzere metni tam olarak eşleştirir.

A2 hücresinde Kedi kedi CAT varsa =SUBSTITUTE(A2, "cat", "dog") yalnızca küçük harfli kedi oluşumunu değiştirir ve Kedi köpek CAT sonucunu verir.

Büyük/küçük harfe duyarsız değiştirme yapmanız gerekiyorsa önce LOWER veya UPPER ile harf kullanımını standartlaştırın ya da Sheets'teki bir sonraki derste anlatılan düzenli ifade işlevlerini kullanın.

İsteğe bağlı dördüncü bağımsız değişken olan oluşum_numarası, tüm oluşumlar yerine tek bir oluşumu hedeflemenizi sağlar.

A2 hücresinde a-b-c-d varsa ve yalnızca ikinci tireyi değiştirmek istiyorsanız oluşum_numarası değerini 2 olarak ayarlayın.

Sonuç a-b/c-d olur: yalnızca ikinci tire bölü işaretine dönüşür.

Metin olarak saklanan içe aktarılmış bir tutar düşünün: $1,250.00. Bunu kullanılabilir bir sayıya dönüştürmek için dolar işaretini ve virgülü kaldırmanız gerekir.

Her iki karakteri kaldırmak için iç içe iki SUBSTITUTE çağrısı kullanın.

İçteki çağrı virgülü, dıştaki çağrı ise dolar işaretini kaldırır ve geriye 1250.00 kalır.

Her biri farklı bir karakteri temizlemek üzere gerektiği kadar SUBSTITUTE çağrısını iç içe yerleştirebilirsiniz.

Boşluklar, noktalar ve eğik çizgiler içeren karmaşık bir kodu temiz tirelere dönüştürmek için üç değiştirme işlemini zincirleyin.

Her katman, sonucunu bir sonraki katmana aktararak son temizlenmiş dizeyi oluşturur.

SUBSTITUTE işlevini benzer REPLACE işleviyle karıştırmayın.

• SUBSTITUTE, metni içeriğini eşleştirerek değiştirir (bu kelimeyi bul ve değiştir).
• REPLACE, metni konumuna göre değiştirir (5. konumdan başlayan 4 karakteri değiştir).

Neyi bulacağınızı biliyorsanız SUBSTITUTE, nerede bulunduğunu biliyorsanız REPLACE kullanın.

Akıllıca bir ek kullanım: Bir şeyin kaç kez göründüğünü saymak için SUBSTITUTE ile LEN işlevlerini birleştirebilirsiniz.

Bir karakter kaldırıldıktan sonraki uzunluğu özgün uzunluktan çıkarın. A2 hücresinde a,b,c,d varsa bu işlem virgülleri sayarak 3 sonucunu verir.

Bu yöntem, ayraçlarla ayrılmış bir listedeki öğeleri saymak için oldukça kullanışlıdır.

A2 hücresinde 555.123 4567 gibi noktalar ve boşluklar içeren karmaşık numaraların bulunduğunu varsayalım. Bunları tutarlı bir tireli biçime, yani 555-123-4567 biçimine dönüştürmek istiyorsunuz.

Önce noktaları ve boşlukları kaldırıp ardından tireleri yeniden ekleyebilirsiniz; şimdilik ayraçları yalnızca tirelerle birörnek hâle getirin.

Değiştirme işlemlerini iç içe yerleştirmek, hangi gereksiz ayraçların bulunduğundan bağımsız olarak biçimi standartlaştırır.

SUBSTITUTE işlevini ne kadar anladığınızı sınayın.

Formüllerde metin bulup değiştirmeyi öğrendiniz:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Karakterleri silmek için new_text olarak "" kullanın.
• İşlev büyük/küçük harfe duyarlıdır; gerekirse önce harf kullanımını standartlaştırın.
• İsteğe bağlı instance_num tek bir oluşumu hedefler.
• Birden çok karakteri temizlemek için çağrıları iç içe yerleştirin; oluşumları saymak için LEN ile birlikte kullanın.

Metinleri Birleştirme ve Değiştirme bölümünü tamamladınız. Temiz ve tutarlı veriler oluşturma konusunda harika bir iş çıkardınız!