Metin dizisinden ilk veya son iki ya da n kelimeyi nasıl çıkarabilirim?
Excel’de büyük metin veri kümeleriyle çalışırken, genellikle her hücredeki belirli kelimeleri ayıklamanız gerekir: örneğin ilk birkaç kelime, son kelimeler ya da belirli bir konumdaki kelimeler. Yaygın kullanım senaryoları arasında, boşluklarla ayrılmış çok kelimeli metinler içeren hücrelerden isimlerin, adreslerin, anahtar kelimelerin veya benzersiz tanımlayıcıların çıkarılması yer alır. Örneğin, bir hücrede bir cümle veya ürün kodu bulunuyor olabilir ve yalnızca ilk üç kelimeyi ya da son iki kelimeyi aşağı akış işlemleriniz için çekmek isteyebilirsiniz. Excel’in bu işlem için doğrudan yerleşik bir işlevi olmasa da, farklı veri boyutlarına ve ihtiyaçlara uygun olarak esnek kelime çıkarımı sağlayan birçok yaklaşım mevcuttur. Bu makalede, Excel’de metin dizisinden ilk, son veya n’inci kelime(leri) verimli şekilde ayıklamak için formüller, pratik araçlar ve ek otomasyon teknikleri ele alınacaktır.
Formüllerle metin dizisinden ilk iki veya n kelimeyi çıkarın
Formülle metin dizisinden son iki veya n kelimeyi çıkarın
Kullanışlı bir özellik ile metin dizisinden ilk veya n’inci kelimeyi çıkarın
VBA makrosu ile kelimeleri çıkarın (ilk, son veya n’inci kelime)
Power Query kullanarak kelimeleri çıkarın (böl ve yeniden birleştir)
Formüllerle metin dizisinden ilk iki veya n kelimeyi çıkarın
Bir hücre değerinden ilk birkaç kelimeyi çıkarmak için aşağıdaki formülleri kullanabilirsiniz. Bu özellikle tutarlı bir kelime ayırıcınız (örneğin boşluk) olduğunda ve örneğin bir hücreden ilk üç kelimeyi almak istediğinizde faydalıdır. Bu formüller uygulanması kolaydır, orta büyüklükteki veri kümelerinde iyi çalışır ve herhangi bir sayıda baştaki kelimeyi çıkarmak üzere uyarlanabilir.
Aşağıdaki formüllerden birini boş bir hücreye (örneğin C2) girin ve ardından diğer ilgili satırlara uygulamak için doldurma kulmasını aşağı doğru sürükleyin. Aşağıdaki ekran görüntüsünde gösterildiği gibi, her metin dizisinden istediğiniz sayıda baştaki kelimeyi göreceksiniz.
Açıklama ve ipuçları:
1. Yukarıdaki örnek formülde, A2, orijinal metni içeren hücreye işaret eder ve 3, çıkarılacak kelime sayısıdır. Verilerinize göre bu başvuruları gerektiği gibi güncelleyin.
2. Çıkarmak istediğiniz kelime sayısı satıra göre değişiyorsa ve bu sayı sütun B’de belirtilmişse, aşağıdaki esnek formülü kullanabilirsiniz:
Bu formül, çıkarılacak kelime sayısını dinamik olarak belirtmenizi sağlar; bunun için bir hücreye (örneğin B2) başvurun. B2 hücresinin geçerli bir tam sayı içerdiğinden emin olun.
3. Metniniz başka ayırıcılarla (örneğin virgül veya noktalı virgül) ayrılmışsa, formüldeki boşluk karakterini ()" ") kendi ayırıcınızla değiştirerek uyarlayabilirsiniz.
4. Başında veya sonunda çift boşluklar ya da ardışık çoklu boşluklar olmamasına dikkat edin; bunlar sonucu etkileyebilir. KIRP işlevi, fazladan boşlukları kaldırmanıza yardımcı olur.
5. Bir hücredeki toplam kelime sayısı, çıkarmaya çalıştığınız sayıdan azsa, formül tüm metni hatasız şekilde döndürür.
Formülle metin dizisinden son iki veya n kelimeyi çıkarın
Metin dizisinden son kelimeleri (örneğin son iki veya son n kelime) çıkarmak için aşağıdaki formül işinize yarayacaktır. Bu yöntem, soyad, dosya uzantıları veya metin hücresinin sonundaki tanımlayıcılar gibi sürekli olarak sondan çıkarılması gereken veri kümeleri için oldukça etkilidir.
Sonucu görmek istediğiniz boş bir hücreye aşağıdaki formülü kopyalayıp yapıştırın. Formülü girdikten sonra, diğer satırlara uygulamak için doldurma kulmacasını aşağı doğru sürükleyin.
- A2: metni içeren kaynak hücre.
- 3: geri getirmek istediğiniz son kelime sayısını belirtir. Son 5 kelimeyi almak için formüldeki 3 değerini 5 ile değiştirin.
- Hücredeki toplam kelime sayısı belirttiğiniz sayıdan azsa, formül o hücredeki tüm mevcut kelimeleri döndürür.
- Verileriniz virgül, sekme gibi başka bir ayırıcıyla ayrılmışsa, formülü buna uygun şekilde ayarlayın.
- Verilerinizde karmaşık veya tutarsız boşluklar varsa bu, yanlış sonuçlara yol açabilir; formülü uygulamadan önce metninizi mümkünse temizleyin.
Kullanışlı bir özellik ile metin dizisinden ilk veya n’inci kelimeyi çıkarın
Yalnızca metin dizisinden ilk veya belirli (n’inci) kelimeyi çıkarmak istiyorsanız ve karmaşık formülleri ezberlemek ya da düzenlemek zorunda kalmadan pratik bir çözüm arıyorsanız, Kutools for Excel kullanabilirsiniz. Bir hücreden N'inci kelimeyi çıkar özelliği, bir hücreden kolayca istediğiniz kelime konumunu seçmenizi sağlar; bu da hem ara sıra yapılan hem de tekrarlanan görevler için idealdir. Hata riskini en aza indiren, manuel formül girişi gerektirmeyen hızlı ve menü tabanlı bir çözüm arıyorsanız, bu yöntemi öneririz.
Kutools for Excel yüklendikten sonra Kutools for Excel, aşağıdaki adımları izleyin:
1. Çıkarılan sonucun görüntüleneceği bir hücre seçin. Ardından Kutools > Formül Yardımcısı > Formül Yardımcısı yolunu izleyin (ekran görüntüsünde gösterildiği gibi):
2.Formül Yardımcısıiletişim kutusunda şu adımları uygulayın:
- Metin öğesini Formül Türü açılır liste kutusundan seçin.
- Formül liste kutusundan Bir hücreden N'inci kelimeyi çıkar seçeneğini seçin.
- Kaynak hücrenizi Hücre metin kutusuna girin ve istenen kelimenin konumunu (örneğin, ikinci kelime için 2) N. Kelime metin kutusuna yazın.
3.Tamam sonucunu döndürmek için, ardından aynı mantığı listenizin geri kalanına uygulamak üzere doldurma tutamacını aşağı doğru sürükleyin. Şekle bakın:
Bu yöntem, çıkarım gereksinimleri sık sık değiştiğinde veya her seferinde özel formüller oluşturmak yerine rehberli bir arayüz tercih ettiğinizde uygundur.
VBA makrosu ile kelimeleri çıkarın (ilk, son veya n’inci kelime)
Metinden dinamik olarak ilk, son veya belirli bir n. kelimeyi çıkarma gibi daha gelişmiş kullanım senaryolarında ya da büyük veya sıklıkla güncellenen veri kümeleriyle çalışırken, yalnızca formüllerden daha fazla esneklik ve otomasyon sağlayan bir VBA makrosu tercih edilmelidir. VBA, birçok hücreyi hızlıca işleyebilen ve ihtiyaçlarınıza göre hangi kelimenin veya kelime grubunun çıkarılacağını özelleştirebileceğiniz yeniden kullanılabilir bir araç oluşturmanıza olanak tanır. Özellikle metinleri sürekli aynı şekilde düzenlemeniz gerekiyorsa veya karmaşık iç içe geçmiş formüllerden kaçınmak istiyorsanız bu yöntem oldukça faydalıdır.
Tipik senaryolar: İçe aktarılan metinler üzerinde toplu çıkarım yapmak, toplu temizlik gerçekleştirmek veya standartlaştırılmış raporlar hazırlamak.
1. VBA düzenleyicisini açmak için Geliştirici > Visual Basic öğesine tıklayın. Microsoft Visual Basic for Applications penceresinde Ekle > Modül seçeneğini belirleyin ve aşağıdaki kodu yeni modüle yapıştırın:
Sub ExtractWord()
Dim Rng As Range
Dim Cell As Range
Dim WordPos As Integer
Dim Words() As String
Dim ExtractedWord As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set Rng = Application.Selection
Set Rng = Application.InputBox("Select range to extract word from", xTitleId, Rng.Address, Type:=8)
WordPos = Application.InputBox("Enter which word number to extract (1=first,2=second, etc., or -1 for last)", xTitleId, 1, Type:=1)
Application.ScreenUpdating = False
For Each Cell In Rng
Words = Split(Application.Trim(Cell.Value), " ")
If WordPos = -1 Then
If UBound(Words) >= 0 Then
ExtractedWord = Words(UBound(Words))
Else
ExtractedWord = ""
End If
ElseIf WordPos >= 1 And WordPos <= UBound(Words) + 1 Then
ExtractedWord = Words(WordPos - 1)
Else
ExtractedWord = ""
End If
Cell.Offset(0, 1).Value = ExtractedWord
Next Cell
Application.ScreenUpdating = True
MsgBox "Extraction complete! Results placed in the column to the right.", vbInformation, xTitleId
End Sub2. Makroyu çalıştırmak için 
**Çalıştır** düğmesine tıklayın ya da F5 tuşuna basın. Makro, Veri Aralığı'nızı seçmenizi isteyecek, çıkarılacak kelimenin konumunu soracak (örneğin ilk kelime için 1, son kelime için -1) ve ardından çıkarılan kelimeyi sağdaki bitişik sütuna yerleştirecektir. Ayırıcınızın boşluk olduğundan emin olun; diğer ayırıcılar için gerekirse Split fonksiyonunu uygun şekilde uyarlayın.
Bu VBA yaklaşımı, tekrarlayan toplu çıkarımları çok daha hızlı hale getirir ve formül tabanlı çözümlerin karmaşıklaşmaya başladığı durumlarda idealdir. Not: Makroları çalıştırmadan önce mutlaka çalışma kitabınızı kaydedin ve güvenlik ayarlarınızda makroların etkinleştirildiğinden emin olun.
Power Query kullanarak kelimeleri çıkarın (böl ve yeniden birleştir)
Büyük tabloları yönetiyorsanız veya yinelenen içe/dışa aktarma görevleriniz varsa, Power Query metin dizilerinden kelimeleri çıkarmak ve işlemek için güçlü bir alternatif sunar. Power Query, Veri sekmesinde Al ve Dönüştür Veri olarak bulunur; buradan metni ayırıcılara (boşluklar, virgüller vb.) göre görsel olarak bölebilir, gerekli kelime(leri) çıkarabilir ve ardından verileri yeniden birleştirebilir ya da Excel’e geri yükleyebilirsiniz. Bu işlem, özellikle yüzlerce hatta binlerce satırlık verilerle toplu veri temizliği yaparken yüksek düzeyde tekrarlanabilirdir.
Temel avantajlar: Yinelemeli kullanımlarda tutarlılık sağlar; görsel arayüz ve kelime konumu gereksinimleri değiştiğinde kolayca ayarlama imkânı sunar.
İşlem adımları:
- Veri aralığınızı seçin ve verilerinizi Power Query’ye yüklemek için Veri > Tablodan/Aralıktan seçeneğini belirleyin. Gerekirse tablonuzu onaylayın veya oluşturun.
- Power Query Düzenleyici’de metninizin bulunduğu sütunu seçin.
- Tıklayın Sütunu Böl>Ayırıcıya Göre
- Seçin Boşlukveya özel ayırıcınızı girin, ardından Bölme Konumu–Ayırıcının Her Oluşumundaseçeneğini belirleyip Tamam’a tıklayın.
- Metindeki her kelime artık kendi sütununda görünecek (örneğin, Sütun1, Sütun2 vb.). İlk iki kelimeyi kaldırmak istiyorsanız, bölünmüş kelimeleri yeniden birleştirin. İlgili sütunları seçin, üstbilgiye sağ tıklayın ve bağlam menüsünden Sütunları Birleştir seçeneğini belirleyin.
- Tercih ettiğiniz ayırıcıyı (örneğin boşluk veya virgül) seçin ve Tamamdüğmesine tıklayın.
- Son olarak, sonuçlarınızı Yeni çalışma sayfası içine Geçerli Çalışma Kitabı’na geri yüklemek için Kapat ve Yükle düğmesine tıklayın.
Power Query ayrıca süreci yeniden kullanmayı kolaylaştırır; çünkü altta yatan tablonuz değiştiğinde sorguyu yenileyebilirsiniz. Böylece formüllerin veya makroların manuel olarak tekrar uygulanmasına gerek kalmadan tutarlı sonuçlar elde edersiniz. Power Query’nin ürettiği sütun sayısını gözden geçirmeyi ve daha kısa metin dizilerinde eksik kelimeleri hesaba katmayı unutmayın.
İlgili diğer makaleler:
- Bir Hücreden Birden Fazla Satır Çıkarın
- Metin girerken Alt + Enter tuşlarına basarak satır sonlarıyla ayrılmış metin dizeleriniz varsa ve aşağıdaki ekran görüntüsünde gösterildiği gibi bu satırları ayrı hücrelere ayırmak istiyorsanız, Excel’de bu işlemi bir formülle nasıl gerçekleştirebilirsiniz?
- Excel'de Metin Dizisinden Alt Dize Çıkarın
- Metin dizelerinden alt dizeler çıkarmak oldukça yaygın bir görevdir. Excel’de bunu doğrudan gerçekleştiren bir işlev olmasa da, SOLDAN, SAĞDAN, PARÇAAL ve BUL işlevlerini kullanarak ihtiyacınız doğrultusunda çeşitli alt dizeleri kolayca çıkarabilirsiniz.
- Metni Çıkart Metin Dizisi İçindeki Parantezler Arasında
- Metin dizisi içinde parantezlerle çevrelenmiş bir metin bölümü varsa ve aşağıdaki ekran görüntüsünde gösterildiği gibi parantezler arasındaki tüm metni çıkarmak istiyorsanız, bu görevi Excel’de nasıl hızlı ve kolayca halledebilirsiniz?
- Excel'de Belirli Bir Karakterle Başlayan Kelimeyi Çıkarın
- Örneğin, aşağıdaki ekran görüntüsünde gösterildiği gibi her hücrede “=” karakteriyle başlayan ifadeleri çıkarmak isteyebilirsiniz. Excel’de bu görevi hızlı ve kolayca nasıl halledebilirsiniz?
- Metni Çıkart Belirli Bir Karakterin Son Örneğinden Sonra
- Hücre verilerinde tire, virgül ve boşluklar gibi birden fazla ayırıcı içeren karmaşık metin dizeleriniz varsa ve bu örnekte olduğu gibi tirenin son oluşumunun konumunu bulup ondan sonraki alt dizgeyi çıkarmak istiyorsanız:
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— birkaç dakikada hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— ayrı ayrı eklenti satın almaya göre tasarruf sağlar