info@teknoedu.com
Microsoft 365

Excel’de Metin Temizleme ve Bölme: TRIM, TEXTSPLIT, TEXTBEFORE ve Hızlı Doldurma

Fazladan boşluklar, tek hücrede birleşik ad soyad, e-postadan alan adı ayıklama... Excel'in metin fonksiyonları ve Hızlı Doldurma özelliğiyle dağınık metin verisini dakikalar içinde düzenli tabloya dönüştürmeyi anlattık.

Excel'de Metin Temizleme ve Bölme: TRIM, TEXTSPLIT, TEXTBEFORE ve Hızlı Doldurma

Başka sistemlerden dışa aktarılan, formlardan toplanan ya da elle girilen verilerin ortak bir özelliği vardır: dağınıktır. Ad ve soyad aynı hücrededir, bazı hücrelerin başında görünmez boşluklar vardır, telefon numaraları farklı biçimlerde yazılmıştır. Bu veriyle düşeyara yapmaya çalıştığınızda eşleşmeyen kayıtlar, filtrelediğinizde ikiye bölünmüş gruplar görürsünüz.

Bu yazıda Excel’in metin fonksiyonlarını “temizle, böl, birleştir, ayıkla” mantığıyla ele alıyor ve formül yazmadan iş gören Hızlı Doldurma özelliğini tanıtıyoruz.

Not: Formüller İngilizce fonksiyon adlarıyla ve virgül ayırıcıyla yazılmıştır. Türkçe Excel’de fonksiyon adları farklıdır ve ayırıcı olarak noktalı virgül kullanılır. TEXTSPLIT, TEXTBEFORE ve TEXTAFTER gibi yeni fonksiyonlar Microsoft 365 ve web için Excel’de bulunur; eski kalıcı lisanslı sürümlerde olmayabilir.

1. Temizlik: Görünmez sorunları gidermek

TRIM: Fazla boşlukları silmek

TRIM (KIRP), metnin başındaki ve sonundaki boşlukları siler, sözcük arasındaki birden fazla boşluğu teke indirir:

=TRIM(A2)
"  Ayşe   Yılmaz " → "Ayşe Yılmaz"

Bölünmez boşluk sorunu

Web sayfalarından kopyalanan verilerde sıklıkla bölünmez boşluk (karakter kodu 160) bulunur ve TRIM bunu silmez. Bu durumda önce normal boşluğa çevirin:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

CLEAN: Yazdırılamayan karakterler

Bazı sistem çıktılarında satır sonu ve kontrol karakterleri bulunur. CLEAN bunların çoğunu temizler. Birlikte kullanım yaygındır: =TRIM(CLEAN(A2)).

Büyük-küçük harf düzeltme

  • UPPER(A2) → tümü büyük
  • LOWER(A2) → tümü küçük
  • PROPER(A2) → Sözcüklerin ilk harfi büyük

Türkçe uyarısı: “i/İ” ve “ı/I” dönüşümleri sistem ve dil ayarına göre beklenmedik sonuç verebilir. Özellikle “ISPARTA” gibi büyük harfli verileri küçültürken sonucu birkaç örnek üzerinde kontrol edin.

2. Bölme: Bir hücreyi parçalara ayırmak

TEXTBEFORE ve TEXTAFTER

Bir ayırıcının öncesini ya da sonrasını almak artık çok kolay:

=TEXTBEFORE(A2, "@")    → "ayse.yilmaz"      (ayse.yilmaz@ornek.com)
=TEXTAFTER(A2, "@")     → "ornek.com"

Üçüncü bağımsız değişkenle kaçıncı ayırıcının kullanılacağını belirtebilirsiniz. Negatif değer sondan saymayı sağlar. Bu, ad soyad ayırmada çok işe yarar; çünkü bazı kişilerin iki adı vardır:

=TEXTBEFORE(A2, " ", -1)   → "Ayşe Nur"    (Ayşe Nur Yılmaz)
=TEXTAFTER(A2, " ", -1)    → "Yılmaz"

Burada son boşluk ayırıcı olarak kabul edildiği için ikinci ad soyadla karışmaz.

TEXTSPLIT: Birden çok sütuna yaymak

TEXTSPLIT, metni ayırıcıya göre böler ve sonucu yan yana hücrelere taşırır (dinamik dizi):

=TEXTSPLIT(A2, ";")
"Matematik;Fizik;Kimya" → Matematik | Fizik | Kimya

İkinci ayırıcı olarak satır ayırıcısı da verebilirsiniz; böylece metin hem sütunlara hem satırlara bölünür. Taşma alanında başka veri varsa #TAŞMA! hatası alırsınız; sağdaki hücreleri boşaltın.

Klasik yöntemler: LEFT, RIGHT, MID, FIND

Yeni fonksiyonların olmadığı sürümlerde aynı işler klasik fonksiyonlarla yapılır:

=LEFT(A2, FIND(" ", A2) - 1)        → İlk boşluktan önceki kısım
=MID(A2, FIND(" ", A2) + 1, 100)    → İlk boşluktan sonraki kısım
=RIGHT(A2, 4)                       → Son 4 karakter
=LEN(A2)                            → Karakter sayısı

FIND büyük-küçük harfe duyarlıdır; SEARCH değildir ve *, ? joker karakterlerini destekler.

Formülsüz alternatif: Metni Sütunlara Dönüştür

Tek seferlik işler için Veri > Metni Sütunlara Dönüştür sihirbazı hâlâ en hızlı yoldur. Ancak sonuç statiktir; kaynak veri değişirse işlemi tekrarlamanız gerekir. Her ay tekrar eden işler için formül ya da Power Query tercih edin.

3. Birleştirme

İki ya da daha fazla hücreyi birleştirmenin üç yolu vardır:

Yöntem Örnek Not
& operatörü =B2 & " " & C2 En basit yol
CONCAT =CONCAT(B2, " ", C2) Aralık kabul eder
TEXTJOIN =TEXTJOIN(", ", TRUE, B2:E2) Ayırıcı ekler, boşları atlayabilir

TEXTJOIN‘in ikinci bağımsız değişkeni TRUE olduğunda boş hücreler atlanır; böylece “Ankara, , Çankaya” gibi çift virgüller oluşmaz. FILTER ile birlikte kullanıldığında bir koşula uyan tüm adları tek hücrede listelemek de mümkündür:

=TEXTJOIN(", ", TRUE, FILTER(Ogrenciler[Ad], Ogrenciler[Sinif]="6-A"))

4. Değiştirme ve ayıklama

SUBSTITUTE belirli bir metni başka bir metinle değiştirir. Telefon numaralarındaki boşluk, tire ve parantezleri temizlemek için iç içe kullanılabilir:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"(","")

İç içe SUBSTITUTE kalabalıklaşıyorsa okunabilirlik için LET fonksiyonuyla ara adımlara ad verebilirsiniz. Microsoft 365’in güncel sürümlerinde düzenli ifade (regex) desteği sunan fonksiyonlar da bulunmaktadır; bunlar karmaşık desenler için güçlü bir seçenektir, ancak dosyayı eski sürüm kullanan kişilerle paylaşacaksanız uyumluluğu göz önünde bulundurun.

Sayıya ve tarihe dönüştürme

Metin temizliğinin son adımı çoğu zaman sayı ve tarih gibi görünen metinleri gerçek değere dönüştürmektir. Sol üst köşesinde yeşil üçgen olan “sayı olarak saklanan metin” hücrelerini seçip uyarı simgesinden Sayıya Dönüştür‘ü seçebilirsiniz. Formül tarafında ise VALUE fonksiyonu ya da yerel ayarlardan bağımsız çalışan NUMBERVALUE kullanılabilir:

=NUMBERVALUE("1.250,75", ",", ".")   → 1250,75

NUMBERVALUE’nun ikinci ve üçüncü bağımsız değişkenleri ondalık ve binlik ayırıcıyı belirtir. Böylece İngilizce biçimli (1,250.75) ve Türkçe biçimli (1.250,75) verileri aynı çalışma kitabında güvenle dönüştürebilirsiniz.

Hızlı Doldurma: Formülsüz desen tanıma

Hızlı Doldurma (Flash Fill), birkaç örnekten deseni öğrenip sütunun geri kalanını doldurur. Örneğin A sütununda “Ayşe Yılmaz” gibi tam adlar varsa:

  1. B2 hücresine elle “Ayşe” yazın.
  2. B3’e geçip Ctrl+E tuşlarına basın.

Excel tüm adları ayıklar. Aynı yöntemle:

  • E-posta adresinden kullanıcı adı çıkarma
  • “yilmaz, ayşe” biçimini “Ayşe Yılmaz” yapma
  • Telefon numarasını “0532 123 45 67” biçimine getirme
  • Ürün kodunun belirli bir bölümünü alma

gibi işleri saniyeler içinde yapabilirsiniz.

Sınırları: Hızlı Doldurma sonucu statiktir; kaynak değişirse güncellenmez. Ayrıca desen düzensiz verilerde yanlış tahmin edebilir. Sonucu mutlaka birkaç farklı satırda kontrol edin; gerekiyorsa iki üç örnek daha yazarak deseni netleştirin.

Uygulamalı senaryo: Öğrenci listesi temizliği

Bir okul bilgi sisteminden gelen listede ” YILMAZ, Ayşe Nur ” biçiminde adlar ve “6/A” biçiminde sınıflar olduğunu varsayalım. Hedef: ayrı Ad ve Soyad sütunları, “6-A” biçiminde sınıf.

B2: =TRIM(SUBSTITUTE(A2, CHAR(160), " "))     → "YILMAZ, Ayşe Nur"
C2: =PROPER(TEXTBEFORE(B2, ","))              → "Yılmaz"
D2: =TRIM(TEXTAFTER(B2, ","))                 → "Ayşe Nur"
E2: =SUBSTITUTE(F2, "/", "-")                 → "6-A"

Son olarak sonuç sütunlarını kopyalayıp Değerleri Yapıştır ile statik hâle getirebilir ya da tabloyu formüllü bırakıp yeni veri geldiğinde otomatik temizlenmesini sağlayabilirsiniz. PROPER sonucundaki Türkçe karakterleri kontrol etmeyi unutmayın.

Sonuç

Temiz veri, doğru raporun ön koşuludur. TRIM ve SUBSTITUTE ile görünmez sorunları giderip, TEXTBEFORE, TEXTAFTER ve TEXTSPLIT ile metni parçalara ayırıp, TEXTJOIN ile yeniden birleştirdiğinizde dağınık listeler kısa sürede düzenli tablolara dönüşür. Tek seferlik işlerde Hızlı Doldurma’nın pratikliğinden, tekrar eden işlerde formüllerin ve Power Query’nin kalıcılığından yararlanın.