Mustafa ÖZCAN - Kişisel Web Sitesi

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

mustafaozcan - 10.07.2009 02:30:00
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)

Taglar : ,

Kategoriler : İpucu (Tips & Tricks) | Programlar

Yorumlar (37) -

23.07.2009 12:42:26 #

Mustafa ÖZCAN

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

Mustafa ÖZCAN | Yanıtla

22.04.2010 01:49:14 #

Celal

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

Celal | Yanıtla

22.04.2010 04:39:23 #

Mustafa ÖZCAN

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.

Mustafa ÖZCAN | Yanıtla

22.04.2010 04:47:37 #

Celal

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

Celal | Yanıtla

05.10.2010 14:30:23 #

EMRAH

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

EMRAH | Yanıtla

05.10.2010 22:31:45 #

Mustafa ÖZCAN

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

Mustafa ÖZCAN | Yanıtla

06.10.2010 09:18:42 #

EMRAH

ilginiz için teşekkürler

EMRAH | Yanıtla

18.03.2011 09:17:37 #

emrah

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

emrah | Yanıtla

18.03.2011 23:13:45 #

Mustafa ÖZCAN

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

Mustafa ÖZCAN | Yanıtla

21.03.2011 20:36:05 #

yasin

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

yasin | Yanıtla

22.03.2011 21:13:08 #

Mustafa ÖZCAN

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 | Yanıtla

22.03.2011 20:37:47 #

AHMET

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.

AHMET | Yanıtla

22.03.2011 21:27:09 #

Mustafa ÖZCAN

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 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.

Mustafa ÖZCAN | Yanıtla

05.04.2011 23:21:52 #

yasin

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

yasin | Yanıtla

06.04.2011 22:36:53 #

Mustafa ÖZCAN

Ö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.

Mustafa ÖZCAN | Yanıtla

03.06.2011 11:32:15 #

chess

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

chess Turkey | Yanıtla

27.07.2011 15:42:25 #

Tarkan

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))

Tarkan Turkey | Yanıtla

17.10.2011 14:36:15 #

mustafa

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

mustafa Turkey | Yanıtla

31.10.2011 09:56:15 #

slm benim bir sorunum var

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

slm benim bir sorunum var Turkey | Yanıtla

31.10.2011 15:48:18 #

Mustafa ÖZCAN

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

Mustafa ÖZCAN Turkey | Yanıtla

31.10.2011 16:15:19 #

Fatih A.

Hocam süpersiniz ellerinize sağlık

Fatih A. Turkey | Yanıtla

10.07.2012 09:46:10 #

Cem YILMAZ

İ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

Cem YILMAZ Turkey | Yanıtla

22.10.2012 20:47:11 #

mehmet

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

mehmet Turkey | Yanıtla

23.10.2012 20:58:50 #

mustafaozcan

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.

mustafaozcan Turkey | Yanıtla

23.10.2012 09:18:19 #

begüm

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

begüm Turkey | Yanıtla

31.10.2012 11:19:03 #

ali değirmencioğlu

Ü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

ali değirmencioğlu Turkey | Yanıtla

31.10.2012 16:55:54 #

mustafaozcan

İ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.

mustafaozcan Turkey | Yanıtla

26.11.2012 16:01:34 #

Müslüm

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...

Müslüm Turkey | Yanıtla

01.12.2012 15:34:27 #

raci keleş

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.

raci keleş Turkey | Yanıtla

23.07.2013 16:36:54 #

serkan

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

serkan Turkey | Yanıtla

27.11.2013 17:58:02 #

TANER KUNTER

Arkadaş çok teşekkürler.

TANER KUNTER Turkey | Yanıtla

05.02.2014 15:40:14 #

hakan

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??

hakan Turkey | Yanıtla

09.02.2014 13:25:07 #

mustafaozcan

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

mustafaozcan Turkey | Yanıtla

30.06.2014 10:22:31 #

ERSOY

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

ERSOY Turkey | Yanıtla

09.07.2014 22:24:04 #

mustafaozcan

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.

mustafaozcan Turkey | Yanıtla

23.07.2014 09:19:51 #

eray

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

eray Turkey | Yanıtla

25.07.2014 12:37:07 #

mustafaozcan

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.

mustafaozcan Turkey | Yanıtla

Yorum ekle



  Country flag
biuquote
  • Yorum
  • Canlı önizleme
Loading






resimyok Mustafa ÖZCAN
Kıdemli Yazılım Geliştirici
E-mail Send mail

Reklam

Aylara Göre Yazılar

Eklenmiş Dosyalar

File Name Downloads
KlasoreKopyalaTasi-Yukle.reg 1421
KlasoreKopyalaTasi-Kaldir.reg 1214
autorun.inf.zip 1514
BuyukHarf-v3.1.2.html 3580
BuyukHarf-v3.1.2.js 2319
SadeceRakam-v1.5.1.html 3557
SadeceRakam-v1.5.1.js 2548
2009/6/BlogEngine-labels.tr.resx.zip 1319
jquery-1.2.6-intellisense.js 977
System.Web.Mvc.dll 1028
2010/9/FamilyShow-v-3-0-2.zip 10084
BuyukHarf-v3.1.1.js 821
BuyukHarf-v3.1.1.html 1419
buyukharf-v3.1.1.html 1306
sadecerakam-v1.5.1.html 2148
2010/2/FamilyShow-v-3-0-1.zip 856
2009/8/familyshow-v-3.zip 640
buyukharf-v3.1.2.html 1321
Family.Show-v.2.1.0.zip 829
2009/8/FamilyShow-v-3.zip 1006
2010/10/Excel-Ad-Soyad-Ayirmak.xls 2816
system.web.mvc.dll 668
buyukharf-v3.1.2.js 611
klasorekopyalatasi-yukle.reg 671
2010/2/familyshow-v-3-0-1.zip 469
2010/9/familyshow-v-3-0-2.zip 481
2011/7/APNTurkey.apk 29522
2009/6/blogengine-labels.tr.resx.zip 422
klasorekopyalatasi-kaldir.reg 593
2010/10/excel-ad-soyad-ayirmak.xls 545
2011/7/apnturkey.apk 1904
buyukharf-v3.1.1.js 438
2012/10/Android-Mobil-Tarayici.zip 981
2012/11/Android-ActivityIntent.zip 1009
2012/11/Android-SharedPreferences.zip 766
2012/11/SQLiteSample1.zip 799
2012/11/SQLiteSample2.zip 838
2012/12/StylesAndThemes.zip 818
2012/12/NotificationAndDialog.zip 780
2012/12/AsyncTask.zip 910
2012/12/LocationSample.zip 943
2012/12/test.kml 1052
2013/1/Android-Json-Sample.zip 1308
2012/12/locationsample.zip 249
sadecerakam-v1.5.1.js 27
family.show-v.2.1.0.zip 21
2012/10/android-mobil-tarayici.zip 15
2012/11/sqlitesample1.zip 17
2012/11/android-sharedpreferences.zip 17
2012/11/android-activityintent.zip 21
2012/11/sqlitesample2.zip 17
2012/12/notificationanddialog.zip 15
2012/12/asynctask.zip 14
2013/1/android-json-sample.zip 20
2012/12/stylesandthemes.zip 17
   

Online


Online Ziyaretçi : 52

Translate This Page

B l o g - E n g i n e