كيفية استخراج الرمز البريدي من قائمة العناوين في 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 Function3. بعد لصق الكود، في نافذة محرر vba، اختر أدوات > المراجع. انظر لقطة الشاشة:
4. في مربع الحوار المراجع، قم بوضع علامة على Microsoft VBScript Regular Expressions 5.5 واضغط موافق.
5. عد إلى ورقة العمل، وأدخل هذه الصيغة: =ExtractPostcode(A2)، اسحب مقبض التعبئة لأسفل إلى الخلايا الأخرى. ستظهر جميع الرموز البريدية مرة واحدة، انظر لقطة الشاشة:
تلميح: باستخدام هذا الكود، يمكنك استخراج الأكواد البريدية تلقائيًا من أي بلد أو منطقة في Excel خلال ثوانٍ؛ من خلال تعديل التعبير العادي ليتناسب مع قواعد الرمز البريدي للمنطقة المستهدفة، يمكنك بسرعة التكيف مع تنسيقات مختلفة - مثل "SW1A 1AA" في المملكة المتحدة، و"12345-6789" في الولايات المتحدة، و"100000" في الصين - مما يعزز بشكل كبير كفاءة تنظيف البيانات وتحليلها.
مقالات ذات صلة:
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في Excel باستخدام Kutools لـ Excel، واختبر كفاءة غير مسبوقة. Kutools لـ Excel يوفر أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة الأكثر أهمية بالنسبة لك...
Office Tab يجلب واجهة التبويب إلى Office ويجعل عملك أسهل بكثير
- تفعيل تحرير وقراءة عبر التبويبات في Word، Excel، PowerPoint، Publisher، Access، Visio وProject.
- افتح وأنشئ عدة مستندات في تبويبات جديدة في نفس النافذة، بدلاً من نوافذ مستقلة.
- يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات اليومية من الفأرة!
جميع إضافات Kutools. مثبت واحد
حزمة Kutools for Office تجمع بين إضافات Excel وWord وOutlook وPowerPoint إضافة إلى Office Tab Pro، وهي مثالية للفرق التي تعمل عبر تطبيقات Office.
- حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
- مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
- الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
- تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد