Excel’de Yerinekoy Formülü ve Değiştir Formülü işe Metin Değiştirmenin 2 Farklı Yolu6 min read

Bu eğitimde, excel değiştir ve yerinekoy formülü kullanım örnekleriyle açıklanmaktadır. Metin dizeleri, sayılar ve tarihlerle DEĞİŞTİR işlevinin nasıl kullanılacağını ve bir formül içinde birkaç değiştir veya yerinekoy formülünün nasıl yerleştirileceğini görün.

Bugün, bir hücredeki metni konumuna göre değiştirmek veya içeriğe göre bir metin dizesini başka bir metin dizesiyle değiştirmek için diğer iki işleve daha derinlemesine bakacağız.

Excel dosyanızda verilerde sonradan bir metni topluca değiştirmek isteyebilirsiniz. Bunun için ilk aklınıza gelen Bul ve Değiştir fonksiyonu olur. İki formül daha var ki bunlar yerinekoy formülü ve değiştir formülü. Bu iki formül CTRL + H ile yapılan değiştirme işlemlerinden farklı kabiliyetlere sahip.

Örnek çalışma dosyasını buradan indirebilirsiniz.

Örnek;

Aşağıdaki metinde 3 tane kaya var.

“Osman Kaya, arabayla gittiği kayalık bölgede bir kayanın yuvarlanmasına şahit oldu.”

Bu metinde “Kaya” kelimesini değiştirmek istedik ve 3 çeşit değiştirme fonksiyonu kullanabilirsiniz.

CTRL + H ile; tüm “Kaya” kelimelerini değiştirebilirsiniz.

Yerinekoy formülü ile bulduğun ilk veya ikinci veya üçüncü “Kaya” kelimesini değiştirebiliriz. Bu yerinekoy formülü aynı kelimenin birden çok kez geçtiği yerlerde sadece birini değiştirmek için oldukça kullanışlıdır.

Değiştir formülü ise 7. karakterden başlayıp 4 karakteri “Yılmaz” ile değiştir derseniz sadece o karakterler arasında değişim yapar.

Değiştir formülü kullanımı

Bir tablo düşünün, içindeki tarihler 2012 yazılacak yerde hatalı yazılmış ve 1912 diye yazılmış. Tüm 19 ları 20 olarak değiştireceğiz. Fakat CTRL+ H ile yapamadığınız şey sadece istediğiniz alandaki 19 ları 20 yapmak. Örneğin günlerde de 19 olduğundan CTRL+H ile değiştirsek gün ve yıldaki tüm 19lar 20ye dönüşecek. Biz sadece yıldaki 19 ları değiştirmek istiyoruz.

19 ların karakter sırasını bildiğimizden Değiştir formülü ile 7. karakterden başlayarak 2 karakteri 20 ye çevir diyebiliriz. Kullanacağımız formül aşağıdaki gibi olacaktır.

=DEĞİŞTİR(A2;7;2;”20″)

Tümüne uyguladığınızda sadece yıl bölümündeki 19 lar 20 olacaktır.

Yerinekoy formülü ve değiştir formülü ile ilgili diğer detaylar

Değiştir formülü

Excel’deki DEĞİŞTİR formülü, bir metin dizesindeki bir veya birkaç karakteri başka bir karakterle veya bir karakter kümesiyle değiştirmenize olanak tanır.

Gördüğünüz gibi, Excel DEĞİŞTİR işlevinin tümü gerekli olan 4 bağımsız değişkeni vardır.

DEĞİŞTİR(eski_metin,başlangıç_sayısı,sayı_karakterler,yeni_metin)

Eski_metin     bazı karakterlerini değiştirmek istediğiniz metindir.

Başlangıç_sayısı     eski metinde, yeni metinle değiştirmek istediğiniz karakterin konumudur.

Sayı_karakterler     eski metindeki, DEĞİŞTİR işlevinin yeni metinle değiştirmesini istediğiniz karakter sayısıdır.

Yeni_metin     eski_metindeki karakterlerin yerini alacak metindir.

Değiştir formülünü rakamlarla kullanma

Excel’deki DEĞİŞTİR formülü metin dizeleriyle çalışmak üzere tasarlanmıştır. Elbette, bir metin dizesinin parçası olan sayısal karakterleri değiştirmek için kullanabilirsiniz, örneğin:

=DEĞİŞTİR(A2, 7, 4, “2016”)

Metin değerlerinde genellikle yaptığınız gibi “2016” ifadesini çift tırnak içine aldığımıza dikkat edin.

Benzer şekilde, bir sayı içindeki bir veya daha fazla rakamı değiştirebilirsiniz. Örneğin:

=DEĞİŞTİR(A4, 4, 4,”6″)

Ve yine, değiştirme değerini çift tırnak içine almanız gerekir (“6”).

Not. Bir Excel DEĞİŞTİR formülü her zaman bir metin dizesi döndürür, sayı değil.

Excel DEĞİŞTİR formülünü tarihlerle kullanma

Az önce gördüğünüz gibi, DEĞİŞTİR formülü bir metin dizesi döndürmesi dışında sayılarla iyi çalışır 🙂 Dahili Excel sisteminde tarihlerin sayı olarak saklandığını hatırlayarak, tarihler üzerinde bazı Değiştir formülleri kullanmayı deneyebilirsiniz. Sonuçlar oldukça utanç verici olacaktır.

Örneğin, A2’de 1-Ekim-24 gibi bir tarihiniz var ve “Ekim “i “Kasım” olarak değiştirmek istiyorsunuz. Böylece, Excel’e A2 hücrelerindeki 3 karakteri 4. karakterden başlayarak değiştirmesini söyleyen DEĞİŞTİR(A2, 4, 3, “Kasım”) formülünü yazarsınız… ve aşağıdaki sonucu alırsınız:

Neden peki? Çünkü “01-Ekim-24”, tarihi temsil eden temel seri numarasının (45566) yalnızca görsel bir temsilidir. Dolayısıyla, Değiştir formülümüz yukarıdaki seri numarasının son 3 rakamını “Kasım” olarak değiştirir ve “455Kasım” metin dizesini döndürür.”.

Excel DEĞİŞTİR işlevinin tarihlerle doğru şekilde çalışmasını sağlamak için, önce METNEÇEVİR işlevini veya Excel’de tarihi metne dönüştürme bölümünde gösterilen başka bir tekniği kullanarak tarihleri metin dizelerine dönüştürebilirsiniz. Alternatif olarak, METNEÇEVİR işlevini doğrudan DEĞİŞTİR işlevinin eski metin bağımsız değişkenine gömebilirsiniz:

Önce bir metne çevir sütunu açalım ve şu formülü girelim.

=METNEÇEVİR(D2;”gg.aaaa.yyyy”)

Hemen yan sütuna da değiştir formülünü koyun

Lütfen yukarıdaki formülün sonucunun bir metin dizesi olduğunu ve bu nedenle bu çözümün yalnızca değiştirilen tarihleri başka hesaplamalarda kullanmayı planlamıyorsanız işe yaradığını unutmayın. Metin dizeleri yerine tarihlere ihtiyacınız varsa, Excel DEĞİŞTİR işlevi tarafından döndürülen değerleri tekrar tarihlere dönüştürmek için TARİHSAYISI işlevini kullanın:

=TARİHSAYISI(DEĞİŞTİR(METNEÇEVİR(A2;”gg-aaaa-yyyy”);4;4;Kasım))

Bir hücrede birden fazla değiştirme yapmak için iç içe değiştir formülü

Çoğu zaman, aynı hücrede birden fazla değiştirme yapmanız gerekebilir. Elbette, bir değiştirme yapabilir, ara bir sonucu ek bir sütuna çıktı olarak verebilir ve ardından DEĞİŞTİR formülünü tekrar kullanabilirsiniz. Ancak, daha iyi ve daha profesyonel bir yol, tek bir formülle birkaç değiştirme gerçekleştirmenize olanak tanıyan iç içe DEĞİŞTİR işlevlerini kullanmaktır. Bu bağlamda, “içiçe” bir formülü diğerinin içine yerleştirmek anlamına gelir.

Aşağıdaki örneği düşünün. A sütununda “123456789” olarak biçimlendirilmiş bir telefon numaraları listeniz olduğunu ve tire ekleyerek bunların daha çok telefon numarası gibi görünmesini istediğinizi varsayalım. Başka bir deyişle, amacınız “123456789” adresini “123-456-789″ adresine dönüştürmektir.

İlk kısa çizgiyi eklemek kolaydır. Sıfır karakteri tire ile değiştiren, yani bir hücredeki 4. konuma bir tire ekleyen olağan bir Excel Değiştir formülü yazarsınız:

=DEĞİŞTİR(A1;4;0;”-“)

Yukarıdaki Değiştirme formülünün sonucu aşağıdaki gibidir:

Tamam, şimdi 8. konuma bir kısa çizgi daha eklememiz gerekiyor. Bunu yapmak için, yukarıdaki formülü başka bir Excel DEĞİŞTİR formülünün içine yerleştirirsiniz. Daha doğrusu, bunu diğer işlevin eski metin bağımsız değişkenine yerleştirirsiniz, böylece ikinci DEĞİŞTİR işlevi A2 hücresindeki değeri değil, ilk DEĞİŞTİR tarafından döndürülen değeri işler:

=DEĞİŞTİR(DEĞİŞTİR(A1;4;0;”-“);8;0;”-“)

Sonuç olarak, telefon numaralarını istediğiniz formatta alırsınız:

Excel yerinekoy formülü

Excel’deki SUBSTITUTE işlevi, belirli bir karakterin veya metin dizesinin bir veya daha fazla örneğini belirtilen karakter(ler)le değiştirir.

YERİNEKOY(metin, eski_metin, yeni_metin, [yineleme_sayısı])

YERİNEKOY işlevinin söz diziminde aşağıdaki bağımsız değişkenler vardır:

• Metin    Gerekli. Metin veya karakterlerini değiştirmek istediğiniz metni içeren hücreye başvurudur.
• Eski_metin Gerekli. Yinelemek istediğiniz metindir.
• Yeni_metin Gerekli. Eski_metin yerine koymak istediğiniz metindir.
• Yineleme_sayısı İsteğe bağlı. Eski_metinin nerelerde yeni_metin ile değiştirileceğini belirtir. Yineleme_sayısı belirtilirse, eski_metin yalnız orada değiştirilir. Belirtilmezse, eski_metin, metinde geçtiği her yerde yeni_metin ile değiştirilir.

Örneğin, aşağıdaki formüllerin tümü A2 hücresinde “1” yerine “2” koyar, ancak son bağımsız değişkende hangi sayıyı sağladığınıza bağlı olarak farklı sonuçlar döndürür:

=YERİNEKOY(A2, “1”, “2”, 1) – “1 “in ilk geçtiği yeri “2” ile değiştirir.

= YERİNEKOY(A2, “1”, “2”, 2) – “1 “in ikinci geçtiği yeri “2” ile değiştirir.

= YERİNEKOY(A2, “1”, “2”) – “1 “in tüm geçtiği yerleri “2” ile değiştirir.

Not. Excel’deki SUBSTITUTE işlevi büyük/küçük harfe duyarlıdır. Örneğin, aşağıdaki formül A2 hücresindeki büyük “X” harfinin tüm örneklerini “Y” ile değiştirir, ancak küçük “x” harfinin hiçbir örneğini değiştirmez.

Excel değiştir ve yerinekoy formülleri

Excel DEĞİŞTİR ve YERİNEKOY formülleri, her ikisinin de metin dizelerini değiştirmek için tasarlanmış olması bakımından birbirine çok benzer. İki işlev arasındaki farklar aşağıdaki gibidir:

– YERİNEKOY formülleri, belirli bir karakterin veya metin dizesinin bir veya daha fazla örneğini değiştirir. Bu nedenle, değiştirilecek metni biliyorsanız, Excel YERİNEKOY formülünü kullanın.

– DEĞİŞTİR, bir metin dizesinin belirli bir konumundaki karakterleri değiştirir. Bu nedenle, değiştirilecek karakter(ler)in konumunu biliyorsanız, Excel DEĞİŞTİR formülünü kullanın.

– Excel’deki YERİNEKOY formülü, eski_metnin hangi oluşumunun yeni_metin olarak değiştirilmesi gerektiğini belirten isteğe bağlı bir parametre (yineleme sayısı) eklenmesine izin verir.

Excel’de YERİNEKOY formülü ve DEĞİŞTİR formülleri bu şekilde kullanabilirsiniz. Umarım bu örnekler görevlerinizi çözmede faydalı olur.

Microsoft destek sayfasını da inceleyebilirsiniz.