Metin dizisinden ilk veya son iki veya n kelimeyi nasıl çıkarabilirsiniz?
Excel'de büyük metin veri kümeleriyle çalışırken, her hücreden belirli kelimeleri çıkarmak genellikle gerekir; örneğin, ilk birkaç kelime, son kelimeler veya belirli bir konumdaki kelimeler. Yaygın senaryolar arasında çok kelimeli metin girişleri içeren hücrelerden isimler, adresler, anahtar kelimeler veya benzersiz tanımlayıcılar çıkarmak bulunur; özellikle veriler boşluklarla ayrılmışsa. Örneğin, bir hücrede cümle veya ürün kodu varsa ve ilk üç kelimeyi çekmek isteyebilirsiniz veya sadece son iki kelimeyi aşağı yönlü işleme için almak istersiniz. Excel'in bunun için doğrudan yerleşik bir işlevi yoktur, ancak esnek kelime çıkarma için ulaşabileceğiniz birden fazla yaklaşım vardır, her biri farklı veri boyutlarına ve ihtiyaçlara uygun olarak tasarlanmıştır. Bu makale, size Excel'deki bir metin dizisinden ilk, son veya n'inci kelimeyi etkili bir şekilde çıkarmaya yardımcı olacak formüller, pratik araçlar ve ek otomasyon tekniklerini tanıtacaktır.
Formüllerle metin dizisinden ilk iki veya n kelimeyi çıkarma
Formülle metin dizisinden son iki veya n kelimeyi çıkarma
Pratik bir özellik yardımıyla metin dizisinden ilk veya N'inci kelimeyi çıkarma
VBA makrosu ile kelimeleri çıkarma (ilk, son veya n'inci kelime)
Power Query kullanarak kelimeleri çıkarma (böl ve yeniden birleştir)
Formüllerle metin dizisinden ilk iki veya n kelimeyi çıkarma
Bir hücre değerinden ilk birkaç kelimeyi çıkarmak için aşağıdaki formülleri kullanabilirsiniz. Bu, özellikle tutarlı bir kelime sınırlayıcınız varsa (örneğin boşluk) ve bir hücreden ilk üç kelimeyi almak gibi bir durumdaysa çok faydalıdır. Bu formüller uygulamak kolaydır, orta büyüklükteki veri setleri için iyi çalışır ve herhangi bir sayıda başlangıç kelimesini çıkarmak için uyarlanabilir.
Aşağıdaki formüllerden birini boş bir hücreye (örneğin C2) girin ve ardından formülü tüm ilgili satırlar için doldurma tutamağını aşağı sürükleyin. Aşağıdaki ekran görüntüsünde gösterildiği gibi, her metin dizisinden istenen sayıda başlangıç kelimesinin çıkarıldığını göreceksiniz.
Açıklama ve ipuçları:
1. Yukarıdaki örnek formülde, A2 orijinal metninizi içeren hücreye atıfta bulunur ve 3 istediğiniz kelime sayısını ifade eder. Verileriniz için bu referansları gerektiğinde değiştirin.
2. Çıkarmak istediğiniz kelime sayısı satır bazında değişiyorsa ve sütun B'de belirtilmişse, aşağıdaki esnek formülü kullanabilirsiniz:
Bu formül, bir hücreye (örneğin B2) referans vererek ayıklanacak kelime sayısını dinamik olarak belirtmenize olanak tanır. B2'nin geçerli bir tam sayı içerdiğinden emin olun.
3. Metniniz başka sınırlayıcılarla (virgül veya noktalı virgül gibi) ayrılıyorsa, formülü boşluk karakterini (" ") özel sınırlayıcınızla değiştirerek uyarlayabilirsiniz.
4. Başta/sonda çift boşluklar veya birden fazla ardışık boşluk olduğunda dikkatli olun, çünkü bunlar sonucu etkileyebilir. TRIM
uygulamak fazla boşlukları kaldırmaya yardımcı olur.
5. Bir hücredeki toplam kelime sayısı, ayıklamaya çalıştığınız sayıdan daha azsa, formül tam metni hata olmadan döndürür.
Formülle metin dizisinden son iki veya n kelimeyi çıkarma
Metin dizisinden son kelimeleri çıkarmak için (örneğin son iki veya son n kelime), aşağıdaki formül yardımcı olacaktır. Bu yöntem, soyadları, dosya uzantıları veya bir metin hücresinin sonundaki tanımlayıcılar gibi sondaki kelimeleri tutarlı bir şekilde çıkarmak istediğiniz veri setleri için 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, doldurma tutamağını aşağı sürükleyerek diğer satırlara uygulayın.
- A2: metin içeren kaynak hücre.
- 3: geri getirmek istediğiniz son kelime sayısı. Son 5 kelimeyi almak için formüldeki 3'ü 5'e değiştirin.
- Hücredeki toplam kelime sayısı talep ettiğinizden daha azsa, o hücredeki tüm mevcut kelimeleri döndürür.
- Verileriniz başka bir sınırlayıcı (virgül, sekme) ile ayrılıyorsa, formülü buna göre ayarlayın.
- Verilerinizdeki karmaşık veya tutarsız boşluklar yanlış sonuçlara yol açabilir; mümkün olduğunda formülü uygulamadan önce metninizi temizleyin.
Pratik bir özellik yardımıyla metin dizisinden ilk veya N'inci kelimeyi çıkarma
Eğer yalnızca ilk kelimeyi veya belirli bir (n'inci) kelimeyi bir metin dizisinden çıkarmak istiyorsanız ve karmaşık formülleri ezberlemek veya düzenlemek yerine bir çözüm tercih ediyorsanız, Kutools for Excel'i kullanabilirsiniz. Bir hücredeki n'inci kelimeyi çıkar özelliği, bir hücreden belirli bir kelime konumunu kolayca seçmenizi sağlar; bu da hem ara sıra kullanım için hem de tekrarlayan görevler için uygundur. Hızlı, menü tabanlı bir çözüm istiyorsanız ve manuel formül girişi olmadan hata riskini en aza indirmek istiyorsanız bu yaklaşım önerilir.
Kutools for Excel'i kurduktan sonra lütfen aşağıdaki adımları izleyin:
1. Çıkarılan sonucu görüntülemek istediğiniz bir hücreyi seçin. Ardından Kutools > Formül Yardımcısı > Formül Yardımcısı'na gidin, aşağıdaki ekran görüntüsünde gösterildiği gibi:
2. Formül Yardımcısı iletişim kutusunda şu adımları izleyin:
- Formül türü açılır listesinden Metin'i seçin.
- Formül listesi kutusundan Bir hücreden N'inci kelimeyi çıkar'ı seçin.
- Kaynak hücrenizi Hücre metin kutusuna belirtin ve İstenilen kelime konumunu (örneğin ikinci kelime için 2) The Nth metin kutusuna yazın.
3. Sonucu döndürmek için Tamam'a tıklayın, ardından aynı mantığı listenizin geri kalanına uygulamak için doldurma tutamağını aşağı sürükleyin. Görseli inceleyin:
Bu yöntem, çıkarma gereksinimleri sık sık değiştiğinde veya her seferinde özel formüller oluşturmak yerine rehberli bir arayüz istediğinde uygundur.
VBA makrosu ile kelimeleri çıkarma (ilk, son veya n'inci kelime)
Daha gelişmiş kullanım durumlarında—metinden ilk, son veya belirli bir n'inci kelimeyi dinamik olarak çıkarmak veya büyük veya sıkça güncellenen veri setleriyle uğraşırken—formüllerden daha fazla esneklik ve otomasyon sağlayan bir VBA makrosu idealdir. VBA, birçok hücreyi hızlı bir şekilde işleyebilen ve ihtiyaçlarınıza göre hangi kelime veya kelime grubunun çıkarılacağını özelleştirebileceğiniz yeniden kullanılabilir bir araç oluşturmaya olanak tanır. Özellikle metni sürekli aynı şekilde değiştirmeniz gerekiyorsa veya karmaşık iç içe formüllerden kaçınmak istiyorsanız çok yararlı olabilir.
Tipik senaryolar: Toplu metin içe aktarma, toplu temizleme veya standart raporlar hazırlama.
1. Geliştirici > Visual Basic'e tıklayarak VBA düzenleyicisini açın. Microsoft Visual Basic for Applications penceresinde, Ekle > Modül'ü seçin 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 Sub
2Makroyu çalıştırmak için Çalıştır düğmesine tıklayın veya F5tuşuna basın. Makro, veri aralığınızı seçmenizi, hangi kelime konumunun çıkarılacağını 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. Sınırlayıcınızın bir boşluk olduğundan emin olun veya
Split
fonksiyonunu gerektiğinde diğer sınırlayıcılar için uyarlayın.
Bu VBA yaklaşımı, tekrarlanan, toplu çıkarmaları çok daha hızlı hale getirir ve formül tabanlı çözümler karmaşık hale geldiğinde idealdir. Not: Makroları çalıştırmadan önce çalışma kitabınızı her zaman kaydedin ve güvenlik ayarlarınızda makroların etkin olduğundan emin olun.
Power Query kullanarak kelimeleri çıkarma (böl ve yeniden birleştir)
Büyük tabloları yönetirken veya tekrarlayan içe/dışa aktarma görevlerinde Power Query, metin dizelerinden kelimeleri çıkarmak ve işlemek için sağlam bir alternatif sunar. Power Query, Veri sekmesi altında Get & Transform Data olarak bulunur, görsel olarak metni sınırlayıcılarla (boşluklar, virgüller vb.) ayırmanızı, gerekli kelime(leri) çıkarmayı ve ardından veriyi yeniden birleştirip Excel'e geri yüklemenizi sağlar. Bu süreç, özellikle yüzlerce veya binlerce satırla uğraşırken, yüksek düzeyde tekrarlanabilir ve toplu veri temizliği için uygundur.
Ana avantajlar: Tekrarlanan kullanım için tutarlılık, görsel arayüz, kelime konumu gereksinimleri değişirse kolayca ayarlanabilir.
İşlem adımları:
- Veri aralığınızı seçin ve Veri > Tablodan/Aralıktan seçeneğini belirleyerek verilerinizi Power Query'ye yükleyin. İstenirse tablonuzu onaylayın/oluşturun.
- Power Query Düzenleyici'de metin içeren sütunu seçin.
- Sütun Böl > Sınırlayıcıya Göre'ye tıklayın
- Boşluk'u seçin veya özel sınırlayıcınızı girin, ardından Her sınırlayıcı oluşumunda – Ayır'a tıklayın. Ardından Tamam'a tıklayın.
- Metindeki her kelime artık kendi sütununda görünecektir (örneğin, Sütun1, Sütun2, vb.). İlk iki kelimeyi çıkarmak istiyorsanız, bölünmüş kelimeleri yeniden birleştirin. İlgili sütunları seçin, başlığa sağ tıklayın ve bağlam menüsünden Sütunları Birleştir'i seçin.
- Tercih ettiğiniz sınırlayıcıyı (örneğin boşluk veya virgül) seçin ve Tamam'a tıklayın.
- Son olarak, sonuçlarınızı geçerli çalışma kitabının yeni bir çalışma sayfasına geri yüklemek için Kapat ve Yükle'ye tıklayın.
Power Query ayrıca süreci yeniden kullanmayı da kolaylaştırır, çünkü temel tablonuz değişirse sorguyu yenileyebilirsiniz, bu da formüllerin veya makroların el ile yeniden uygulanmasını gerektirmeden tutarlı sonuçlar sağlar. Power Query'nin ürettiği sütun sayısını gözden geçirin ve daha kısa metin dizilerinde eksik kelimeleri hesaba katın.
Daha fazla ilgili makale:
- Bir Hücreden Birden Fazla Satır Çıkarma
- Satır sonları (metin girerken Alt + Enter tuşlarına basılarak oluşan) ile ayrılmış bir metin dizisi listesi varsa ve bu metin satırlarını birden fazla hücreye çıkarmak istiyorsanız aşağıdaki ekran görüntüsüne bakın. Bu sorunu Excel'de bir formülle nasıl çözebilirsiniz?
- Excel'de Metin Dizesinden Altdize Çıkarma
- Muhtemelen sizin için yaygın bir görevdir ki metin dizelerinden altdizeler çıkarmak zorundasınızdır, Excel'de bunu yapmak için doğrudan bir işlev bulunmamaktadır, ancak LEFT, RIGHT, MID ve SEARCH fonksiyonlarının yardımıyla ihtiyacınız olan çeşitli altdizeleri çıkarabilirsiniz.
- Metin Dizesinden Parantez İçindeki Metni Çıkarma
- Eğer metin dizisi içinde parantezlerle çevrili bir metin kısmı varsa ve bu parantezler arasındaki tüm metin dizelerini aşağıdaki ekran görüntüsünde gösterildiği gibi çıkarmak istiyorsanız. Bu görevi Excel'de hızlı ve kolay bir şekilde nasıl çözebilirsiniz?
- Excel'de Belirli Bir Karakterle Başlayan Kelimeyi Çıkarma
- Örneğin, her hücrede belirli bir “=” karakteriyle başlayan kelimeyi çıkarmak istiyorsanız, aşağıdaki ekran görüntüsünde gösterildiği gibi bu görevi Excel'de hızlı ve kolay bir şekilde nasıl çözebilirsiniz?
- Belirli Bir Karakterin Son Örnekten Sonra Metni Çıkarma
- Birkaç sınırlayıcı içeren (örneğin tire, virgül, boşluklar gibi) karmaşık metin dizeleri listesi varsa ve tirenin son oluşumunun konumunu bulup bundan sonraki altdizeyi çıkarmak istiyorsanız.
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin, daha önce hiç yaşamadığınız bir verimlilik deneyimini yaşayın. Kutools for Excel, üretkenliğinizi artıracak ve zamanı kaydetmenizi sağlayacak300’den fazla gelişmiş özellik sunar. En çok ihtiyaç duyduğunuz özelliği almak için buraya tıklayın...
Office Tab, Office’e sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır
- Word, Excel, PowerPoint’te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Aynı pencere içerisinde yeni sekmelerde birden fazla belge açın veya oluşturun, yeni pencerelerde açmak yerine.
- Verimliliğinizi %50 artırın, her gün yüzlerce fare tıklamasını sizin için azaltın!