Excel'de adres listesinden posta kodu nasıl çıkarılır?
Excel'de müşteri bilgilerini yönetirken, her bir kaydın hem sokak detaylarını hem de bir posta kodunu içeren tam adreslerden oluşan bir listeyle karşılaşmak oldukça yaygındır. Bu adreslerden yalnızca posta kodlarını ayırmak ve çıkarmak gerekiyorsa—örneğin e-posta listesi oluşturma, bölgesel analiz veya veri biçimlendirme gibi amaçlarla—bunları tek tek manuel olarak işlemek, özellikle büyük veri kümelerinde hem sıkıcı hem de hata yapmaya açık olabilir. Neyse ki Excel, bu süreci manuel çaba gerektirmeden, birden fazla posta kodunu aynı anda hızlı ve verimli şekilde çıkarmanın pratik yollarını sunar. Bu makale, adreslerden posta kodlarını ayıklamak için hem bir Excel formülü hem de VBA makrosu çözümüyle adım adım talimatlar sağlar.
Excel'de formülle posta kodu çıkarın
Excel'de Kullanıcı Tanımlı İşlev ile posta kodu çıkarın
Excel'de formülle posta kodu çıkarın
Birçok yaygın Adres Listesi’nde posta kodu, adres dizesinin sonunda yer alır ve 8 karakterden uzun değildir. Bu yöntem, listenizdeki tüm adresler tutarlı bir biçime sahip olduğunda ve posta kodu her adreste son öğe olarak boşluklarla ayrıldığında en uygundur.
Formül kullanarak posta kodunu hızlıca çıkarmak için şu adımları izleyin:
1. Posta kodunun görünmesini istediğiniz boş bir hücreyi seçin (örneğin, adresiniz A1’deyse B1). Aşağıdaki formülü girin:
=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&,LEN(A1))),1))=" "))-1))+1,LEN(A1))2.Enter tuşuna basın. A1 hücresindeki adresin posta kodu, seçilen hücrede görüntülenecektir.
3. Bu formülü diğer adreslere uygulamak için formülün bulunduğu hücreyi seçin ve sütun boyunca tüm adres satırlarını kapsayacak şekilde doldurma tutamacını aşağı doğru sürükleyin; Excel her adres için posta kodunu otomatik olarak çıkaracaktır.
İpucu: Bu formül, posta kodunun adres dizesindeki son boşluktan sonraki son öğe olduğunu varsayar. Adresleriniz farklı yapıdaysa—örneğin posta kodları ortada yer alıyor veya virgülle ayrılıyor sa—bu formül doğru sonucu vermeyebilir. Uluslararası posta kodlarını veya özel formatları desteklemek için bu yöntemi genişletmek istiyorsanız, farklı ya da daha gelişmiş bir formül yaklaşımı gerekebilir; alternatif olarak aşağıdaki VBA çözümünü de deneyebilirsiniz.
Excel'de Kullanıcı Tanımlı İşlev ile posta kodu çıkarın
Adres verileriniz daha karmaşıkse, tutarsızsa veya aşağıdaki ekran görüntüsünde gösterildiği gibi posta kodlarını dize içinde çeşitli biçimlerde ve konumlarda içeriyorsa, bir VBA makrosu daha esnek çıkarma seçenekleri sunabilir. Bu otomatik yöntem, tüm sütundaki adresleri hızlıca işleyerek yalnızca posta kodlarını ayıklar ve döndürür; böylece önemli ölçüde zaman kazanılır ve manuel çaba en aza indirilir.
1. Alt + F11 tuşlarına basarak Microsoft Visual Basic for Applications penceresini açın.
2. VBA penceresinde Ekle > Modül seçeneğine tıklayarak yeni bir modül oluşturun. Aşağıdaki VBA kodunu modül penceresine kopyalayıp yapıştırın:
Public Function ExtractPostcode(text As String) As String
Dim reg As New RegExp
Dim m As MatchCollection
reg.Pattern = "\b([A-Z]{1,2}\d{1,2}[A-Z]?\s*\d[A-Z]{2}|\d{5}(?:-\d{4})?|\d{6})\b"
reg.IgnoreCase = True
reg.Global = False
If reg.Test(text) Then
Set m = reg.Execute(text)
ExtractPostcode = m(0).Value
Else
ExtractPostcode = ""
End If
End Function3. Kodu yapıştırdıktan sonra VBA düzenleyici penceresinde Araçlar > Başvurular seçeneğini belirleyin. Ekran görüntüsüne bakın:
4. Başvurular iletişim kutusunda Microsoft VBScript Regular Expressions 5,5 kutusunu işaretleyin ve Tamam düğmesine tıklayın.
5. Çalışma sayfasına dönün ve şu formülü girin: =ExtractPostcode(A2), ardından doldurma tutamacını diğer hücrelere sürükleyin. Tüm posta kodları anında görüntülenecektir—ekran görüntüsüne bakın:
İpucu: Bu kodla, hedef bölgenizin posta kodu kurallarına uygun olarak düzenli ifadeyi hafifçe özelleştirerek İngiltere’nin «SW1A 1AA», ABD’nin «12345-6789» veya Çin’in «100000» gibi farklı biçimlere kolayca uyarlayabilir; böylece Excel’de herhangi bir ülkeden ya da bölgeden posta kodlarını saniyeler içinde otomatik olarak çıkarabilirsiniz. Bu da veri temizleme ve analiz verimliliğinizi büyük ölçüde artırır.
İ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