كيفية استخراج الرمز البريدي من قائمة العناوين في Excel؟

عند إدارة معلومات العملاء في Excel، من الشائع وجود قائمة بالعناوين الكاملة حيث تحتوي كل إدخال على تفاصيل الشارع والرمز البريدي (الرمز البريدي). إذا كنت بحاجة إلى عزل واستخراج الرموز البريدية فقط من هذه العناوين - ربما لقوائم المراسلات، أو التحليل الإقليمي، أو تنسيق البيانات - فإن فصلها يدويًا واحدة تلو الأخرى يمكن أن يكون مملًا وعرضة للأخطاء، خاصة مع مجموعات البيانات الكبيرة. لحسن الحظ، يوفر Excel طرقًا عملية لاستخراج العديد من الرموز البريدية بسرعة وكفاءة دفعة واحدة دون جهد يدوي. تقدم هذه المقالة تعليمات خطوة بخطوة لاستخراج الرموز البريدية من العناوين باستخدام صيغة Excel وحل ماكرو VBA.

استخراج الرمز البريدي باستخدام الصيغة في Excel

استخراج الرمز البريدي باستخدام وظيفة معرفة المستخدم في Excel

استخراج الرمز البريدي باستخدام الصيغة في Excel

في العديد من قوائم العناوين الشائعة، يكون الرمز البريدي موجودًا في نهاية سلسلة العنوان ولا يزيد طوله عن 8 أحرف. هذه الطريقة هي الأنسب عندما تتبع جميع العناوين في قائمتك تنسيقًا ثابتًا ويظهر الرمز البريدي كعنصر أخير في كل عنوان، مفصولة بمسافات.

لاستخراج الرمز البريدي بسرعة باستخدام صيغة، اتبع الخطوات التالية:

1. حدد خلية فارغة حيث تريد أن يظهر الرمز البريدي (على سبيل المثال، B1 إذا كان عنوانك في A1). أدخل الصيغة التالية:

=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1))

2. اضغط على مفتاح Enter. سيظهر الرمز البريدي من العنوان في A1 في الخلية المحددة.

3. لتطبيق هذه الصيغة على عناوين أخرى، حدد الخلية التي تحتوي على الصيغة، واسحب مقبض التعبئة لأسفل على طول العمود لتغطية جميع صفوف العناوين، وسيقوم Excel باستخراج الرمز البريدي لكل عنوان تلقائيًا.

تلميح: هذه الصيغة تفترض أن الرمز البريدي هو العنصر الأخير بعد الفراغ النهائي في سلسلة العنوان. إذا كانت عناوينك منظمة بشكل مختلف، مثل ظهور الأكواد البريدية في المنتصف، أو مفصولة بفواصل، فقد لا تعيد هذه الصيغة النتيجة الصحيحة. لتوسيع هذه الطريقة لأكواد بريدية دولية أو تنسيقات مخصصة، قد تحتاج إلى نهج صيغة مختلف أو أكثر تقدمًا، أو جرب حل VBA أدناه.

استخراج الرمز البريدي باستخدام وظيفة معرفة المستخدم في Excel

إذا كانت بيانات العنوان الخاصة بك أكثر تعقيدًا وغير متسقة أو تحتوي على رموز بريدية بتنسيقات ومواقع مختلفة داخل السلسلة كما هو موضح في لقطة الشاشة التالية، يمكن لماكرو VBA تقديم خيارات استخراج أكثر مرونة. تقوم هذه الطريقة الآلية بمعالجة عمود كامل من العناوين بسرعة لعزل وإرجاع الرموز البريدية فقط، مما يوفر الكثير من الوقت ويقلل من الجهد اليدوي.

1. اضغط Alt + F11 لفتح نافذة Microsoft Visual Basic for Applications.

2. في نافذة VBA، انقر فوق إدراج > وحدة لإنشاء وحدة جديدة. انسخ والصق الكود التالي للماكرو VBA في نافذة الوحدة:

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. بعد لصق الكود، في نافذة محرر vba، اختر أدوات > المراجع. انظر لقطة الشاشة:

4. في مربع الحوار المراجع، قم بوضع علامة على Microsoft VBScript Regular Expressions 5.5 واضغط موافق.

5. عد إلى ورقة العمل، وأدخل هذه الصيغة: =ExtractPostcode(A2)، اسحب مقبض التعبئة لأسفل إلى الخلايا الأخرى. ستظهر جميع الرموز البريدية مرة واحدة، انظر لقطة الشاشة:

تلميح: باستخدام هذا الكود، يمكنك استخراج الأكواد البريدية تلقائيًا من أي بلد أو منطقة في Excel خلال ثوانٍ؛ من خلال تعديل التعبير العادي ليتناسب مع قواعد الرمز البريدي للمنطقة المستهدفة، يمكنك بسرعة التكيف مع تنسيقات مختلفة - مثل "SW1A 1AA" في المملكة المتحدة، و"12345-6789" في الولايات المتحدة، و"100000" في الصين - مما يعزز بشكل كبير كفاءة تنظيف البيانات وتحليلها.

مقالات ذات صلة:

• استخراج الأحرف الأولى من الأسماء في Excel
• استخراج القيمة العشرية من السلسلة في Excel