Excel'de Ad Soyad Ayırma: Formül ve Hızlı Doldurma

Ad ve soyad hangi formülle ayrılır?

Ad ile soyadı ayırmanın temeli, aradaki boşluğun yerini bulmaktır. BUL fonksiyonu boşluğun kaçıncı karakterde olduğunu verir; SOLDAN da o noktaya kadar olan kısmı alır. "Zeynep Kaya" metninde boşluk 7. karakterdir, bu yüzden ilk 6 karakter ad olarak döner.

=SOLDAN(A2;BUL(" ";A2)-1)

=LEFT(A2,FIND(" ",A2)-1)

Formüldeki -1, boşluğun kendisini sonuca katmamak içindir. Bunu unutursanız "Zeynep " değeri sonda bir boşlukla gelir ve sonraki aramalarda eşleşme sorunlarına yol açar.

Soyadı almak

Soyadı, boşluktan sonraki bütün karakterlerdir. Metnin toplam uzunluğundan boşluğun konumunu çıkarırsanız soyadın kaç karakter olduğunu bulursunuz. SAĞDAN bu sayı kadar karakteri sondan alır.

=SAĞDAN(A2;UZUNLUK(A2)-BUL(" ";A2))

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

İki formülü yan yana iki sütuna yazıp aşağı kopyaladığınızda yüzlerce satırlık bir personel listesi saniyeler içinde ad ve soyad sütunlarına ayrılır. Önce ham sütunu KIRP'tan geçirmek iyi olur; baştaki bir boşluk BUL'un ilk boşluğu yanlış yerde bulmasına neden olur.

İki adı olan kişiler

Türkçe isimlerde iki ad sık görülür: "Ayşe Nur Demir" gibi. Yukarıdaki formül ilk boşluğa baktığı için ad olarak "Ayşe", soyad olarak "Nur Demir" verir. Doğru sonuç için ilk boşluğa değil son boşluğa bakmak gerekir.

Son boşluğu bulmanın yolu, YERİNEKOY ile o boşluğu metinde geçmeyen bir işaretle değiştirmektir. Boşluk sayısını, metnin uzunluğu ile boşluksuz uzunluğu arasındaki farktan hesaplarsınız. YERİNEKOY'un dördüncü argümanı kaçıncı boşluğun değişeceğini söyler.

=SAĞDAN(A3;UZUNLUK(A3)-BUL("@";YERİNEKOY(A3;" ";"@";UZUNLUK(A3)-UZUNLUK(YERİNEKOY(A3;" ";"")))))

=RIGHT(A3,LEN(A3)-FIND("@",SUBSTITUTE(A3," ","@",LEN(A3)-LEN(SUBSTITUTE(A3," ","")))))

Aynı mantıkla iki adı birlikte de alabilirsiniz: =SOLDAN(A3;BUL("@";YERİNEKOY(A3;" ";"@";UZUNLUK(A3)-UZUNLUK(YERİNEKOY(A3;" ";""))))-1) formülü "Ayşe Nur" sonucunu verir. Formül uzun görünür ama yalnız bir kez yazılır, sonra aşağı kopyalanır.

Unvanlı ya da hatalı kayıtlar

Listede "Dr. Ahmet Kara" gibi unvanlı kayıtlar varsa formül unvanı ad olarak alır; önce unvanları ayrı bir adımda silmek sonucu düzeltir. =KIRP(YERİNEKOY(A2;"Dr.";"")) formülü unvanı ve ardından kalan boşluğu temizler. Birden fazla unvan varsa YERİNEKOY'ları iç içe yazabilirsiniz.

Tek kelimelik kayıtlar da sorun çıkarır. Yalnız "Mehmet" yazan bir hücrede boşluk olmadığı için BUL #DEĞER! hatası verir. Formülü =EĞERHATA(SOLDAN(A2;BUL(" ";A2)-1);A2) biçiminde sararsanız boşluk bulunmayan hücrede metnin tamamı ad olarak döner.

Formülsüz yol: Hızlı Doldurma

Formül yazmak istemiyorsanız Hızlı Doldurma çoğu listede yeterlidir. Ad Soyad sütununun yanındaki ilk hücreye "Zeynep" yazın, alt hücreye geçip Ctrl+E'ye basın. Excel deseni tanır ve sütunun kalanını adlarla doldurur. Soyad sütununda da aynı adımı "Kaya" yazarak tekrarlarsınız.

Hızlı Doldurma örnekten tahmin yürütür. İki adlı kişiler listenin ortasındaysa bazılarını yanlış ayırabilir; sonuçlara göz atmanız gerekir. Kaynak sütun sonradan değişirse sonuç güncellenmez, çünkü hücrelere formül değil değer yazılır.

Ctrl+E hiçbir şey yapmıyorsa özellik kapalı olabilir. Microsoft, bu durumda Excel seçeneklerinin Gelişmiş bölümünde, Düzenleme Seçenekleri altındaki Otomatik Olarak Hızlı Doldur kutusunu işaretlemenizi önerir. Ayrıca örnek yazdığınız sütunun ham veriye bitişik olması gerekir; arada boş sütun varsa Excel deseni ilişkilendiremez.

Metni Sütunlara Dönüştürme Sihirbazı

Veri sekmesindeki Metni Sütunlara Dönüştürme Sihirbazı, metni seçtiğiniz ayırıcıdan böler. Sütunu seçip sihirbazı açın, Sınırlandırılmış'ı seçin, Sınırlayıcılar bölümünde boşluğu işaretleyin. Hedef kutusuna sonuçların yazılacağı ilk hücreyi verin; vermezseniz sihirbaz ham sütunun üzerine yazar.

Bu yöntem her boşlukta böldüğü için "Ayşe Nur Demir" üç sütuna dağılır. Listede iki adlı kişiler çoksa formül yöntemi daha kontrollüdür.

Microsoft 365'te METİNBÖL

Microsoft 365 ve Excel 2024'te METİNBÖL fonksiyonu metni ayırıcıdan böler ve parçaları yan hücrelere döker. =METİNBÖL(A2;" ") yazdığınızda "Zeynep" ve "Kaya" iki ayrı hücreye yerleşir. Eski sürümlerde bu fonksiyon bulunmaz ve #AD? hatası verir; dosyayı paylaşacağınız kişilerin sürümünü bilmiyorsanız SOLDAN ve SAĞDAN daha güvenlidir.

Soyada göre sıralamak için yeniden birleştirmek

Ad ve soyad ayrıldıktan sonra listeyi soyada göre sıralamak kolaylaşır. Bazı raporlarda ise "Kaya, Zeynep" biçimi istenir. Ayrılmış sütunları =C2&", "&B2 ile birleştirerek bu görünümü elde edersiniz. Böylece aynı veriden hem ayrı sütunlar hem de rapora uygun tek bir metin üretmiş olursunuz.

Sıradaki adım

Listenizi önce KIRP ile temizleyin, sonra ilk birkaç satırda SOLDAN ve SAĞDAN formüllerini deneyin. İki adlı kişiler varsa son boşluğa bakan formüle geçin ve sonuçları değer olarak yapıştırarak sabitleyin.