Excel'de Ayrı Ad ve Soyad (Formülleri Kullanan Bölünmüş Adlar)

Videoyu İzle - Excel'de Adları Böl (Ad, Orta ve Soyadı olarak)

Metin verilerini dilimlemek ve küp küp doğramak söz konusu olduğunda Excel harika bir araçtır.

Excel'de metin verileriyle çalışmak için kullanabileceğiniz pek çok kullanışlı formül ve işlev vardır.

Metin verilerini işlemekle ilgili aldığım en yaygın sorulardan biri - “Excel'de ad ve soyadı (veya ad, orta ve soyadı) nasıl ayrılır?“.

Excel'de adları bölmenin birkaç kolay yolu vardır. Seçtiğiniz yöntem, verilerinizin nasıl yapılandırıldığına ve sonucun statik mi yoksa dinamik mi olmasını istediğinize bağlı olacaktır.

Öyleyse başlayalım ve Excel'de adları bölmenin farklı yollarını görelim.

Metni Sütunlara Kullanarak Adları Böl

Excel'deki Metinden Sütunlara işlevi, metin değerlerini hızlı bir şekilde arka arkaya ayrı hücrelere bölmenize olanak tanır.

Örneğin, aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve adınızı ve soyadınızı ayırıp bunları ayrı hücrelere almak istediğinizi varsayalım.

Aşağıda, Metni Sütunlara Dönüştür özelliğini kullanarak adı ve soyadını ayırma adımları verilmiştir:

1. Sütundaki tüm adları seçin (bu örnekte A2:A10)
2. 'Veri' sekmesini tıklayın
3. 'Veri Araçları' grubunda, 'Sütunlara Metin' seçeneğine tıklayın.
4. Metni Sütuna Dönüştürme Sihirbazı'nda aşağıdaki değişiklikleri yapın:
   1. Adım 1/3: Sınırlandırılmış'ı seçin (bu, ayırıcı olarak boşluk kullanmanıza izin verir) ve İleri'ye tıklayın.
   2. Adım 2/3: Boşluk seçeneğini seçin ve İleri'ye tıklayın
   3. Adım 3/3: B2'yi hedef hücre olarak ayarlayın (aksi takdirde mevcut verilerin üzerine yazacaktır)
5. Bitir'e tıklayın

Yukarıdaki adımlar, adları anında ad ve soyadına böler (adları B sütununda ve soyadı C sütununda olacak şekilde).

Not: Hücrelerde halihazırda herhangi bir veri olması durumunda (Metni Sütunlara Dönüştürme çıktısının beklendiği yerler), Excel size hücrelerde zaten bazı veriler olduğunu bildiren bir uyarı gösterecektir. Verilerin üzerine yazmayı veya Metni Sütunlara Dönüştürmeyi iptal etmeyi ve önce manuel olarak kaldırmayı seçebilirsiniz.

Tamamlandığında, isterseniz tam ad verilerini silebilirsiniz.

Excel'de adları ve soyadlarını ayırmak için Metni Sütunlara Dönüştürürken bilmeniz gereken birkaç şey:

1. Bunun sonucu statiktir. Bu, yeni verileriniz olması veya orijinal verilerde bazı değişiklikler olması durumunda, adları bölmek için bunu baştan yapmanız gerektiği anlamına gelir.
2. Yalnızca Adı veya yalnızca Soyadı istiyorsanız, 'Metin Sütuna Sihirbazı' iletişim kutusunun 3. adımında istemediğiniz sütunları atlayabilirsiniz. Bunu yapmak için önizlemede atlamak istediğiniz sütunu seçin ve ardından 'Sütunu içe aktarma (atla)' seçeneğini seçin.
3. Hedef hücreyi belirtmemeniz durumunda, Metinden Sütuna Geçerli sütunun üzerine yazacaktır.

Metni Sütunlara Çevir seçeneği, tutarlı verileriniz olduğunda en uygunudur (örneğin, tüm adların yalnızca adı ve soyadı veya tüm adların adı, ortası ve soyadı vardır).

Bu örnekte, sınırlayıcı olarak boşluk olan isimleri nasıl ayıracağınızı gösterdim. Sınırlayıcının virgül veya virgül ve boşluk kombinasyonu olması durumunda, yine aynı adımları kullanabilirsiniz. Bu durumda, sınırlayıcıyı sihirbazın Adım 2/3'ünde belirtebilirsiniz. Sınırlayıcı olarak virgül kullanma seçeneği zaten var veya 'Diğer' seçeneğini seçip özel bir sınırlayıcı da belirtebilirsiniz.

Metni Sütunlara Dönüştürme, adları ayırmanın hızlı ve etkili bir yolu olsa da, yalnızca çıktının statik bir sonuç olmasını istediğinizde uygundur. Genişleyebilecek veya değişebilecek bir veri kümeniz varsa, adları ayırmak için formüller kullanmanız daha iyi olur.

Formülleri Kullanarak Ad, Orta ve Soyadı Ayırın

Formüller, metin verilerini dilimlemenize ve bölmenize ve istediğinizi çıkarmanıza olanak tanır.

Bu bölümde ad verilerini (verilerinizin nasıl yapılandırıldığına bağlı olarak) ayırmak için kullanabileceğiniz çeşitli formülleri paylaşacağım.

Aşağıda adı, orta adı ve soyadını ayırmak için kullanabileceğiniz üç formül bulunmaktadır (ilerideki bölümlerde ayrıntılı olarak açıklanmıştır).

İlk adı almak için formül:

=SOL(A2,ARA(" ",A2)-1)

Göbek adını almak için formül:

=ORTA(A2,ARA(" ",A2)+1,ARA(" ",YERİ(A2," ","@",1))-ARA(" ",A2))

Soyadı almak için formül:

=SAĞ(A15,UZUNLUK(A15)-ARA("@",YEDEK(A15," ","@",UZUNLUK(A15)-UZUNLUK(YEDEK(A15," ","")))))

Adını Al

Aşağıda gösterildiği gibi veri kümeniz olduğunu ve bir hücredeki adı ve bir hücredeki soyadını hızlı bir şekilde ayırmak istediğinizi varsayalım.

Aşağıdaki formül size ilk adı verecektir:

=SOL(A2,ARA(" ",A2)-1)

Yukarıdaki formül, ad ve soyadı arasındaki boşluk karakterinin konumunu almak için SEARCH işlevini kullanır. SOL işlevi daha sonra önündeki tüm metni çıkarmak için bu boşluk konum numarasını kullanır.

Bu, metin değerinin bir kısmını çıkarmanın oldukça basit bir kullanımıdır. Tek yapmamız gereken ilk boşluk karakterinin konumunu belirlemek olduğundan, ismin göbek adı olup olmaması önemli değildir. Yukarıdaki formül gayet iyi işleyecektir.

Şimdi, her bir örnekle biraz daha ilerleyelim.

Soyadı Al

Diyelim ki aynı veri setine sahipsiniz ve bu sefer soyadını almanız gerekiyor.

Aşağıdaki formül, soyadını yukarıdaki veri kümesinden çıkaracaktır:

=SAĞ(A2,UZUNLUK(A2)-ARA(" ",A2))

Yine, oldukça basit.

Bu sefer önce boşluk karakteri konumunu buluyoruz, bu daha sonra boşluktan sonra kalan karakter sayısını (soyadı olacak) bulmak için kullanılıyor.

Bu, boşluk karakterinin konum değeri ile adındaki toplam karakter sayısı çıkarılarak elde edilir.

Bu sayı daha sonra tüm bu karakterleri adın sağından almak için SAĞ işlevinde kullanılır.

Bu formül yalnızca ad ve soyadı olduğunda harika çalışıyor olsa da, ikinci adınız olması durumunda da işe yaramaz. Bunun nedeni, yalnızca bir boşluk karakterini hesaba katmamızdır (ad ve soyadı arasında). Bir göbek ada sahip olmak, ada daha fazla boşluk karakteri ekler.

Bir göbek adınız olduğunda soyadını almak için aşağıdaki formülü kullanın:

=SAĞ(A15,UZUNLUK(A15)-ARA("@",YEDEK(A15," ","@",UZUNLUK(A15)-UZUNLUK(YEDEK(A15," ","")))))

Şimdi, bu biraz karmaşık olmaya başladı… öyle değil mi?

Bunun nasıl çalıştığını açıklayayım.

Yukarıdaki formül, önce addaki toplam boşluk karakteri sayısını bulur. Bu, boşluk karakteri olan ve olmayan adın uzunluğunu alarak ve ardından boşluksuz olanı boşluklu olandan çıkararak yapılır. Bu, toplam boşluk karakteri sayısını verir.

SUBSTITUTE işlevi daha sonra son boşluk karakterini bir '@' sembolüyle değiştirmek için kullanılır (herhangi bir sembolü kullanabilirsiniz - adın bir parçası olarak ortaya çıkması muhtemel olmayan bir şey).

@ sembolü son boşluk karakterinin yerine geçtiğinde, bu @ sembolünün konumunu kolayca bulabilirsiniz. Bu, SEARCH işlevi kullanılarak yapılır.

Şimdi tek yapmanız gereken bu @ sembolünün sağındaki tüm karakterleri çıkarmak. Bu, SAĞ işlevi kullanılarak yapılır.

Orta Adı Alın

Aşağıda gösterildiği gibi veri kümeniz olduğunu ve ikinci adı çıkarmak istediğinizi varsayalım.

Aşağıdaki formül bunu yapacaktır:

=ORTA(A2,ARA(" ",A2)+1,ARA(" ",YERİ(A2," ","@",1))-ARA(" ",A2))

Yukarıdaki formül, bir başlangıç ​​konumu ve bu konumdan çıkarılacak karakter sayısını belirlemenize olanak sağlayan MID işlevini kullanır.

SEARCH işlevini kullanarak başlangıç ​​konumunu bulmak kolaydır.

Zor kısım, bu başlangıç ​​konumundan sonra kaç karakter çıkarılacağını bulmaktır. Bunu elde etmek için, başlangıç ​​konumundan son boşluk karakterine kadar kaç karakter olduğunu belirlemeniz gerekir.

Bu, DEĞİŞTİR işlevi kullanılarak yapılabilir ve son boşluk karakterini bir '@' sembolü ile değiştirebilir. Bu yapıldıktan sonra, bu son boşluk karakterinin konumunu bulmak için SEARCH işlevini kolayca kullanabilirsiniz.

Artık başlangıç ​​pozisyonuna ve son boşluğun pozisyonuna sahip olduğunuza göre, MID işlevini kullanarak ikinci adı kolayca getirebilirsiniz.

Adları ayırmak için formül kullanmanın faydalarından biri de sonucun dinamik olmasıdır. Bu nedenle, veri kümeniz genişler ve ona daha fazla ad eklenirse veya orijinal veri kümesinde bazı adlar değişirse, sonuçta ortaya çıkan veriler için endişelenmenize gerek yoktur.

Bul ve Değiştir'i Kullanan Ayrı Adlar

'Bul ve Değiştir' ile gelen esnekliği seviyorum - çünkü içinde joker karakterler kullanabilirsiniz.

Önce joker karakterin ne olduğunu açıklayayım.

Joker karakter, herhangi bir metin yerine kullanabileceğiniz bir şeydir. Örneğin, bir yıldız işareti (*) kullanabilirsiniz ve bu, Excel'de istediğiniz sayıda karakteri temsil edecektir. Örnek vermek gerekirse, A harfi ile başlayan tüm isimleri bulmak istersem bul ve değiştir kısmında A* kullanabilirim. Bu, adın A ile başladığı tüm hücreleri bulacak ve seçecektir.

Hala net değilseniz, endişelenmeyin. Okumaya devam edin, sonraki birkaç örnek, joker karakterlerin ne olduğunu ve adları (veya Excel'deki herhangi bir metin değerini) hızlı bir şekilde ayırmak için bunların nasıl kullanılacağını netleştirecektir.

Aşağıda ele alınan tüm örneklerde, veri kümesinin yedek bir kopyasını oluşturduğunuzdan emin olun. Bul ve Değiştir, kullanıldığı verileri değiştirir. Önce verileri kopyalayıp yapıştırmak ve ardından kopyalanan veri kümesinde Bul ve Değiştir'i kullanmak en iyisidir.

Adını Al

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve yalnızca ilk adı almak istediğinizi varsayalım.

Bunu yapmak için gereken adımlar aşağıdadır:

1. Sütun A'daki ad verilerini kopyalayın ve Sütun B'ye yapıştırın.
2. Sütun B'deki veriler seçiliyken Ana Sayfa sekmesine tıklayın
3. Düzenleme grubunda Bul ve Seç'e tıklayın.
4. Değiştir'e tıklayın. Bu, 'Bul ve Değiştir' iletişim kutusunu açacaktır.
5. 'Bul ve Değiştir' iletişim kutusuna aşağıdakini girin
   1. Neyi bulun: * (boşluk karakteri ve ardından yıldız işareti)
   2. Şununla değiştirin: bunu boş bırakın
6. Tümünü Değiştir'e tıklayın.

Yukarıdaki adımlar size ilk adı verir ve ilk addan sonraki her şeyi kaldırır.

Bu, göbek adı olan adlarınız olsa bile çalışır.

Profesyonel İpucu: Bul ve Değiştir iletişim kutusunu açmak için klavye kısayolu Kontrol + H (kontrol tuşunu basılı tutun ve ardından H tuşuna basın).

Soyadı Al

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve yalnızca soyadını almak istediğinizi varsayalım.

Bunu yapmak için gereken adımlar aşağıdadır:

1. Sütun A'daki ad verilerini kopyalayın ve Sütun B'ye yapıştırın.
2. Sütun B'deki veriler seçiliyken Ana Sayfa sekmesine tıklayın
3. Düzenleme grubunda Bul ve Seç'e tıklayın.
4. Değiştir'e tıklayın. Bu, 'Bul ve Değiştir' iletişim kutusunu açacaktır.
5. 'Bul ve Değiştir' iletişim kutusuna aşağıdakini girin
   1. Neyi bulun: * (yıldız işareti ve ardından bir boşluk karakteri)
   2. Şununla değiştirin: bunu boş bırakın
6. Tümünü Değiştir'e tıklayın.

Yukarıdaki adımlar size soyadını verir ve ilk addan önceki her şeyi kaldırır.

Bu, göbek adı olan adlarınız olsa bile çalışır.

Orta Adı Kaldır

Yalnızca göbek adından kurtulmak ve yalnızca ad ve soyadına sahip olmak istiyorsanız, Bul ve Değiştir'i kullanarak bunu yapabilirsiniz.

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve bunlardan göbek adını çıkarmak istediğinizi varsayalım.

Bunu yapmak için gereken adımlar aşağıdadır:

1. Sütun A'daki ad verilerini kopyalayın ve Sütun B'ye yapıştırın.
2. Sütun B'deki veriler seçiliyken Ana Sayfa sekmesine tıklayın
3. Düzenleme grubunda Bul ve Seç'e tıklayın.
4. Değiştir'e tıklayın. Bu, 'Bul ve Değiştir' iletişim kutusunu açacaktır.
5. 'Bul ve Değiştir' iletişim kutusuna aşağıdakini girin
   1. Neyi bulun: * (boşluk karakteri ve ardından yıldız işareti ve ardından boşluk karakteri)
   2. Şununla değiştirin: (buraya bir boşluk karakteri koymanız yeterlidir)
6. Tümünü Değiştir'e tıklayın.

Yukarıdaki adımlar, göbek adını tam addan kaldırır. Bazı isimlerin göbek adı yoksa, değiştirilmezler.

Hızlı Doldurma Kullanarak Ayrı Adlar

Flash dolgu, Excel 2013'te tanıtıldı ve bir metin veri kümesini değiştirmeyi veya temizlemeyi gerçekten kolaylaştırıyor.

Ad verilerini ayırmak söz konusu olduğunda, Flash Fill'in ara sokaklarındadır.

Hızlı Doldurma kullanırken bilinmesi gereken en önemli şey, hızlı doldurmanın tanımlayabileceği bir desene ihtiyaç duyulmasıdır. Modeli belirledikten sonra, Excel'deki adları ayırmanıza kolayca yardımcı olacaktır (aşağıdaki birkaç örneği gözden geçirdiğinizde bu konuda daha fazla netlik kazanacaksınız).

Tam Adından Adını veya Soyadını Alın

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve yalnızca ilk adı almak istediğinizi varsayalım.

1. Bitişik hücrede, tam addan ilk adı manuel olarak yazın. Bu örnekte, Rick yazardım.
2. İkinci hücrede, bitişik hücrenin adından ilk adı manuel olarak yazın. Yazarken, Hızlı Doldurma'nın size otomatik olarak ilk adın bir listesini (gri renkte) gösterdiğini göreceksiniz.
3. Adları gri renkte gördüğünüzde, doğru adları gösterdiğinden emin olmak için hızlıca göz atın. Bunlar doğruysa, enter tuşuna basın ve Hızlı Doldurma, hücrelerin geri kalanını otomatik olarak ilk adla dolduracaktır.

Hızlı Doldurma, size değiştirilmiş verileri verirken izleyebileceği bir kalıp vermenizi gerektirir. Örneğimizde, ilk hücreye ilk adı yazdığınızda, Hızlı Doldurma kalıbı çözemez.

Ancak, ikinci hücreye Adı girmeye başladığınızda, Hızlı Doldurma kalıbı anlar ve size bir öneri gösterir. Öneri doğruysa, sadece enter tuşuna basın.

Ve doğru değilse, birkaç hücreye daha manuel olarak girmeyi deneyebilir ve Hızlı Doldurma'nın deseni ayırt edip edemediğini kontrol edebilirsiniz.

Bazen deseni gri renkte göremeyebilirsiniz (yukarıdaki 2. adımda gösterildiği gibi). Bu durumda, Hızlı Doldurma sonucunu almak için aşağıdaki adımları izleyin:

1. Metni iki hücreye manuel olarak girin.
2. Bu iki hücreyi de seçin
3. İmleci seçimin sağ alt kısmına getirin. İmlecin artı simgesine dönüştüğünü fark edeceksiniz.
4. Üzerine çift tıklayın (farenin sol tuşu). Bu, tüm hücreleri dolduracaktır. Bu noktada, sonuçlar muhtemelen yanlıştır ve beklediğiniz gibi değildir.
5. Ortaya çıkan verilerin sağ alt kısmında küçük bir Otomatik Doldurma simgesi göreceksiniz. Bu Otomatik Doldurma simgesine tıklayın
6. Hızlı Doldur'a tıklayın

Yukarıdaki adımlar size Hızlı Doldurmanın sonucunu verecektir (çıkardığı desene göre).

Soyadı veya ikinci adı almak için Hızlı Doldurma'yı da kullanabilirsiniz. İlk iki hücreye soyadını (veya ikinci adı) girin ve flash doldurma kalıbı anlayabilecektir.

Hızlı Doldurma Kullanarak Adı Yeniden Düzenleme

Hızlı Doldurma akıllı bir araçtır ve biraz karmaşık desenleri de deşifre edebilir

Örneğin, aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve adı Rick Novak'tan Novak, Rick'e (soyadın önce virgül ve ardından adın geldiği) yeniden düzenlemek istediğinizi varsayalım.

Bunu yapmak için gereken adımlar aşağıdadır:

1. Bitişik hücreye manuel olarak yazın Novak, Rick
2. İkinci hücreye manuel olarak yazın Connor, Susan. Yazarken, Hızlı Doldurma işleminin size aynı biçimde (gri renkte) adların bir listesini gösterdiğini göreceksiniz.
3. Adları gri renkte gördüğünüzde, doğru adları gösterdiğinden emin olmak için hızlıca göz atın. Bunlar doğruysa, enter tuşuna basın ve Hızlı Doldurma, hücrelerin geri kalanını aynı biçimde adlarla otomatik olarak dolduracaktır.

Orta Adı Kaldır (veya Sadece göbek adı al)

Göbek adından kurtulmak veya yalnızca ikinci adı almak için Hızlı Doldurma'yı da kullanabilirsiniz.

Örneğin, aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve ikinci adı değil, yalnızca adı ve soyadını almak istediğinizi varsayalım.

Bunu yapmak için gereken adımlar aşağıdadır:

1. Bitişik hücreye manuel olarak yazın Rick Novak
2. İkinci hücreye manuel olarak yazın Susan Connor. Yazarken, Hızlı Doldurma'nın size aynı biçimde (gri renkte) adların bir listesini gösterdiğini göreceksiniz.
3. Adları gri renkte gördüğünüzde, doğru adları gösterdiğinden emin olmak için hızlıca göz atın. Bunlar doğruysa, enter tuşuna basın ve Hızlı Doldurma, hücrelerin geri kalanını orta adı olmayan adlarla otomatik olarak dolduracaktır.

Benzer şekilde, yalnızca ikinci adları almak istiyorsanız, ilk iki hücreye ikinci adı yazın ve kalan tüm adlardan ikinci adı almak için Hızlı Doldurma'yı kullanın.

Bu öğreticide gösterilen örnekler, metin verilerini işlerken adları kullanır. Aynı kavramları diğer veri biçimleriyle (adresler, ürün adları vb.)