Excel'de alfanümerik bir dizgeden metni nasıl çıkarabilirim?
Günlük Excel işlerinizde, hücrelerde harf ve rakamların bir arada bulunduğu—alfanümerik dizgeler olarak bilinen—verilerle sık sık karşılaşabilirsiniz. «Order2058», «User_15A» veya «ID1234B» gibi örnekler, dışa aktarılan raporlar, veritabanı çıktıları ya da standartlaştırılmış kod sistemlerinde oldukça yaygındır. İleri düzey analiz veya raporlama yaparken bazen bu dizgelerin yalnızca metin kısmını ayıklayıp sayısal öğeleri tamamen çıkarmak gerekebilir. Bu makalede, Excel’de alfanümerik dizgelerden sadece harf karakterlerini çıkarmak için çeşitli pratik yöntemler, yaygın kullanım senaryoları, dikkat edilmesi gereken noktalar ve sorun giderme ipuçları adım adım açıklanmaktadır.
➤ Alfanümerik bir dizgeden yalnızca Metni Çıkart'i Excel'de Tanımlı İşlev kullanarak alma
➤ Alfanümerik bir dizgeden yalnızca Metni Çıkart'i Kutools for Excel ile alma
➤ Alfanümerik bir dizgeden yalnızca Metni Çıkart'i bir Excel formülü kullanarak alma
Excel'de Kullanıcı Tanımlı Bir İşlev (UDF) kullanarak alfanümerik bir dizgeden yalnızca Metni Çıkart çıkartma
Genel bakış
- Avantajlar: Büyük veri kümelerinde dinamik çıkarım; karmaşık çalışma sayfası formüllerinden daha sade ve anlaşılır; herhangi bir çalışma kitabında tekrar kullanılabilir.
- Sınırlamalar: Makroların etkinleştirilmesi ve dosyanın
.xlsmolarak kaydedilmesi gerekir; özel işlevler yeniden hesaplanır ve çok büyük aralıklarda performansı olumsuz etkileyebilir.
VBA konusunda rahat hissediyorsanız, özel bir Kullanıcı Tanımlı İşlevi (UDF) oluşturarak herhangi bir alfanümerik dizgeden doğrudan harf karakterlerini çıkarabilirsiniz. Bu yöntem, yerleşik işlevler yetersiz kalır ya da aşırı karmaşıklaşır hâlde özellikle faydalıdır.
Uygulanacak adımlara hızlı bir genel bakış:
1. Çıkartım formülünü kullanmak istediğiniz herhangi bir hücreyi seçin ve ardından Alt + F11 tuşlarına basarak Microsoft Visual Basic for Applications düzenleyicisini açın.
2. VBA düzenleyicide yeni bir modül oluşturmak için Ekle > Modül seçeneğine tıklayın. Aşağıdaki kodu modüle yapıştırın.
VBA: Yalnızca harfleri çıkart (ASCII A–Z)
Function TextOnly(pWorkRng As Range) As String
'Updatebyextendoffice
Dim xValue As String
Dim OutValue As String
xValue = pWorkRng.Value
For xIndex = 1 To VBA.Len(xValue)
If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1)) Then
OutValue = OutValue & VBA.Mid(xValue, xIndex, 1)
End If
Next
TextOnly = OutValue
End Function
3. Çalışma sayfasına dönün ve boş bir hücreye ()=TextOnly(A1)) girin (hedefinizi belirtmek için A1 ifadesini değiştirin). Enter tuşuna basın ve sütun boyunca uygulamak için doldurma tutamacını sürükleyin. Sonuç, her dizgeden çıkarılan yalnızca harf karakterlerini gösterir.
Bu yöntem orijinal verilerinizi değiştirmez; çıktılar yeni hücrelerde veya sütunlarda görünür ve Kaynak Veri bozulmaz.
Sorun giderme ve ipuçları
- Makroları etkinleştirin ve kaydetme biçimini ayarlayın: Makroların etkin olduğundan emin olun ve dosyayı
.xlsmolarak kaydedin; böylece kullanıcı tanımlı işlev (UDF) kalıcı hale gelir. - Yalnızca harfler (tasarıma göre): Bu işlev, rakamları, noktalama işaretlerini ve sembolleri kaldırır. Boşlukları korumak için ikinci bağımsız değişken olarak
TRUEdeğerini geçirin (örneğin,)=TextOnly(A1, TRUE)). - Aksanlı/ASCII dışı harfler: Basit desen
[A-Za-z],é, ügibi karakterleri dışlar. Daha geniş alfabelere ihtiyacınız varsa, RegExp sürümünü kullanarak karakter aralıklarını genişletmeyi (örneğin,)[A-Za-zÀ-ÖØ-öø-ÿ]) veya metni önceden normalleştirmeyi düşünün. - Boş veya hata içeren hücreler: UDF, boş ya da hata değerleri için boş bir dize döndürür.
- Performans: UDF’ler çalışma sayfasıyla birlikte yeniden hesaplanır. Çok büyük aralıklarda, sonuçları aldıktan sonra bunları değerlere dönüştürmeyi düşünün.
Alfanümerik bir dizgeden yalnızca Metni Çıkart çıkartma, Kutools for Excel ile
Kod yazmadan daha hızlı bir çözüm tercih ediyorsanız, Kutools for Excel, hücrelerden istenmeyen sayıları ve harf olmayan karakterleri kaldırmak için son derece kullanışlı bir yol sunar. Bu yöntem, metin temizliğiyle sık sık uğraşan, toplu veri işleyen veya VBA yazmak istemeyen kullanıcılar için idealdir.
Genel bakış
- Ne yapar: Rakamları ve diğer harf olmayan karakterleri kaldırır, yalnızca harfleri bırakır.
- En iyi kullanım alanı: Formül veya makro kullanmadan tek seferlik ya da toplu temizlik işlemleri.
Kutools for Excel, seçili hücrelerden harf olmayan tüm karakterleri verimli bir şekilde kaldırmanıza olanak tanıyan Belirli Karakterleri Sil özelliğine sahiptir.
Kutools for Excel, çalışma sürecinizi kolaylaştırmak için 300Excel aracı sunar.30gün boyunca sınırsız olarak ücretsiz deneyebilirsiniz.Şimdi alın
Yalnızca harfleri çıkartmanın yöntemi şöyle:
1. İşlemek istediğiniz alfanümerik dizgeleri içeren hücre aralığını seçin.
2. Kutools > Metin > Belirli Karakterleri Sil yolunu izleyin. Bir iletişim kutusu açılacaktır. Ekran görüntüsüne bakın:
3. Belirli Karakterleri Sil iletişim kutusunda, yalnızca harfleri korumanın en basit yolu Alfabetik Olmayanseçeneğini işaretlemektir (bazı sürümlerde)Alfa Olmayan olarak etiketlenebilir). Bu işlem, A–Z karakterleri dışındaki her şeyi kaldırır. Sürümünüzde bu seçenek yoksa, Sayısal ve Yazdırılamayan seçeneklerini işaretleyin ve istemediğiniz sembolleri kaldırmak için (isteğe bağlı olarak) Özel seçeneğini kullanın. Ekran görüntüsüne bakın:
4. Tamam düğmesine tıklayın. Kutools, seçili hücrelerde yalnızca metin kısımlarını bırakmak üzere tüm harf olmayan içerikleri kaldırarak seçimi işleyecektir.
Sorun giderme ve ipuçları
- Yerinde değişiklik: Bu işlem, seçili hücrelerin üzerine yazar. Orijinal verileri korumak istiyorsanız, öncelikle yeni bir konuma kopyalayın.
- Belirli karakterleri koruyun: Boşlukları veya sembolleri (örneğin, tireleri) korumak için Alfabetik Olmayan seçeneğinden kaçının ve bunun yerine Sayısal, Yazdırılamayan ile Özel seçeneklerini birleştirerek hassas kontrol sağlayın.
Belirli Karakterleri Sil hakkında daha fazla bilgi edinin
Kutools yaklaşımı, hem küçük hem de büyük veri kümelerinde tek seferlik temizlik işlemleri için idealdir. Ancak dinamik (otomatik olarak güncellenen) sonuçlar elde etmek istiyorsanız, bunun yerine bir formül veya VBA UDF kullanmayı değerlendirmelisiniz.
Alfanümerik bir dizgeden yalnızca Metni Çıkart çıkartma, Excel formülü kullanarak
VBA veya eklentiler olmadan bir çözüm arayan kullanıcılar için, yerel Excel formüllerini kullanarak metinden karakterleri çıkartmak mümkündür. Bu yaklaşım, özellikle makroları veya üçüncü taraf araçları kullanamadığınız ortamlarda ve çıkarımın dinamik, formül odaklı olması gereken veri kümeleri için idealdir.
Bunu bir dizi formülüyle şöyle yapabilirsiniz:
1.Sonucunuzun görünmesini istediğiniz boş bir hücreyi seçin (örneğin B1) ve aşağıdaki formülü girin (yalnızca A–Z harflerini korur):
=TEXTJOIN("",
TRUE,
IF(
(CODE(MID(A1, ROW(INDIRECT("1:"&,LEN(A1))), 1))>,=65)*
(CODE(MID(A1, ROW(INDIRECT("1:"&,LEN(A1))), 1))<,=90) +
(CODE(MID(A1, ROW(INDIRECT("1:"&,LEN(A1))), 1))>,=97)*
(CODE(MID(A1, ROW(INDIRECT("1:"&,LEN(A1))), 1))<,=122),
MID(A1, ROW(INDIRECT("1:"&,LEN(A1))), 1),
""
)
)2. Formülü yazdıktan sonra, Excel 2019 veya önceki sürümlerini kullanıyorsanız, formülü bir dizi formülüne dönüştürmek için Ctrl+Shift+Enter tuşlarına basın. Microsoft Excel 365 veya Excel 2021 ve sonraki sürümlerini kullanıyorsanız ise yalnızca Enter tuşuna basın; çünkü bu sürümler dinamik dizi formüllerini doğrudan destekler.
A1 hücre referansını, alfanümerik dizesi içeren kendi hücrenizin referansıyla değiştirin. Formül, her dizeden yalnızca harfleri döndürecektir. Formülü daha fazla satıra uygulamak için B1 hücresinden aşağı doğru doldurma tutamacını sürükleyin.
Parametre açıklamaları:
- MID ve ROW(INDIRECT(…)), hedef hücredeki her karakteri numaralandırır.
- CODE, yalnızca harfleri korumak için ASCII aralıklarını kontrol eder: 65–90 (A–Z) ve 97–122 (a–z).
- TEXTJOIN, korunan karakterleri tek bir dizeye birleştirir.
- Hücre başına 1.000 karakterin altındaki orta uzunluktaki metinlerde en iyi şekilde çalışır; çok uzun metinler performansı yavaşlatabilir.
Bu yöntem, orijinal verilerinizi korur ve ekstra araçlar veya makro ayarlarına gerek duymaz. Hata alırsanız, hücre referanslarını ve dizi formülünü girme şeklinizi tekrar gözden geçirin.
Sorun giderme ve özet öneriler
- Hata görürseniz (örneğin,)
#DEĞER!), Excel sürümünüzü ve dizi formülünü (Ctrl+Shift+Enter — 2019 veya önceki sürümler için) kontrol edin. - Çıkartımdan sonra sonuçları beklenmedik boşluklar veya karakterler açısından gözden geçirin ve gerektiğinde düzeltmeler yapın. İş akışınızda Kutools, VBA veya formüllerden en uygun olanını seçin.
İlgili Makaleler:
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