Excel'de bir karakterin ilk, son veya tekrar geçtiği konumu nasıl bulursunuz?
Birçok veri işleme görevinde, tire (“-”) gibi özel karakterler içeren metin dizeleriyle karşılaşabilirsiniz ve bu karakterlerin ilk, son veya belirli (n. sıradaki) geçişlerinin konumunu belirlemeniz gerekebilir. Örneğin, sınırlayıcılara göre bilgi çıkarma, parça kodlarını analiz etme veya veri bileşenlerini ayırma gibi işlemler genellikle her dizede ilgili karakterin nerede geçtiğini tespit etmeyi gerektirir. Ancak Excel, bir hücredeki bir karakterin ilk, son veya n. geçiş konumunu doğrudan döndüren yerleşik bir işlev sunmaz. Bu makalede, söz konusu geçişleri doğru şekilde belirlemek ve çeşitli veri ayrıştırma ile hazırlama ihtiyaçlarınızı karşılamak için kullanabileceğiniz pratik teknikler ve adım adım çözümler yer almaktadır.
Formüllerle bir karakterin son geçtiği yeri bulun
Kullanıcı Tanımlı İşlev (UDF) ile bir karakterin son geçtiği yeri bulun
Formülle bir karakterin ilk veya Tekrar geçtiği yeri bulun
Kolay bir özellik ile belirli bir karakterin ilk veya Tekrar geçtiği yeri bulun
Formüllerle bir karakterin son geçtiği yeri bulun
Bir metin dizesinde belirli bir karakterin (örneğin “-”) en son geçtiği konumu bulmak istiyorsanız, Excel’in standart işlevleri bunu doğrudan desteklemez. Ancak birkaç formülü akıllıca birleştirerek bu sorunu etkili şekilde çözebilirsiniz. Ürün kodları, dosya yolları veya tutarlı bir ayırıcı deseni içeren diğer veriler üzerinde çalışırken özellikle kullanışlı olan iki formül yaklaşımı aşağıda yer almaktadır. Büyük veri kümelerinde karmaşık formüllerin hesaplama yükünü artırabileceğini unutmayın.
1. Boş bir hücreye (örneğin C2) aşağıdaki formüllerden birini girin veya kopyalayın:
=SEARCH("^^",SUBSTITUTE(A2,"-","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))=LOOKUP(2,1/(MID(A2,ROW(INDIRECT("1:"&,LEN(A2))),1)="-"),ROW(INDIRECT("1:"&,LEN(A2))))Her iki formül de, başvurulan hücredeki (A2) “-” karakterinin son geçtiği yerin konumunu (sayı olarak) döndürmek üzere tasarlanmıştır. İlk formül, son “-” işaretini benzersiz bir sembolle değiştirip ardından bu sembolü arayarak konumu belirlemek için SEARCH ve SUBSTITUTE işlevlerini kullanır. İkincisi ise son “-” işaretini bulmak için LOOKUP işlevini MID ve ROW işlevleriyle birlikte kullanır. Aşinalığınız veya veri hacminize göre bu yöntemlerden birini tercih edebilirsiniz. LOOKUP yaklaşımı, dizi destekli modern Excel sürümlerine uyarlaması daha kolay olabilir.
2. Formülü girdikten sonra Enter tuşuna basın. Bu formülü ek satırlara uygulamak için, gerektiğinde tüm aralığı kapsayacak şekilde doldurma tutamacını aşağı doğru sürükleyin. Böylece her hücre, ilgili metin dizesi için son geçiş konumunu anında alacaktır; sonuç aşağıdaki görselde gösterilmiştir. Formülü başka bir alana kopyalarken başvurulan hücrelerin doğru olduğundan emin olun.
Not: Bu örneklerde, A2 verilerinizi içeren hücreyi ifade eder ve “-” hedef karakterdir. “-” işaretini aramak istediğiniz herhangi bir karakterle değiştirebilirsiniz. Hücre boşsa ya da metinde bu karakter geçmiyorsa, formül hata döndürebilir. Tekrarlayan sınırlayıcılar veya özel semboller içeren metin girişlerinde, sonuçlarınızı etkileyebilecek gizli veya yazdırılamayan karakterleri dikkatlice kontrol edin.
Kullanıcı Tanımlı İşlev (UDF) ile bir karakterin son geçtiği yeri bulun
Başka bir esnek yöntem, VBA modülünde Kullanıcı Tanımlı Bir İşlev (UDF) kullanmaktır. Bu yöntem, özellikle çeşitli analizlerde herhangi bir karakterin son geçtiği yeri sıklıkla belirlemeniz gerekiyorsa size daha fazla kontrol ve kolay yeniden kullanım imkânı sunar. Yerleşik Excel işlevlerine benzer şekilde çalışan, kısa ve öz bir formül arayüzü ()=lastpositionofchar(cell, character)) arıyorsanız bu yaklaşım tam size göre! Ancak unutmayın: VBA çözümlerinin çalışması için makroların etkinleştirilmesi gerekir ve UDF’nizi korumak amacıyla dosyanızı makro etkin bir dosya (*.xlsm) olarak kaydetmelisiniz.
1. Bu işlevi eklemek istediğiniz çalışma sayfasını açın.
2. ALT + F11 tuşlarına aynı anda basarak Microsoft Visual Basic for Applications düzenleyicisini açın.
3. VBA düzenleyicide yeni bir modül oluşturmak için Ekle > Modül seçeneğine tıklayın. Ardından aşağıdaki kodu modül penceresine kopyalayıp yapıştırın.
VBA kodu: bir karakterin son geçtiği yeri bulun
Function LastpositionOfChar(strVal As String, strChar As String) As Long
LastpositionOfChar = InStrRev(strVal, strChar)
End Function
Sorun Giderme: İşlev bir sonuç döndürmüyorsa, makroların etkinleştirildiğinden ve formülde doğru hücre referansı ile karakteri girdiğinizden emin olun. Farklı bir karakterin son geçtiği yeri bulmanız gerekiyorsa, bağımsız değişkeni buna göre güncelleyin.
4. Kod penceresine dönün ve aşağıdaki formülü boş bir hücreye (örneğin B2 hücresine) girin: =lastpositionofchar(A2,"-"). Çalışma sayfanıza döndükten sonra, A2 hücresini ve “-” işaretini hedef karakterinizle değiştirin.
5. Formülü gerektiği kadar ek hücrelere uygulamak için doldurma tutamacını aşağı doğru sürükleyin. Böylece her metin dizesinde belirttiğiniz karakterin son konumu aşağıdaki gibi döndürülecektir.
Not: İlk bağımsız değişken ()A2) metin dizenizin bulunduğu kaynak hücredir; ikincisi ise (“-”) hedef karakterdir. Bu parametreleri ihtiyacınıza göre özelleştirebilirsiniz. Eşleşme bulunamazsa, işlev 0 değerini döndürür. VBA kodunu çalıştırmadan veya düzenlerken mutlaka işinizi kaydedin. Hata alırsanız, yanlış yerleştirilmiş tırnak işaretlerini veya hatalı hücre başvurularını kontrol edin.
Formülle bir karakterin ilk veya Tekrar geçtiği yeri bulun
Göreviniz, bir metin dizesindeki bir karakterin yalnızca sonuncusunu değil, aynı zamanda ilk, ikinci veya tekrar geçtiği yeri bulmaksa, farklı bir formül yapısı kullanabilirsiniz. Bu genellikle hiyerarşik kodları ayrıştırırken, yapılandırılmış kimliklerin parçalarını çıkarırken ya da veri girişlerinde tekrarlanan sembolleri belirlerken gereklidir.
1. Belirtilen bir karakterin n. konumunu almak için boş bir hücreye (örneğin C2) aşağıdaki formülü girin veya yapıştırın:
=FIND(CHAR(160),SUBSTITUTE(A2,"-",CHAR(160),2))Bu formülde, A2 arama yapılan hücreyi, “-” bulunacak karakteri ve 2 ise aradığınız karakterin kaçıncı geçişini temsil eder (örneğin, ikinci geçiş için 2). Veri gereksinimlerinize göre geçiş numarasını istediğiniz gibi ayarlayabilirsiniz.
2.Enter tuşuna basın. Formülü daha fazla satıra uygulamak için doldurma tutamacını aşağı doğru gerekli aralığa kadar sürükleyin. Bu işlem, örneğin her metin dizesindeki “-” işaretinin ikinci oluşumunun konumunu verecektir. Belirtilen tekrar sayısı, dizedeki karakterin toplam sayısından fazlaysa formül bir hata döndürür. Böyle bir durumda metin verilerinizi veya tekrar parametrenizi kontrol edin.
Not: Formüldeki sayı 2, aranacak oluşumun sırasını belirtir. İstenen karakterin ilk, üçüncü, dördüncü veya başka bir tekrarının konumunu bulmak için bu sayıyı güncelleyin. Karakter, belirtilen sayıda bulunamazsa #DEĞER! hatası verilir; temiz sonuçlar almak için bunu IFERROR() ile yakalayabilirsiniz.
Belirli bir karakterin ilk veya Tekrar oluşumunu kolay bir özellik ile bulun
Karmaşık formüller veya VBA kodu yazmakta zorlanan ya da doğrudan ve etkileşimli bir çözüm arayan kullanıcılar için Kutools for Excel, Bir dizgede belirtilen karakterin N. tekrarının konumunu bul adlı son derece kullanışlı bir araç sunar. Bu özellik, bir hücredeki belirli bir karakterin ilk, ikinci, üçüncü veya istediğiniz herhangi bir tekrarının konumunu sadece birkaç tıklamayla belirleyerek süreci büyük ölçüde kolaylaştırır; böylece tekrarlayan veri temizleme işlemlerini hızlandırır ve büyük veri kümeleriyle çalışırken size önemli bir avantaj sağlar.
Kutools for Excel yüklendikten sonra Kutools for Excel, hızlı sonuçlar almak için aşağıdaki adımları izleyin:
Bir dizi metin dizesinde tire (“-”) karakterinin ikinci oluşumunun konumunu elde etmek istediğinizi varsayalım:
1. Sonucun görünmesini istediğiniz hücreye tıklayın.
2.Kutools>Formül Yardımcısı>Formül Yardımcısısekmesine Şerit şeridinde aşağıda gösterildiği gibi tıklayın:
3.Formül Yardımcısıiletişim kutusunda:
- Arama seçeneğini, Formül Türü menüsünden seçin.
- Bir formül seçin listesinden Bir dizgede belirtilen karakterin N. tekrarının konumunu bul seçeneğini seçin.
- Argüman Girişi alanında, metninizin bulunduğu hücreyi seçin, aranacak karakteri girin ve geçiş numarasını belirtin.
4.Tamam tuşuna tıklayın. Formül sonucu döndükten sonra, diğer satırlara uygulamak için doldurma tutamacını aşağı doğru sürükleyebilirsiniz. Bu yöntem, manuel girişten kaynaklanan formül hataları riskini ortadan kaldırır.
Kutools for Excel'i şimdi indirin ve ücretsiz deneyin!
Bu seçenek, özellikle teknik olmayan kullanıcılar veya farklı çalışma sayfalarında süreci sık sık tekrarlaması gereken kişiler için uygundur; manuel hataları en aza indirir ve karmaşık formül oluşturmada zaman kazandırır.
Yeni Excel sürümleri için alternatif formül (dinamik dizilerle)
Dinamik dizileri destekleyen yeni Excel sürümlerinde (Excel 365, Excel 2021 ve sonrası), SEQUENCE ve FILTER gibi işlevler sayesinde bir karakterin tüm konumlarını tek seferde döndürebilirsiniz. Bu, tekrarları kolayca belirlemenizi veya tüm oluşumları ileri işleme için ayrı bir aralıkta listelemenizi sağlar.
1. A2 hücresindeki “-” karakterinin tüm konumlarını almak için boş bir hücreye (örneğin B2) aşağıdaki formülü girin:
=FILTER(SEQUENCE(LEN(A2)), MID(A2,SEQUENCE(LEN(A2)),1)="-")Enter tuşuna bastıktan sonra, formül “-” işaretinin her oluşumunun konumunu bitişik hücrelere otomatik olarak döker. Yalnızca tekrar döndürmek istiyorsanız şu formülü kullanabilirsiniz:
=INDEX(FILTER(SEQUENCE(LEN(A2)), MID(A2,SEQUENCE(LEN(A2)),1)="-"),2)2 2 yerine istediğiniz tekrar sayısını yazın. Bu yöntem, değişken uzunluklu diziler veya listeler için özellikle etkilidir; ancak bu işlevleri destekleyen bir Excel sürümü gerektirir.
İpucu: #HESAP! veya benzeri bir hata alırsanız, Excel sürümünüzün dinamik dizileri desteklediğini ve başvurulan hücrelerin metin verisi içerdiğini kontrol edin.
Bir karakterin Tekrar konumunu bulmak için VBA kodu
Sadece son değil, aynı zamanda herhangi bir Tekrar karakter konumunu bulmanız gerekiyorsa VBA da yardımcı olabilir. Aşağıdaki makro birçok farklı ayrıştırma ihtiyacına göre özelleştirilebilir ve birden fazla çalışma kitabında tekrar tekrar kullanıldığında zamandan tasarruf sağlayabilir.
1. Geliştirici sekmesine gidin > Visual Basic, ardından Ekle > Modül seçeneğini tıklayın. Aşağıdaki kodu modül penceresine yapıştırın:
Function NthPositionOfChar(cell As Range, ch As String, nth As Integer) As Long
Dim s As String
Dim i As Long, count As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
s = cell.Value
count = 0
For i = 1 To Len(s)
If Mid(s, i, 1) = ch Then
count = count + 1
If count = nth Then
NthPositionOfChar = i
Exit Function
End If
End If
Next i
NthPositionOfChar = 0
End Function2. Düzenleyiciyi kapatın. A2 hücresindeki “-” işaretinin ikinci oluşumunun konumunu bulmak için çalışma sayfanıza =NthPositionOfChar(A2,"-",2) formülünü girin (gerekirse bağımsız değişkenleri güncelleyin). Karakter tekrar bulunamazsa, işlevin 0 döndüreceğini unutmayın.
Bu kod, verilerden esnek sınırlayıcı bilgileri çıkarmak veya tekrarlayan ayrıştırma görevlerini basitleştirmek için idealdir. VBA özel işlevleri ayrıca büyük veri kümeleriyle çalışmak veya büyük/küçük harfe duyarlı karakter aramak gibi daha özel gereksinimler için de ayarlanabilir.
Metin dizeleri ve karakter oluşum konumlarıyla çalışırken, formül sonuçlarını etkileyebilecek gizli veya yazdırılamayan karakterleri (boşluklar, Taşıma İşareti veya özel Unicode sembolleri gibi) her zaman kontrol edin. Ayrıca verileriniz çeşitli sınırlayıcılar kullanıyorsa, formüller veya makrolar uygulamadan önce tutarlı olduğundan emin olun. Hata alırsanız, giriş parametrelerinizi, formül başvurularınızı ve kullandığınız işlevle uyumlu veri türünüzü (metin/sayı) tekrar kontrol edin.
Formüllerin, VBA’nın ve Kutools özelliklerinin birlikte kullanılması, hem rutin hem de gelişmiş Excel metin işleme görevleri için kapsamlı bir esneklik sağlar. En iyi doğruluk ve verimlilik için teknik rahatlığınızı, Excel sürümünüzü ve veri karmaşıklığınızı göz önünde bulundurarak uygun yöntemi seçin.
İlgili diğer makaleler:
- Excel'de İlk / Son Kelime Dışındaki Tümünü Çıkarın
- Bir hücredeki ilk veya son kelime dışında tüm kelimeleri kaldırmak, ihtiyacınız olan istenmeyen kelimelerden kurtulmanıza yardımcı olabilir. Elbette tek bir hücre için istediğiniz kelimeleri elle kopyalayıp başka bir hücreye yapıştırabilirsiniz. Ancak birden fazla hücrenin içeriğinden ilk veya son kelime dışındaki tüm kelimeleri çıkarmanız gerekiyorsa bu işlem oldukça sıkıcı hale gelir. Excel’de ilk veya son kelime dışındaki tüm kelimeleri hızlı ve kolayca nasıl kaldırabilirsiniz?
- Bir Hücrede Sağdan Sola Karakterleri Çıkarın
- Bu makale, Excel çalışma sayfasında bir hücredeki karakterleri sağdan itibaren ilk boşluğa kadar çekme veya çıkarma işlemini ve bu sayede aşağıdaki sonucu elde etmeyi ele almaktadır. Makalede yer alan kullanışlı formül, bu görevi hızlı ve kolayca halledebilir.
- İlk, Son X Karakteri veya Belirli Konumdaki Karakterleri Kaldırın
- Bu makale, Excel çalışma sayfasında bir hücredeki karakterleri sağdan itibaren ilk boşluğa kadar çekme veya çıkarma işlemini ve bunun sonucunda elde edilen çıktıyı ele alır. Makalede yer alan kullanışlı formül, bu görevi hızlı ve kolay bir şekilde halledebilir.
- İlk Küçük Harfin Konumunu Bulun
- Hem büyük hem küçük harf içeren metin dizelerinden oluşan bir listeniz varsa ve Excel çalışma sayfasında bu metinlerdeki ilk küçük harfin konumunu öğrenmek istiyorsanız ne yaparsınız? Karakterleri tek tek saymadan sonucu hızlıca nasıl elde edebilirsiniz?
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