Excel de birleşik yazılmış isimleri ad soyad olarak farklı sütunlara ayırmak

Birçok insan Excel kullanırken bir sütundaki birleşik veriyi kolonlara ayırma ihtiyacı duymuştur. Bunu yapmanın tabi ki farklı yöntemleri var. Örneğin kolonu seçip Veri menüsünden Metni Sütunlara Dönüştür deyip kriter olarak boşluk karakterini seçerek ayırma işlemini yapabilirsiniz. Ancak özellikle ad ve soyad ayırmak istediğinizde sadece ad ve soyad dan oluşan (Ali KAYA gibi) isimler 2 kolona düzgün bir şekilde dağıtılırken 2 adı olan isimler (Mehmet Ali KAYA gibi) 3 kolona dağılacaktır.

Daha basit formül ile isim ayırma problemine çözüm bulabiliriz. Önereceğim bu çözüm 1 ve 2 adı olan isimleri desteklemektedir. 3 adı olanlar için manuel müdahe etmeniz gerekir.

Excel de A sütununda basit bir isim tablomuz olduğunu varsayalım:

Örneğin:

 

B2 hücresinde = yazıp ilk formülümüzü yapıştıralım daha sonra enter a basalım, hücredeki formül aşağıdaki gibi olmalı

Excel Türkçe ise :

=EĞER(BAĞ_DEĞ_SAY(BUL(” “;A2;BUL(” “;A2)+1))>0;SOLDAN(A2; BUL(” “; A2;BUL(” “; A2)+1)-1); SOLDAN(A2; BUL(” “; A2)-1))

 

Excel İngilizce ise :

=IF(COUNT(FIND(” “;A2;FIND(” “;A2)+1))>0;LEFT(A2;FIND(” “;A2;FIND(” “;A2)+1)-1);LEFT(A2;FIND(” “;A2)-1))


C2 hücresinde = yazıp ikinci formülümüzü yapıştıralım daha sonra enter a basalım, hücredeki formül aşağıdaki gibi olmalı

Excel Türkçe ise :

=PARÇAAL(A2;UZUNLUK(B2)+2;UZUNLUK(A2)-UZUNLUK(B2))

 

Excel İngilizce ise :

=MID(A2;LEN(B2)+2;LEN(A2)-LEN(B2))


Gördüğünüz gibi ilk satırımız düzgün bir şekilde ad ve soyad olarak ayrıldı.

Daha sonra bu formülü aşağı doğru bütün satırlara uygulayalım (Aşağıdaki şekildeki gibi seçip artı işareti olan yere mouse ile çift tıklayın veya mouse ile tutarak en son satıra kadar çekin).

 

Evet Sonunda aşağıdaki gibi bir formül ve sonuç elde etmiş olacağız.

 

 

Daha sonra ayırdığımız bu datayı formülsüz olarak sadece metinlerini başka bir sayfaya kopylarak istediğiniz yerde kullanabilirsiniz.

Bunu yapmak için kopyalamak istediğiniz hücreleri seçin sonra (Ctrl + C) veya kopyala ile hafızaya alın sonra başka bir Excel sayfasında yapıştırmak istediğiniz yere sağ tuş ile tıklayın açılan menüden “Özel Yapıştır > Değerleri” seçerek yapıştırınız.

Örnek Excel belgesini aşağıdaki linkten indirebilirsiniz.

Excel-Ad-Soyad-Ayirmak.xls (20,00 kb)

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestShare on RedditDigg thisEmail this to someonePrint this page

Post Author: mustafaozcan

Excel de birleşik yazılmış isimleri ad soyad olarak farklı sütunlara ayırmak” için 37 yorum

  • Mustafa ÖZCAN

    (23 Temmuz 2009 - 12:42)

    Çok Teşekkürler.. Çok kullanışlı bir bilgi..

  • Celal

    (22 Nisan 2010 - 01:49)

    S.A. hocam,
    Bu yazdığınız uygulamayı 2 isim bir soyisim olan isimlerde yapınca, ikinci ismi de soyisimle beraber ayırıyor. Bunu nasıl düzeltebiliriz.
    Ali Veli YILMAZ
    Ali Veli YILMAZ Gibi

  • Mustafa ÖZCAN

    (22 Nisan 2010 - 04:39)

    Sanırım siz formülü hatalı uyguladınız. Çünkü bu formülün asıl amacı iki isimli olanları düzgün ayırabilmektir. Yukarıdaki şekle bakacak olursanız Mehmet Ali CAN örneğinde olduğu gibi düzgün bir şekilde ayrıştırıldığını görebilirsiniz.

  • Celal

    (22 Nisan 2010 - 04:47)

    Arkadaşım çok teşekkür ederim. Süpersin

  • EMRAH

    (5 Ekim 2010 - 14:30)

    HAYDAR BORAN BORAN
    ABDULLAH ÜNLÜ LAH ÜNLÜ
    EMİNE GÜL E GÜL
    ŞENEL TORANER RANER
    ALİ TORANER NER
    KIYMET TORANER ORANER
    KUDRET TORANER ORANER
    BÜLENT ŞİMŞEK ŞİMŞEK
    HAMDULLAH TEMİZ LAH TEMİZ
    EMRULLAH TEMİZ AH TEMİZ
    SALMAN TEMİZ TEMİZ
    NURFAN ŞAHİN ŞAHİN
    ETHEM ÖZERDEM ERDEM
    NURULLAH YILMAZ H YILMAZ
    ZİNNET TORANER ORANER
    HASAN AKÇA AKÇA
    OLARAK ÇIKIYO HİÇBİR ŞEKİLDE DÜZELTEMİYORUM NE YAPMAM LAZIM AD BÖLÜMÜ ÇOK SÜPER OLDU AMA BİR DE BU OLURSA TAM OLAÇEK

  • Mustafa ÖZCAN

    (5 Ekim 2010 - 22:31)

    Muhtemelen formülde hata yapıyorsunuzdur. Örnek Excel belgesini yukarıya ekliyorum oradan inceleyebilirsiniz.

  • EMRAH

    (6 Ekim 2010 - 09:18)

    ilginiz için teşekkürler

  • emrah

    (18 Mart 2011 - 09:17)

    slm benim bir sorunum var ama nasıl yapacağımı bilmiyorum adobe reader dosyayı excell çevirmek istiyorum ilgilenirseniz sevinirim

  • Mustafa ÖZCAN

    (18 Mart 2011 - 23:13)

    Bu site üzerinden ücretsiz çevirebilirsiniz. http://www.pdftoexcelonline.com/

  • yasin

    (21 Mart 2011 - 20:36)

    merhaba hocam bnm sorum bıraz farklı ismin ilk harfi soyadın hepsı buyuk olacak bunu nasıl yapabılırz şimdiden teşekkurler

  • AHMET

    (22 Mart 2011 - 20:37)

    EYVALLAH HOCA SAĞLAM BİLGİ. FORMULLERDEN OLUŞAN BİR ÇALIŞMANIZ VARMI İNDİREBİLECEĞİMİZ. YOKSA NEREYİ TAVSİYE EDERSİNİZ.

  • Mustafa ÖZCAN

    (22 Mart 2011 - 21:13)

    Excel Türkçe ise
    ——————————
    =YAZIM.DÜZENİ(A1) & " " & BÜYÜKHARF(B1)

    İngilizce ise
    ——————————
    =PROPER(A1) & " " & UPPER(B1)

    Bu formul ile istediğiniz sonucu elde edersiniz.

  • Mustafa ÖZCAN

    (22 Mart 2011 - 21:27)

    Maalesef yok. Uzmanlık alanım da Excel değil. Ancak en çok ihtiyaç duyulan ve genellikle çözülememiş bazı problemlere çözümler üretmeyi seviyorum. Türkçe kaynak olarak http://www.excel.web.tr adresini takip edebilirsiniz. Kitap konusunda ise hangi alanda kendinizi geliştirmek istiyorsanız o alandaki kitapları inceleyiniz. Örneğin makrolar, vba, formüller, grafikler, ileri düzey istatistikler vs. gibi bir çok başlık altında yayınlanan kitaplar mevcut. Son 6-7 yıldır Excel ile ilgili bir kitap okumadığımdan güncel yayınlar hakkında bilgi sahibi değilim.

  • yasin

    (5 Nisan 2011 - 23:21)

    hocam merhaba bız bunları aynı sutunda yapmak ıstersek ne olacak peki isim bozum mesela ali veli kılıc dıger sutunda Ali Veli KILIC YAZDIRMAK ISTIORUM NE YAPMAM LAZIM TSKRLER

  • Mustafa ÖZCAN

    (6 Nisan 2011 - 22:36)

    Önce sütunlara ayırıp adı için YAZIM.DÜZENİ fonksiyonunu, soyadı için de BÜYÜKHARF fonksiyonunu kullanıp daha sonra bu sütunları birleştirebilirsiniz.

  • chess

    (3 Haziran 2011 - 11:32)

    sıkışık bir anımda bu yazı çok işime yaradı.binlerce kaydı tek harekette ayırdım.
    http://www.aktifsatranc.com

  • Tarkan

    (27 Temmuz 2011 - 15:42)

    7 yöntemle ad ayırma
    1
    =SOLDAN(A1;BUL(“#”;YERİNEKOY(A1;” “;”#”;ELEMAN(UZUNLUK(A1)-UZUNLUK(YERİNEKOY(A1;” “;””))+1;1;1;2;3;4))&”#”;1))
    2
    =SOLDAN(A1;ARA(UZUNLUK(A1);1*YERİNEKOY(PARÇAAL(A1;SATIR($1: $30);1);” “;SATIR($1: $30);1)))
    3
    =SOLDAN(A1;ARA(99999;BUL(” “;PARÇAAL(A1;SATIR($1: $30);1));SATIR($1: $30)))
    4
    =SOLDAN(A1;ARA(99999;BUL(” “;A1;SATIR($1: $30))))
    5
    Dizi formüldür
    =SOLDAN(A1;MAK(ÖZDEŞ(PARÇAAL(A1;SATIR($1: $30);1);” “)*(SATIR($1: $30))))
    6
    Dizi formüldür
    =SOLDAN(A1;BÜYÜK(1*EĞER(PARÇAAL(A1;SATIR($1: $30);1)=” “;SATIR($1: $30));1))
    7
    Dizi formüldür
    =SOLDAN(A1;UZUNLUK(A1)-KAÇINCI(“*”&” “&”*”;SAĞDAN(A1;SATIR($1: $30));0))

  • mustafa

    (17 Ekim 2011 - 14:36)

    Allah razı olsun. İyiki bu dünyada seni,n gibi adamlar var…

  • slm benim bir sorunum var

    (31 Ekim 2011 - 09:56)

    excel de proğramdan aldığım verilerin sayıların sonunda nokta var bu noktaları tek tek siliyorum örnek 167,71. şeklinde

  • Mustafa ÖZCAN

    (31 Ekim 2011 - 15:48)

    En kolay çözüm o sütunu seçin bul değiştir yaparak noktaları değiştirin.

  • Fatih A.

    (31 Ekim 2011 - 16:15)

    Hocam süpersiniz ellerinize sağlık

  • Cem YILMAZ

    (10 Temmuz 2012 - 09:46)

    İyi günler,
    benim sorum:
    örneğin 18:05:00 gibi bir saat verisi var elimde ve her verininde karşılığında 1-5 arasında değerler var.
    öncelikle bu saat verisininin saat kısmını ayırıp (18)karşılığındaki veryi ekleyip örnek bu saatin karşılığındaki veri 1 olsun.
    18+1=19 yapıp, bu 19'u ilk hücredeki değerimizin yerine 19:05:00 olarak yazdırabilir miyiz?
    HEDEF
    MEHMET ALİ 08:05:00 18:05:00 1 19:05:00

  • mehmet

    (22 Ekim 2012 - 20:47)

    iyi çalışmalar hocam benım sıze sormak ıstedığım su excel de maaş bordsu hazırlarken adı olan ları alfeye gote sılarken soy adını da sıralamaya alabılırmıyız mesala tabloda Abdulbaki İBRAHİM
    Abdulkerim GÜZEL
    Abdullah AĞDAL
    Abdurrahman KAYA
    Adem AYIK
    Adnan ÖZÇELİK
    Ahmet ACUN
    Ahmet GÜL
    Ahmet YILMIŞ
    Ali KAYA
    örneklemede ismleri ilesoy isimleri a ile başlayanları liste karışmadan sıralamak istiyorum msl Adem AYIK.Ahmet ACUN.Abdullah AĞDAL.Ahmet GÜL.Abdulkerim GÜZEL gibi nasıl yapabılırım

  • begüm

    (23 Ekim 2012 - 09:18)

    Merhaba hocam,
    O kadar faydalı oldu ki beni çok ağır bir iş yükünden kurtardınız. Emeğinize sağlık

  • mustafaozcan

    (23 Ekim 2012 - 20:58)

    Bu işlem için öncelikle ad soyadı birbirinden ayırmalısınız. Daha sonra sıralama yaparken önce ad sonra soyad alanlarına göre sırala derseniz sorununuz çözülür.

  • ali değirmencioğlu

    (31 Ekim 2012 - 11:19)

    ÜSTAT MERHABA;
    EXCELDE METİNLERİ BİRBİRİNDEN AYIRMAK İSTİYORUM AMA BİR TÜRLÜ FORMÜLÜ BULAMADIM YARDIMLARINIZI RİCA EDİYORUM

    ÖRNEK: ALİ GÖRENERHASAN GÖRENER
    YAVUZ YİĞİTCANMURAT YİĞİTCAN GİBİ

    ÖRNEKTE GÖRÜLDÜĞÜ GİBİ ALİ GÖRENEN VE HASAN GÖRENER İBİRBİRİNDEN AYIRMAK İSTİYORUM BUNU NASIL YAPABİLİRİZ

  • mustafaozcan

    (31 Ekim 2012 - 16:55)

    İsteğiniz günümüz şartlarında teknik olarak mümkün değil. Ancak bir insan GÖRENERHASAN yada YİĞİTCANMURAT metnine baktığında ayrımı doğru yapabilir.

  • Müslüm

    (26 Kasım 2012 - 16:01)

    Sayın Mustafa ÖZCAN … bu güne kadar bir sürü paylaşımlarınızdan yararlandım. ve çok işime yaradılar. bundan dolayı çok teşekkür ediyor ve allah sizden ve sizin gibi paylaşımcı şahsiyetlerden razı olsun diyorum…
    Şimdi sizden müsadeniz olursa bir yardım talep ediyorum.. Benim acemi denebilecek seviyede bir excel hayranı olduğumu da göz önünde bulundurarak yardımcı olursanız sevinirim.
    Aynı kitap içerisinde bir "Data" ve bir de Sipariş sayfam var. sipariş sayfasında bazı girişler karşılığında data sayfasından bilgiler getirerek işimi görüyorum… Bir tanede Form Denetimleri Birleşik Kutu (Açılır Kutu) koydum. Buradanda data listemi görüyor ve seçiyorum. ama sorun olan burada giriş yapılmadan bulup tıklama ile seçim yapılıyor.. benim istediğim birkaç harf yazınca içinde geçenleri alfabetik sıralayıp sadece onları getirsin.. yani google arama sayfasındaki gibi.. bu excel formulleri yada yönetilir bütonlarla mümkünmü. Makroya girmeden mümkünse yardımlarınızı bekliyorum. e-mail yazarsanız örnek belgeyi gönderebilirim… teşekkürler…

  • raci keleş

    (1 Aralık 2012 - 15:34)

    hocam benim bir sorum olacak,
    elimde şu şekilde sözlük var.

    Ada tavşanı Europen rabbit
    Adabımuaşeret Etiquette
    Adaçayı Sage tea
    Adaçayı bitkisi "Sage, garden sage"

    Ben ayrımı Büyük harfle başladığı için özelliğini kullanarak nasıl iki sutuna ayırabilirim, kolay gelsin.

  • serkan

    (23 Temmuz 2013 - 16:36)

    hocam ellerine sağlık süpersin. beni büyük bir yükten kurtardın allah razı olsun.

  • TANER KUNTER

    (27 Kasım 2013 - 17:58)

    Arkadaş çok teşekkürler.

  • hakan

    (5 Şubat 2014 - 15:40)

    Frau;Claudia;Herwig;13.06.1987;DE;37075;Göttingen;Bertheaustr.;

    tek hücrede olan bu yazıyı sütunlara nasıl ayırırım yardımcı olabilirmisiniz??

  • mustafaozcan

    (9 Şubat 2014 - 13:25)

    Veri – Metni Sütunlara Dönüştür menüsünü kullanarak yapabilirsiniz. Kriter olarak ; verirseniz hepsi farklı sütunlara ayrılacaktır.

  • ERSOY

    (30 Haziran 2014 - 10:22)

    Ayakkabı (955 ÇİFT)
    Bady(DZ) (7599 ADET)
    Battaniye
    mesela bunları nasıl ayıra bilirim mal tanımı 1 sütünde parantezli olanlar 1 sütünda olması lazım ama bazılarıda sadece mal tanımı bu formuller işime yaramadı benim istediğim parantezleri görsün ve içindekilerler birlikte baska bir sutuna atsın onları
    mesela
    Ayakkabı (955 ÇİFT) AYAKKABI (955 ÇİFT)
    Bady(DZ) (7599 ADET) bady (DZ) (7599 ADET)
    Battaniye Battaniye
    BÖYLE OLMASI LAZIM YARDIMCI OLABILIRMISINNIZ

  • mustafaozcan

    (9 Temmuz 2014 - 22:24)

    Sütunu seçip Veri – Metni Sütunlara dönüştür menüsünden Sınırlandırılmış – Diğer alanına ( girerek ayırabilirsiniz.

    Bu durumda aşağıdaki gibi iki kolon elde edersiniz
    Ayakkabı 955 ÇİFT)

    daha sonra 955 ÇİFT) verisinin bulunduğu sütunu seçip üstteki işlemi tekrarlayın sadece Diğer alanı için ) karakterini girin işleminiz tamam.

  • eray

    (23 Temmuz 2014 - 09:19)

    Merhaba İyi günler ;

    Benim şöyle bir problemim var. Örnek olarak ;

    ahmet yılmaz – 3821
    mehmet kurt – 3092
    hikmet mustafa yurt – 9087

    exelde tek sütunda bulunmakta. ben rakamları bir sutunda toplamak istiyorum . yardımcı olabilirimisin . şimdiden teşekkür ederim ilginiz için

  • mustafaozcan

    (25 Temmuz 2014 - 12:37)

    Sütunu seçip Veri, Metni Sütunlara dönüştür menüsünden Sınırlandırılmış, Diğer alanına – işareti girerek ayırabilirsiniz.

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir