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 Function

3. 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:

• Excel'de isimlerden baş harfleri çıkarın
• Excel'de dizeden ondalık değer çıkarın