Excel'de adres listesinden posta kodu nasıl çıkarılır?
Excel'de müşteri bilgilerini yönetirken, her bir girdinin hem sokak detaylarını hem de posta kodunu (zip kodu) içerdiği tam adres listesiyle karşılaşmak yaygındır. Eğer bu adreslerden yalnızca posta kodlarını izole etmek ve çıkarmak istiyorsanız - belki de posta listeleri için, bölgesel analizler için veya veri biçimlendirmesi için - bunları tek tek elle ayırmak sıkıcı ve hata yapmaya açık olabilir, özellikle büyük veri setleriyle çalışırken. Neyse ki, Excel, manuel çaba gerektirmeden birden fazla posta kodunu hızlı ve verimli bir şekilde çıkarmak için pratik yöntemler sunar. Bu makale, bir Excel formülü ve bir VBA makrosu çözümü kullanarak adreslerden posta kodlarını çıkarmak için adım adım talimatlar sağlar.
Excel'de formül ile posta kodu çıkarın
Excel'de Kullanıcı Tanımlı Fonksiyon ile posta kodu çıkarın
Excel'de formül ile posta kodu çıkarın
Birçok yaygın adres listesinde, posta kodu adres dizesinin sonunda bulunur ve 8 karakterden uzun değildir. Bu yöntem, listenizdeki tüm adresler tutarlı bir formatta olduğunda ve posta kodu her adresin son öğesi olarak boşluklarla ayrıldığında en uygun olanıdır.
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'deki adresten posta kodu seçilen hücrede görüntülenecektir.
3. Bu formülü diğer adreslere uygulamak için, formül içeren hücreyi seçin, dolgu tutamacını sütun boyunca aşağı doğru sürükleyerek tüm adres satırlarını kapsayın ve Excel her bir adres için posta kodunu otomatik olarak çıkaracaktır.
İpucu: Bu formül, posta kodunun adres dizesindeki son boşluğun ardından gelen son öğe olduğunu varsayar. Eğer adresleriniz farklı bir yapıda ise, örneğin posta kodları ortada yer alıyorsa veya virgüllerle ayrılmışsa, bu formül doğru sonucu vermeyebilir. Bu yöntemi uluslararası posta kodları veya özel formatlar için genişletmek isterseniz, farklı veya daha gelişmiş bir formül yaklaşımı kullanmanız gerekebilir ya da aşağıdaki VBA çözümünü deneyebilirsiniz.
Excel'de Kullanıcı Tanımlı Fonksiyon ile posta kodu çıkarın
Adres verileriniz daha karmaşık, tutarsız veya posta kodlarını çeşitli formatlarda ve dizenin içinde farklı konumlarda içeriyorsa, bir VBA makrosu daha esnek çıkarma seçenekleri sunabilir. Bu otomatikleştirilmiş yöntem, bir adresler sütununu hızlıca işleyerek yalnızca posta kodlarını izole eder ve döndürür, önemli ölçüde zaman kazandırır ve manuel çabayı en aza indirir.
1. Microsoft Visual Basic for Applications penceresini açmak için Alt + F11 tuşlarına basın.
2. VBA penceresinde, Insert > Module'a 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 editörü penceresinde Araçlar > Referanslar'ı seçin. Ekran görüntüsüne bakın:
4. Referanslar iletişim kutusunda Microsoft VBScript Regular Expressions 5.5'i işaretleyin ve Tamam'a tıklayın.
5. Çalışma sayfasına dönün ve şu formülü girin: =ExtractPostcode(A2), dolgu tutamacını diğer hücrelere doğru aşağı sürükleyin. Tüm posta kodları anında görüntülenecektir, ekran görüntüsüne bakın:
İpucu: Bu kod ile, hedef bölgenizin posta kodu kurallarına uyacak şekilde düzenli ifadeyi basitçe değiştirerek Excel'de herhangi bir ülke veya bölgeden saniyeler içinde posta kodlarını otomatik olarak çıkarabilirsiniz; İngiltere'nin “SW1A 1AA”, Amerika Birleşik Devletleri'nin “12345-6789” veya Çin'in “100000” gibi farklı formatlara hızla uyum sağlayabilirsiniz, bu da veri temizleme ve analiz verimliliğinizi büyük ölçüde artırır.
İlgili Makaleler:
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına 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.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin