Excel eğitimi: metin, sayı ve tarih hücrelerini bölme (birden fazla sütuna ayırma)

Excel kullanırken, bazen bir hücredeki metni birden fazla hücreye bölmeniz gerekebilir. Bu eğitim üç bölüme ayrılmıştır: metin hücrelerini bölme, sayı hücrelerini bölme ve tarih hücrelerini bölme. Her bölüm, aynı sorunla karşılaştığınızda bölme işlemini nasıl yapacağınızı göstermek için farklı örnekler sunar.

1 Metin hücrelerini böl

Bu bölüm, bir metin hücresini birden fazla sütuna bölerken karşılaşacağınız durumları toplar ve size uygun yöntemleri sunar.

Örnek #1 Hücreleri virgül, boşluk veya başka bir ayraç ile böl

Bir metin hücresini virgül, boşluk, tire gibi belirli bir ayraç ile birden fazla sütuna bölmek için aşağıdaki yöntemlerden birini uygulayabilirsiniz.

Hücreleri ayraç ile Veriyi Böl özelliğiyle böl

Excel’in yerleşik özelliklerinden biri olan Veriyi Böl, hücreleri bölmek için sıkça kullanılır. Aşağıdaki ekran görüntüsünde gösterildiği gibi, Metin Dizileri sütunundaki hücreleri virgül ile bölmek için Veriyi Böl özelliğini şu şekilde uygulayabilirsiniz.

1. Virgül ile bölmek istediğiniz sütun aralığını seçin, Veri > Veriyi Böl’e tıklayın.

2. Metni Sütunlara Dönüştür Sihirbazı –3 Adımda1 iletişim kutusunda, Ayraçlı seçeneğini işaretli bırakın ve ardından İleri’ye tıklayın.

3. Metni Sütunlara Dönüştür Sihirbazı –3 Adımda2 iletişim kutusunda, ihtiyacınıza göre bir ayraç belirtin (bu örnekte sadece Virgül kutusunu işaretliyorum) ve ardından İleri’ye tıklayın.

4. Son adım iletişim kutusunda, düğmesine tıklayarak bölünen metinleri çıkış olarak vereceğiniz hücreyi seçin ve ardından Tamamla düğmesine tıklayın.

Ardından, seçili aralıktaki metinler virgül ile bölünerek farklı sütunlara yerleştirilir, aşağıda gösterildiği gibi.

Hücreleri ayraç ile formül kullanarak böl

Excel’de hücrelerdeki metinleri belirli bir ayraç ile bölmek için aşağıdaki formülü uygulayabilirsiniz.

Genel formül

=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

Argümanlar

Şimdi bu formülü uygulamaya devam edin.

Aşağıdaki ekran görüntüsünde gösterildiği gibi, önce farklı hücrelere1,2,3... şeklinde bir yardımcı satır oluşturmanız gerekir.

Not: Buradaki1,2,3... metin dizisinin birinci, ikinci ve üçüncü alt dizesini temsil eder.

2.1 numaralı hücrenin altındaki bir hücreyi seçin, aşağıdaki formülü kopyalayın veya girin ve ilk alt dizeyi almak için Enter tuşuna basın. Sonuç hücresini seçin, Otomatik Doldurma Tutamacı’nı sağa ve aşağı sürükleyerek diğer alt dizeleri alın. Ekran görüntüsüne bakın:

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(D$4-1)*LEN($B5)+1,LEN($B5)))

Not: Formülde, “,” B5’teki metin dizisini bölmek için kullanılan ayraçtır. İhtiyacınıza göre değiştirebilirsiniz.

Hücreleri ayraç ile harika bir araçla böl

Burada, Kutools for Excel’in Hücreleri Böl özelliğini kullanarak hücreleri belirli bir ayraç ile kolayca ayrı sütunlara veya satırlara bölebilirsiniz.

1. Kutools for Excel’i yükledikten sonra, bölmek istediğiniz metin dizilerinin aralığını seçin ve ardından Kutools > Birleştir & Böl > Hücreleri Böl’e tıklayın.

2. Hücreleri Böl iletişim kutusunda aşağıdaki gibi yapılandırmanız gerekir.

3. Sonraki Hücreleri Böl iletişim kutusunda, alt dizeleri çıkış olarak vereceğiniz bir hücre seçin ve ardından Tamam’a tıklayın.

Ardından, alt dizeler yukarıdaki2. adımda belirttiğiniz şekilde farklı sütunlara veya satırlara bölünür.

Veriyi Böl:

Satırlara Böl:

Örnek #2 Hücreleri belirli bir uzunluğa göre böl

Metin dizisini belirli bir uzunluğa göre bölmek için aşağıdaki yöntemlerden faydalanabilirsiniz.

Hücreleri belirli bir uzunluğa göre Veriyi Böl özelliğiyle böl

Veriyi Böl özelliği, seçili hücrelerdeki metin dizilerini belirli bir uzunluğa göre bölmek için Sabit genişlik seçeneği sunar.

Aşağıdaki ekran görüntüsünde gösterildiği gibi, B5:B9 aralığındaki metinleri her3 karakterde bir sütuna bölmek için aşağıdaki adımları uygulayabilirsiniz.

Bölmek istediğiniz metin dizilerinin bulunduğu hücreleri seçin.

Veri > Veriyi Böl’e tıklayın.

3. Metni Sütunlara Dönüştür Sihirbazı –3 Adımda1 iletişim kutusunda, Sabit genişlik seçeneğini seçin ve İleri’ye tıklayın.

4. Ardından 3 Adımda2 iletişim kutusu açılır. Veri önizleme bölümünde, eksen üzerinde istediğiniz konuma tıklayarak bir bölme çizgisi (oklu bir çizgi) oluşturun. Tüm bölme çizgilerini oluşturduktan sonra, İleri’ye tıklayın.

Bu örnekte, metin dizisinde her3 karakter için bölme çizgileri oluşturuyorum.

5. Son adım sihirbazında, bölünen metinleri çıkış olarak vereceğiniz bir hücre seçin ve ardından Tamamla düğmesine tıklayın.

Artık seçili hücrelerdeki metin dizileri her3 karakterde bir bölünerek aşağıdaki ekran görüntüsünde gösterildiği gibi sütunlara ayrılmıştır.

Hücreleri belirli bir uzunluğa göre harika bir araçla böl

Excel’de hücreleri belirli bir uzunluğa göre bölmek için, Kutools for Excel’in Hücreleri Böl özelliğini kolayca kullanabilirsiniz.

1. Belirli uzunlukta bölmek istediğiniz metin dizisi hücrelerini seçin, Kutools > Birleştir & Böl > Hücreleri Böl’e tıklayın.

2. Hücreleri Böl iletişim kutusunda aşağıdaki gibi yapılandırmanız gerekir.

3. Sonraki Hücreleri Böl iletişim kutusunda, bölünen metinleri yerleştirmek için bir hücre seçin ve ardından Tamam’a tıklayın.

Ardından, seçili hücrelerdeki metin dizileri belirli bir uzunluğa göre bölünerek farklı sütunlara yerleştirilir.

Örnek #3 Hücreleri belirli bir kelimeye göre böl

Aşağıdaki ekran görüntüsünde gösterildiği gibi, B5:B9 aralığındaki metin dizilerini “sales” kelimesine göre bölmek için bu bölümde verilen formülleri uygulayabilirsiniz.

Bir hücredeki belirli bir kelimeden önceki alt diziyi alın

LEFT ve FIND fonksiyonlarına dayalı bir formül uygulayarak, metin dizisinde belirli bir kelimeden önceki alt diziyi alabilirsiniz.

Genel formül

=LEFT(A1,FIND("certain_word", A1)-1)

Argümanlar

1. Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve belirli kelimeden önceki alt diziyi almak için Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek formülü diğer hücrelere uygulayın.

=LEFT(B5,FIND("sales",B5)-1)

Bir hücredeki kelimeden sonraki alt diziyi alın

Kelimeden önceki alt diziyi aldıktan sonra, aşağıdaki formülü uygulayarak kelimeden sonraki alt diziyi almanız gerekir.

Genel formül

=TRIM(MID(A1,SEARCH("certain_word",A1)+LEN("certain_word"),255))

Argümanlar

1. İlk sonuç hücresinin (D5) yanındaki boş bir hücreyi seçin.

2. Aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin, Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer sonuçları alın.

=TRIM(MID(B5,SEARCH("sales",B5)+LEN("sales"),255))

Artık metin dizilerini bir kelimeye göre farklı sütunlara böldünüz.

Örnek #4 Hücreleri satır sonuna göre böl

Bu bölümde, Excel’de metin hücrelerini satır sonuna göre bölmek için farklı yöntemler gösterilmektedir.

Hücreleri satır sonuna göre Veriyi Böl özelliğiyle böl

Veriyi Böl özelliği, Excel’de hücreleri satır sonuna göre bölmek için uygulanabilir. Şu adımları izleyebilirsiniz.

Satır sonuna göre bölmek istediğiniz hücre aralığını seçin.

Veri > Veriyi Böl’e tıklayın.

3. Metni Sütunlara Dönüştür Sihirbazı –3 Adımda1 iletişim kutusunda, Ayraçlı seçeneğini seçin ve ardından İleri’ye tıklayın;

4.3 Adımda2 iletişim kutusunda, mevcut Ayraç seçimlerini kaldırın, Diğer kutusunu işaretleyin ve ardından Ctrl + J kısayoluna basın. Metin kutusunda yalnızca küçük bir nokta görünecek, ardından Veri önizleme kutusunda metinler satır sonlarına göre bölünür. İleri’ye tıklayın.

5. Son adım sihirbazında, bölünen metinleri çıkış olarak vereceğiniz bir hücre seçin ve ardından Tamamla düğmesine tıklayın.

Ardından, seçili hücrelerdeki metinler satır sonlarına göre farklı sütunlara bölünür.

Hücreleri satır sonuna göre formül ile böl

Aşağıdaki formüller de Excel’de hücreleri satır sonuna göre bölmenize yardımcı olabilir.

Yukarıdaki örnekte olduğu gibi, bölme işleminden sonra farklı sütunlarda üç alt dize elde edeceksiniz.

İlk satır sonundan önceki alt diziyi alın

Öncelikle, hücredeki ilk satır sonundan önceki alt diziyi bölmek için LEFT ve SEARCH fonksiyonlarına dayalı bir formül uygulayabiliriz.

Genel formül

=LEFT(cell, SEARCH(CHAR(10),cell,1)-1)

Argümanlar

1. Boş bir hücre seçin (bu örnekte D5), aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer hücrelerin ilk satır sonundan önceki alt dizelerini alın.

=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)

İlk ve ikinci satır sonu arasındaki alt diziyi alın

Bir hücredeki ilk ve ikinci satır sonu arasındaki alt diziyi almak için aşağıdaki formül size yardımcı olabilir.

Genel formül

=MID(cell,SEARCH(CHAR(10),cell)+1,SEARCH(CHAR(10),cell,SEARCH(CHAR(10),cell)+1)-SEARCH(CHAR(10),cell)-1)

Argümanlar

1. D5’in yanındaki bir hücreyi (E5) seçin, aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer hücrelerin ilk ve ikinci satır sonu arasındaki alt dizilerini alın.

=MID(B5, SEARCH(CHAR(10),B5) +1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) -1)

İkinci satır sonundan sonraki alt diziyi alın

İlk adım, aşağıdaki formül ile ikinci satır sonundan sonraki alt diziyi almaktır.

Genel formül

=RIGHT(cell,LEN(cell) - SEARCH(CHAR(10),cell, SEARCH(CHAR(10), cell) +1))

Argümanlar

1. (Bu örnekte F5) bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer hücrelerin ikinci satır sonundan sonraki alt dizilerini alın.

=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) +1))

Hücreleri satır sonuna göre VBA ile böl

Bu bölümde, Excel’de seçili hücrelerdeki metinleri satır sonuna göre kolayca bölmek için bir VBA kodu sunulmaktadır.

1. Microsoft Visual Basic for Applications penceresini açmak için Alt + F11 tuşlarına basın.

2. Microsoft Visual Basic for Applications penceresinde, Ekle > Modül’e tıklayın. Ardından aşağıdaki VBA kodunu Kod penceresine kopyalayın.

VBA kodu: Excel’de hücreleri satır sonuna göre böl

Sub ExtendOffice_SplitRangeTex()
'Updated by Extendoffice 20211116
Dim xStr() As String
Dim xRg As Range
Dim xCell As Range
Dim xI As Integer
Set xRg = Application.InputBox("Please select the range of cells where you want to split by line break:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
For xI = 1 To xRg.Count
    Set xCell = xRg.Item(xI)
    xStr = VBA.Split(xCell.Value, vbLf)
    xCell.Resize(1, UBound(xStr) + 1).Offset(0, 1) = xStr
Next
End Sub

3. Kodu çalıştırmak için F5 tuşuna basın. Ardından bir Kutools for Excel iletişim kutusu açılır, satır sonuna göre bölmek istediğiniz hücre aralığını seçmeniz ve son olarak Tamam’a tıklamanız gerekir.

Ardından, seçili hücrelerdeki metinler satır sonlarına göre anında farklı sütunlara bölünür.

Hücreleri satır sonuna göre harika bir araçla böl

Burada, Kutools for Excel’in Hücreleri Böl yardımcı aracını tanıtıyoruz. Bu araç ile, seçili hücrelerdeki metinleri toplu olarak yalnızca birkaç tıklama ile satır sonuna göre bölebilirsiniz.

Satır sonuna göre bölmek istediğiniz hücre aralığını seçin.

Kutools > Birleştir & Böl > Hücreleri Böl’e tıklayın.

Hücreleri Böl iletişim kutusunda aşağıdaki ayarları yapmanız gerekir.

4. Sonraki açılan Hücreleri Böl iletişim kutusunda, bölünen metinleri yerleştirmek için boş bir hücre seçin ve Tamam’a tıklayın.

Ardından, seçili hücrelerdeki metinler satır sonlarına göre farklı sütunlara bölünür, aşağıdaki ekran görüntüsünde gösterildiği gibi.

Örnek #5 Hücreleri yalnızca ilk veya son ayraç ile böl

Bazen, bir hücreyi yalnızca ilk veya son ayraç ile ikiye bölmeniz gerekebilir, aşağıdaki formülleri deneyebilirsiniz.

Hücreleri ilk ayraç ile formüllerle böl

Aşağıdaki ekran görüntüsünde gösterildiği gibi, B5:B9 aralığındaki her hücreyi ilk boşluğa göre ikiye bölmek için iki formül uygulamanız gerekir.

İlk boşluktan önceki alt diziyi böl

İlk boşluktan önceki alt diziyi bölmek için, LEFT ve FIND fonksiyonlarına dayalı bir formül kullanabilirsiniz.

Genel formül

=LEFT(cell,FIND("delimiter",cell)-1)

Argümanlar

1. Alt diziyi çıkış olarak vereceğiniz bir hücre seçin (bu örnekte D5), aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer hücrelerin alt dizilerini alın.

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

İlk boşluktan sonraki alt diziyi böl

Ardından, bir hücredeki ilk boşluktan sonraki alt diziyi almak için aşağıdaki formülü uygulayın.

Genel formül

=RIGHT(cell,LEN(cell)-FIND("delimiter",cell))

Argümanlar

Aşağıdaki formülü E5 hücresine kopyalayın veya girin ve sonucu almak için Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer hücrelerin son boşluktan sonraki alt dizilerini alın.

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

Not: Formüllerde, hücre başvurusunu ve ayraç karakterini ihtiyacınıza göre değiştirebilirsiniz.

Hücreleri son ayraç ile formüllerle böl

Aşağıdaki ekran görüntüsünde gösterildiği gibi, B5:B9 aralığındaki hücrelerdeki metinleri son boşluğa göre ikiye bölmek için bu bölümde verilen iki formül size yardımcı olabilir.

Son ayraçtan önceki metni alın

Bir hücredeki son ayraçtan önceki metni almak için aşağıdaki formülü uygulayabilirsiniz.

Genel formül

=LEFT(B5,SEARCH("^",SUBSTITUTE(cell,"delimiter","^",LEN(cell)-LEN(SUBSTITUTE(cell,"delimiter",""))))-1)

Argümanlar

1. Sol alt diziyi yerleştirmek için bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek formülü diğer hücrelere uygulayın.

=LEFT(B5,SEARCH("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)

Son ayraçtan sonraki metni alın

Şimdi, bu örnekte son boşluğun sağındaki metni almamız gerekiyor.

Genel formül

=TRIM(RIGHT(SUBSTITUTE(cell,"delimiter",REPT("delimiter",LEN(cell))),LEN(cell)))

Argümanlar

1. Sağ alt diziyi yerleştirmek için bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek formülü diğer hücrelere uygulayın.

=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))),LEN(B5)))

Not: Formüllerde, hücre başvurusunu ve ayraç karakterini ihtiyacınıza göre değiştirebilirsiniz.

Örnek #6 Hücreleri büyük harfe göre böl

Bu bölümde, hücrelerdeki kelimeleri büyük harfe göre bölmek için kullanıcı tanımlı bir fonksiyon tanıtılmaktadır.

Microsoft Visual Basic for Applications penceresini açmak için Alt + F11 tuşlarına basın.

Microsoft Visual Basic for Applications penceresinde, Ekle > Modül’e tıklayın, ardından aşağıdaki VBA kodunu Kod penceresine kopyalayın.

VBA kodu: Excel’de hücreleri büyük harfe göre böl

Function GetName(s As String, Num As Long) As String
'Updated by Extendoffice 20211116
  With CreateObject("VBSCript.RegExp")
    .Global = True
    .Pattern = "[A-Z][a-z]+|[A-Z]"
    GetName = .Execute(s).Item(Num - 1)
  End With
End Function

Microsoft Visual Basic for Applications penceresini kapatmak için Alt + Q tuşlarına basın.

D5 hücresini (bu örnekte) seçin, ilk büyük harften önceki kelimeyi çıkış olarak almak için aşağıdaki formülü girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı sağa ve ardından aşağı sürükleyerek diğer kelimeleri alın.

=IFERROR(GetName($B5,COLUMNS($D:D)),"")

Not: Kodda, $B5 bölmek istediğiniz hücre, $D:D ise sonuç hücresinin sütunudur. Kendi verilerinize göre değiştirebilirsiniz.

Örnek #7 Hücrelerdeki isimleri böl

Bir çalışma sayfanızda tam isimlerin bulunduğu bir sütun olduğunu ve tam ismi ayrı sütunlara, örneğin ad ve soyadı veya ad, ikinci ad ve soyadı olarak bölmek istediğinizi varsayalım. Bu bölümde, bu sorunları çözmenize yardımcı olacak ayrıntılı adımlar listelenmiştir.

Tam ismi ad ve soyad olarak böl

Aşağıdaki ekran görüntüsünde gösterildiği gibi, ad, ikinci ad ve soyad bir boşlukla ayrılmıştır. Sadece ad ve soyadı tam isimden ayırıp farklı sütunlara yerleştirmek için aşağıdaki yöntemlerden birini uygulayabilirsiniz.

1) Formüllerle tam ismi ad ve soyad olarak böl

Tam ismi ad ve soyad olarak bölmek için iki formülü ayrı ayrı uygulamanız gerekir.

Tam isimden adı böl

Tam isimden adı bölmek için, LEFT ve SEARCH fonksiyonlarına dayalı bir formül uygulayabilirsiniz.

Genel formül

=LEFT(cell, SEARCH(" ", cell) -1)

Adı çıkış olarak vereceğiniz bir hücre seçin.

Aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer tam isimlerden adları bölün.

=LEFT(B5, SEARCH(" ", B5) -1)

Artık, belirttiğiniz hücre aralığındaki tüm adları tam isimlerden böldünüz, şimdi aşağıdaki formülü uygulayarak soyadları bölmeye devam edin.

Tam isimden soyadı böl

Genel formül

=RIGHT(cell, LEN(cell) - SEARCH("^", SUBSTITUTE(cell," ", "^", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))

Ad hücresinin yanındaki bir hücreyi seçin.

Aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer tam isimlerden soyadları alın.

=RIGHT(B5, LEN(B5) - SEARCH("^", SUBSTITUTE(B5," ", "^", LEN(B5) - LEN(SUBSTITUTE(B5, " ", "")))))

Not: Formüllerde, B5 bölmek istediğiniz tam isim hücresidir. İhtiyacınıza göre değiştirebilirsiniz.

2) Harika bir araçla tam ismi ad ve soyad olarak böl

Birçok Excel kullanıcısı için formülleri hatırlamak zordur. Burada, Kutools for Excel’in İsmi Böl özelliğini öneriyoruz. Bu özellik ile tam ismi yalnızca birkaç tıklama ile kolayca ad ve soyad olarak bölebilirsiniz.

Tam isim hücrelerinin aralığını seçin, Kutools > Birleştir & Böl > İsmi Böl’e tıklayın.

İsmi Böl iletişim kutusunda, Bölme Türü bölümünde yalnızca Adı ve Soyadı kutularını işaretleyin ve ardından Tamam’a tıklayın.

İpucu: Seçili aralık, Bölme Aralığı kutusunda görüntülenir, ihtiyacınıza göre değiştirebilirsiniz.

Ardından başka bir İsmi Böl iletişim kutusu açılır, bir hedef hücre seçin ve Tamam’a tıklayın.

Ardından, seçili hücrelerdeki tam isimler toplu olarak ad ve soyad olarak bölünür, aşağıdaki ekran görüntüsünde gösterildiği gibi.

Tam ismi ad, ikinci ad ve soyad olarak böl

Aşağıdaki ekran görüntüsünde gösterildiği gibi, ad, ikinci ad ve soyad bir boşlukla ayrılmış bir tam isim sütunu var. Tam ismi ad, ikinci ad ve soyad olarak bölüp farklı sütunlara yerleştirmek için aşağıdaki yöntemlerden faydalanabilirsiniz.

1) Tam ismi Veriyi Böl ile ad, ikinci ad ve soyad olarak böl

Yerleşik Veriyi Böl özelliği, Excel’de tam ismi kolayca ad, ikinci ad ve soyad olarak bölmenize yardımcı olur.

Yukarıda belirtilen adımları izleyerek Veriyi Böl özelliğini uygulayabilirsiniz.

Not:3 Adımda2 sihirbazında yalnızca Boşluk kutusunu işaretleyin.

2) Formüllerle tam ismi ad, ikinci ad ve soyad olarak böl

Excel’de tam ismi ad, ikinci ad ve soyad olarak bölmek için formüller de uygulayabilirsiniz.

Tam ismi bölmek için kullanılan genel formüller

Adı böl

=LEFT(cell,SEARCH(" ", cell)-1)

İkinci adı böl

=MID(cell, SEARCH(" ", cell) +1, SEARCH(" ", cell, SEARCH(" ", cell)+1) - SEARCH(" ", cell)-1)

Soyadı böl

=RIGHT(cell,LEN(cell) - SEARCH(" ",cell, SEARCH(" ",cell,1)+1))

Ardından, ad, ikinci ad ve soyadı almak için formülleri farklı hücrelerde uygulayın.

1. D5 hücresine aşağıdaki formülü girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer tam isimlerin adlarını alın.

=LEFT(B5,SEARCH(" ", B5)-1)

2. E5 hücresine aşağıdaki formülü girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer ikinci adları alın.

=MID(B5, SEARCH(" ", B5) +1, SEARCH(" ", B5, SEARCH(" ", B5)+1) - SEARCH(" ", B5)-1)

3. Soyadları almak için, F5 hücresine aşağıdaki formülü girin ve Enter’a basın, ardından sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı ihtiyacınız olan hücrelere sürükleyin.

=RIGHT(B5,LEN(B5) - SEARCH(" ",B5, SEARCH(" ",B5,1)+1))

3) Harika bir araçla tam ismi ad, ikinci ad ve soyad olarak böl

Burada, Kutools for Excel’in İsmi Böl özelliği tanıtılmaktadır. Bu özellik ile tam ismi yalnızca birkaç tıklama ile ad, ikinci ad ve soyad olarak bölebilirsiniz.

Bölmek istediğiniz tam isim hücrelerini seçin ve ardından Kutools > Birleştir & Böl > İsmi Böl’e tıklayın.

İsmi Böl iletişim kutusunda aşağıdaki gibi yapılandırmanız gerekir.

Sonraki açılan İsmi Böl iletişim kutusunda, bölünen metinleri çıkış olarak vereceğiniz bir hücre seçin ve ardından Tamam’a tıklayın. Aşağıdaki demoya bakın.

Örnek #8 Hücrelerdeki metin ve sayıları böl

Aşağıdaki ekran görüntüsünde gösterildiği gibi, karışık metin ve sayılardan oluşan bir metin dizisi listeniz olduğunu ve metin ile sayıları ayırıp farklı sütunlara yerleştirmek istediğinizi varsayalım. Sizin için dört yöntem sunuyoruz.

Metin ve sayıları formüllerle böl

Aşağıdaki formüller ile bir hücredeki metin ve sayıları iki ayrı hücreye bölebilirsiniz.

Genel formüller

Hücreden metni alın

=LEFT(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))-1)

Hücreden sayıları alın

=RIGHT(cell,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))+1)

Ardından, ayrı metin ve sayıları almak için bu iki formülü farklı hücrelerde uygulayın.

Listedeki ilk metin dizisinin metnini yerleştirmek için boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek listedeki diğer metin dizilerinin metinlerini alın.

=LEFT(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))-1)

İlk sonuç hücresinin (D5) yanındaki bir hücreyi (E5) seçin, aşağıdaki formülü kopyalayın veya girin ve Enter’a basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek listedeki diğer metin dizilerinin sayılarını alın.

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

Metin ve sayıları Flash Fill (2013 ve sonrası sürümler) ile böl

Excel2013 veya sonraki sürümleri kullanıyorsanız, bir hücredeki metin ve sayıları iki sütuna bölmek için yerleşik Flash Fill özelliğini kullanabilirsiniz.

Not: Flash Fill özelliğinin çalışması için, sonuç sütunlarını orijinal metin dizisi sütununun yanına yerleştirmeniz gerekir. Örneğin, orijinal metin dizileri B sütununda ise, ayrılan metin ve sayılar C ve D sütunlarında olmalıdır. Ekran görüntüsüne bakın:

İlk metin dizisi hücresinin (D5) metnini C5 hücresine manuel olarak yazın.

İkinci metin dizisi hücresinin (D6) metnini C6 hücresine yazmaya devam edin.

C7 hücresini etkinleştirin, Veri > Flash Fill’e tıklayın.

Ardından, diğer metin dizilerinin metinleri otomatik olarak hücrelere doldurulur, aşağıdaki ekran görüntüsünde gösterildiği gibi.

1’den3’e kadar olan adımları tekrarlayarak D sütununda sayıları alın.

Notlar:

Metin ve sayıları Kullanıcı Tanımlı Fonksiyon ile böl

Listedeki metin dizileri düzenli değilse, yukarıdaki iki yöntem yanlış sonuçlar döndürebilir, aşağıdaki ekran görüntüsünde gösterildiği gibi.

Burada, metin ve sayıları bir hücreden iki sütuna ayırmak için, sayıların metin dizisinde nerede olduğuna bakılmaksızın, kullanıcı tanımlı bir fonksiyon tanıtıyoruz. Ekran görüntüsüne bakın:

Alt + F11 tuşlarına basın.

Açılan Microsoft Visual Basic for Applications penceresinde, Ekle > Modül’e tıklayın ve ardından aşağıdaki VBA kodunu Kod penceresine kopyalayın.

VBA kodu: Bir hücredeki metin ve sayıları iki sütuna böl

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Update by Extendoffice 20211105
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

Microsoft Visual Basic for Applications penceresini kapatmak için Alt + Q tuşlarına basın.

İlk metin dizisinin metnini çıkış olarak vereceğiniz bir hücre seçin, aşağıdaki formülü girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek aynı listedeki diğer metin dizilerinin metinlerini alın.

=SplitText(B5,FALSE)

İlk metin sonucu hücresinin yanındaki bir hücreyi seçin, sayıları çıkış olarak almak için aşağıdaki formülü girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer metin dizilerinin sayılarını alın.

=SplitText(B5,TRUE)

Metin ve sayıları harika bir araçla böl

Burada, bir hücredeki metin ve sayıları aynı anda iki sütuna ayırmanın en kolay yolunu öneriyoruz. Kutools for Excel’in Hücreleri Böl özelliğini uygulayarak bu sorunu yalnızca birkaç tıklama ile çözebilirsiniz.

Metin ve sayıları iki sütuna ayırmak istediğiniz metin dizisi hücrelerini seçin.

Kutools > Birleştir & Böl > Hücreleri Böl’e tıklayın.

Hücreleri Böl iletişim kutusunda aşağıdaki ayarları yapmanız gerekir.

3.1) Seçili aralık, Bölme aralığı kutusunda listelenir ve ihtiyacınıza göre yeni bir aralık seçmek için düğmesine tıklayabilirsiniz;

3.2) Tür bölümünde, Satırlara Böl veya Veriyi Böl seçeneğini seçin;

3.3) Bölme türü bölümünde, Metin ve Sayıya Göre Böl seçeneğini işaretleyin;

3.4) Tamam düğmesine tıklayın.

Sonraki Hücreleri Böl iletişim kutusunda, ayrılan metin ve sayıları çıkış olarak vereceğiniz bir hücre seçin ve ardından Tamam’a tıklayın.

Ardından, seçili hücrelerdeki metin ve sayılar aynı anda iki sütuna bölünür, aşağıdaki ekran görüntüsünde gösterildiği gibi.

2. Sayı hücrelerini böl

Bu bölümde, sayı hücrelerini iki durumda nasıl böleceğiniz anlatılmaktadır: birden fazla rakamdan oluşan bir sayıyı tek tek rakamlara bölmek ve sayıyı ondalıkta bölerek tamsayı ve ondalık kısımları ayırmak.

Örnek #1: Hücreleri rakamlara göre böl

Birden fazla rakamdan oluşan bir sayıyı farklı sütunlarda tek tek rakamlara bölmek istiyorsanız, aşağıdaki yöntemlerden birini deneyin.

Bir hücredeki sayıyı formül ile tek tek rakamlara böl

Aşağıdaki formül, bir sayıyı tek tek rakamlara bölüp farklı sütunlara yerleştirmenize yardımcı olabilir.

Genel formül

=MID($A1, COLUMNS($A$1:A$1),1)

Argüman

İlk rakamı çıkış olarak vereceğiniz boş bir hücre seçin, aşağıdaki formülü girin ve Enter tuşuna basın.

=MID($B3, COLUMNS($B$3:B$3),1)

Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı sağa sürükleyerek diğer rakamları alın. Tüm sonuç hücrelerini seçili tutun ve ardından Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer sayıların tek tek rakamlarını alın.

Not: Bu formülde, B3 tek tek rakamlara bölmek istediğiniz sayıyı içeren hücredir ve ihtiyacınıza göre değiştirebilirsiniz.

Bir hücredeki sayıyı VBA ile tek tek rakamlara böl

Aşağıdaki VBA kodu da Excel’de bir hücredeki sayıyı tek tek rakamlara bölmenize yardımcı olabilir. Şu adımları izleyebilirsiniz.

Microsoft Visual Basic for Applications penceresini açmak için Alt + F11 tuşlarına basın.

Microsoft Visual Basic for Applications penceresinde, Ekle > Modül’e tıklayın ve ardından aşağıdaki VBA kodunu Kod penceresine kopyalayın.

VBA kodu: Excel’de sayıyı tek tek rakamlara böl

Sub SplitNumberIntoDigits()
'Updateby Extendoffice 2021118
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Kutools for Excel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
 xInt = InputRng.Row

Application.ScreenUpdating = False

For Each Rng In InputRng
    xValue = Rng.Value
    xRow = (Rng.Row - xInt) + 1
    For I = 1 To VBA.Len(xValue)
        OutRng.Cells(xRow, I).Value = VBA.Mid(xValue, I, 1)
    Next
Next
Application.ScreenUpdating = True
End Sub

Kodu çalıştırmak için F5 tuşuna basın, ardından bir Kutools for Excel iletişim kutusu açılır, bölmek istediğiniz sayı hücrelerinin aralığını seçmeniz ve ardından Tamam’a tıklamanız gerekir.

İkinci Kutools for Excel iletişim kutusu açılır, rakamları çıkış olarak vereceğiniz bir hücre seçin ve ardından Tamam’a tıklayın.

Not: Bu kod, bir kelimeyi de tek tek harflere bölebilir.

Ardından, seçili hücrelerdeki sayılar tek tek rakamlara bölünerek farklı sütunlara yerleştirilir.

Bir sayıyı harika bir araçla kolayca tek tek rakamlara böl

Kutools for Excel’in Hücreleri Böl özelliği, Excel’de sayıları kolayca tek tek rakamlara bölmenize yardımcı olan pratik bir araçtır.

Kutools for Excel’i yükledikten sonra, bölmek istediğiniz sayı hücrelerinin aralığını seçin, Kutools > Birleştir & Böl > Hücreleri Böl’e tıklayın.

Hücreleri Böl iletişim kutusunda aşağıdaki ayarları yapın.

2.1) Bölme aralığı bölümünde, seçtiğiniz aralığı metin kutusunda görebilirsiniz. İhtiyacınıza göre düğmesine tıklayarak aralığı değiştirebilirsiniz;

2.2) Tür bölümünde, ihtiyacınıza göre Satırlara Böl veya Veriyi Böl seçeneğini seçin;

2.3) Bölme türü bölümünde, Genişlik belirt seçeneğini işaretleyin, metin kutusuna1 sayısını girin;

2.4) Tamam düğmesine tıklayın.

Açılan Hücreleri Böl iletişim kutusunda, rakamları çıkış olarak vereceğiniz boş bir hücre seçin ve ardından Tamam’a tıklayın.

Ardından, seçili hücrelerdeki sayılar aynı anda tek tek rakamlara bölünür.

Örnek #2 Sayıyı ondalıkta böl

Bu bölümde, Excel’de bir sayıyı tamsayı ve ondalık kısımlara bölmek için birkaç yöntem anlatılmaktadır.

Sayıyı ondalıkta formüllerle böl

TRUNC fonksiyonu, Excel’de bir sayıyı ondalıkta bölmek için uygulanabilir. Şu adımları izleyebilirsiniz.

Genel formüller

Tamsayı kısmını alın

=TRUNC(A1)

Ondalık kısmı alın

=A1-TRUNC(A1)

Argüman

Şimdi, Excel’de belirli bir hücre aralığındaki sayıları ondalıkta bölmek için bu iki formülü uygulayabilirsiniz.

İlk sayı hücresinin tamsayı kısmını yerleştirmek için bir hücre seçin, aşağıdaki formülü girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer sayı hücrelerinin tamsayılarını alın.

=TRUNC(B5)

İlk sonuç hücresinin yanındaki bir hücreyi seçin, ondalık kısmı yerleştirmek için aşağıdaki formülü girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer sayı hücrelerinin ondalıklarını alın.

=B5-TRUNC(B5)

Sayıyı ondalıkta Veriyi Böl özelliğiyle böl

Excel’de sayıyı ondalıkta bölmek için Veriyi Böl özelliğini uygulayabilirsiniz.

Ondalıkta bölmek istediğiniz sayı hücrelerinin aralığını seçin ve ardından Veri > Veriyi Böl’e tıklayın.

Metni Sütunlara Dönüştür Sihirbazı –3 Adımda1 iletişim kutusunda, Ayraçlı seçeneğini seçin ve İleri’ye tıklayın.

3 Adımda2 iletişim kutusunda, yalnızca Diğer kutusunu işaretleyin, metin kutusuna bir nokta girin ve ardından İleri’ye tıklayın.

4. 3 Adımda3 iletişim kutusunda, düğmesine tıklayarak bölünen tamsayı ve ondalıkları çıkış olarak vereceğiniz bir hücre seçin ve son olarak Tamamla düğmesine tıklayın. Ekran görüntüsüne bakın:

Ardından, seçili hücrelerdeki tamsayı ve ondalık kısımlar aşağıdaki ekran görüntüsünde gösterildiği gibi ayrılır. Ancak, ondalık kısım negatif işaretini kaybeder, bu kullanıcı için gerekli olabilir.

Sayıyı ondalıkta Flash Fill (2013 ve sonrası sürümler) ile böl

Yukarıdaki örnekte, hücrelerdeki metin ve sayıları bölmek için Flash Fill fonksiyonunun kullanımını tanıttık, burada da sayıyı ondalıkta bölmek için aynı yöntemi tanıtacağız.

Birkaç örnek girin. Bu örnekte, B5’in tamsayı kısmını C5’e, B6’nın tamsayı kısmını C6’ya giriyoruz. Ekran görüntüsüne bakın:

Not: Negatif sayılar için, eksi işaretini de girmeyi unutmayın.

Doldurmak istediğiniz sütundaki örnekleri de içeren hücreleri seçin, Veri > Flash Fill’e tıklayın.

Ardından, belirtilen sayı hücrelerinden tamsayılar çıkarılır, aşağıdaki ekran görüntüsünde gösterildiği gibi.

Aynı sayı hücrelerinden ondalıkları bölmek için yukarıdaki adımları tekrarlayın.

3. Tarih hücrelerini böl

Sıkça karşılaşılan bir diğer durum da tarih hücrelerini bölmektir. Tarih hücrelerini ayrı gün, ay ve yıl olarak veya tarih ve saat olarak ayırmanız gerekiyorsa, ihtiyacınıza göre aşağıdaki çözümü kullanın.

Örnek #1 Tarih hücrelerini ayrı gün, ay ve yıl olarak böl

B5:B9 aralığında bir tarih listeniz olduğunu ve her bir tarih değerini gün, ay ve yıl için üç ayrı sütuna bölmek istediğinizi varsayalım. Tarihleri bölme sonucunu elde etmenize yardımcı olacak üç yöntem aşağıda verilmiştir.

Tarih hücrelerini formüllerle gün, ay ve yıl olarak böl

Bir tarihi Excel’de ayrı gün, ay ve yıl olarak bölmek için DATE, MONTH ve YEAR fonksiyonlarına dayalı üç formül uygulayabilirsiniz.

Genel formüller

=DATE(A1)

=MONTH(A1)

=YEAR(A1)

Argüman

Ayrı gün, ay ve yıl için üç sütun oluşturun.

Gün sütunundaki ilk hücreyi seçin, aşağıdaki formülü girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer tarih hücrelerinin günlerini alın.

=DAY(B5)

Aynı işlemi1. adımda olduğu gibi Ay ve Yıl sütunlarında aşağıdaki formülleri uygulayarak tarih hücrelerinden ayrı ay ve yılı alın.

Bir tarihin ayını alın

=MONTH(B5)

Bir tarihin yılını alın

=YEAR(B5)

Tarih hücrelerini Veriyi Böl özelliğiyle gün, ay ve yıl olarak böl

Yukarıdaki adımları izleyerek Veriyi Böl özelliğini uygulayarak tarih hücrelerini ayrı gün, ay ve yıl olarak bölebilirsiniz.

Not: 3 Adımda2 iletişim kutusunda yalnızca Diğer kutusunu işaretleyin ve metin kutusuna / sembolü girin.

Tarih hücrelerini harika bir araçla gün, ay ve yıl olarak böl

Aşağıdaki demo, Kutools for Excel’in Hücreleri Böl özelliği ile tarihi ayrı ay, gün ve yıl olarak nasıl böleceğinizi göstermektedir.

Bu özellik, yalnızca birkaç tıklama ile tarihleri toplu olarak bölmenize yardımcı olur.

Bu özellik hakkında daha fazla bilgi için tıklayın.

Örnek #2 Hücredeki tarih ve saati böl

B5:B9 aralığında bir tarih saat listeniz olduğunu ve tarihleri ve saatleri ayrı sütunlara bölmek istediğinizi varsayalım, bu bölümde bunu yapmanıza yardımcı olacak farklı yöntemler gösterilecektir.

Hücredeki tarih ve saati formül ile böl

Aşağıdaki formülleri uygulayarak Excel’de tarih ve saati farklı sütunlara bölebilirsiniz.

Tarih ve saatleri yerleştirmek için iki sütun hazırlayın.

Tarih sütunu hücrelerini Tarih biçimine, saat sütunu hücrelerini Saat biçimine ayarlamanız gerekir.

Tarih sütunundaki ilk hücreyi seçin, aşağıdaki formülü girin ve Enter tuşuna basın. Sonuç hücresini seçin ve Otomatik Doldurma Tutamacı’nı aşağı sürükleyerek diğer tarihleri alın.

=INT(B5)

Saat sütununda aşağıdaki formülü uygulayarak B5:B9’daki saatleri alın.

=B5-D5

Hücredeki tarih ve saati Flash Fill (2013 ve sonrası sürümler) ile böl

Excel2013 ve sonraki sürümleri kullanıyorsanız, yerleşik Flash Fill özelliğini uygulayarak tarih ve saati farklı sütunlara bölebilirsiniz.

Tarih ve Saat sütunlarını oluşturun ve çıkış olarak istediğiniz birkaç örnek girin. Tarih sütununda, B5’in tarihini C5’e, B6’nın tarihini C6’ya girin. Saat sütununda, B5’in saatini D5’e, B6’nın saatini D6’ya girin. Ekran görüntüsüne bakın:

Doldurmak istediğiniz Tarih sütununu (örnekler dahil) seçin, Veri > Flash Fill’e tıklayın.

Doldurmak istediğiniz Saat sütununu (örnekler dahil) seçin ve ardından Flash Fill özelliğini de etkinleştirin. Ardından, B5:B9’daki tarih ve saatler aşağıdaki demoda gösterildiği gibi ayrı sütunlara bölünür.

Artık, Excel’de farklı durumlarda farklı yöntemlerle hücreleri nasıl böleceğinizi öğrendiniz. Hangi yöntemi seçeceğinize karar verirken, kendi senaryonuza en uygun olanı seçebileceksiniz.