استخدم صيغ Excel لتنظيف بيانات المبيعات عندما تحتاج إلى استخراج نطاقات البريد الإلكتروني، وتوحيد نصوص أرقام الهواتف، ومراجعة أسماء الشركات قبل استيرادها إلى CRM. احتفظ بالأعمدة الأصلية، واكتب كل نتيجة في عمود جديد حتى تتمكن من فحص التغييرات.
سأبدأ ببعض الصفوف التي قد تسبب مشكلات: رقم هاتف يبدأ بصفر، وعنوان بريد إلكتروني يفتقد إلى الفاصل، واسم شركة يتضمن كلمة تشبه لاحقة قانونية. فقد تتسبب صيغة تعمل جيدًا في أول صف منظم في إفساد هذه الحالات عند نسخها إلى بقية الصفوف.
يتناول هذا الدليل ثلاث مهام:
- استخراج أسماء النطاقات من عناوين البريد الإلكتروني.
- تنظيف علامات الترقيم في أرقام الهواتف مع الحفاظ على الأرقام الأصلية.
- إزالة لاحقة شركة جرى التحقق منها دون حذف كلمات من داخل الاسم.
📌 ملخص لمن لا يملك وقتًا
جهّز أعمدة المبيعات لاستيرادها إلى CRM بعد التحقق منها، باستخدام ثلاث عمليات مركزة في Excel. أبقِ القيم المصدر بجانب النتائج، ثم راجع الحالات الاستثنائية قبل استبدال أي شيء.
- المدخلات: عناوين البريد الإلكتروني، ونصوص أرقام الهواتف، وأسماء الشركات.
- المخرجات: نطاقات مستخرجة، ونصوص هواتف أزيلت منها علامات الترقيم المحددة، وأسماء شركات نُظفت وفق قاعدة صريحة للواحق.
كيفية استخراج النطاقات من عناوين البريد الإلكتروني
توجد في Excel طريقتان لاستخراج النطاقات من قائمة عناوين البريد الإلكتروني، وإليك شرحهما:
الطريقة الأولى: دالة TEXTAFTER، وهي متاحة في Microsoft 365 وExcel للويب وExcel 2024. راجع وثائق Microsoft الحالية للدالة لمعرفة مدى توفرها في إصدارك.
الطريقة الثانية: الجمع بين دوال RIGHT وLEN وFIND — وهي مناسبة لكل من لا يستطيع استخدام إصدار “TEXTAFTER”.
لاستخراج اسم النطاق من عنوان بريد إلكتروني، اتبع الخطوات التالية:
- أدخل الصيغة
=TEXTAFTERفي حقل الصيغة - حدد الخلية التي تحتوي على أول عنوان بريد إلكتروني لديك (مثل B3)
- أضف
@بوصفها النص المطلوب البحث عنه
ستبدو صيغة Excel كما يلي:
=TEXTAFTER(B3,"@")
ولتطبيق ذلك على جميع عناوين البريد الإلكتروني، اسحب الزاوية السفلية اليمنى للخلية التي تحتوي على الصيغة الأصلية.
استخراج نطاق البريد الإلكتروني في إصدارات Excel القديمة
سأوضح الآن كيفية استخراج اسم النطاق من عناوين البريد الإلكتروني باستخدام إصدار قديم من Excel لا يدعم دالة “TEXTAFTER”.
الصيغة التي سنستخدمها هي =RIGHT(B3,LEN(B3)-FIND("@",B3))
لاستخدام هذه الصيغة، الصقها في حقل الصيغة وطبّقها على الخلية التي تحتوي على أول عنوان بريد إلكتروني لديك.
إليك طريقة عمل الصيغة: تستخدم دالة RIGHT لجلب الأحرف من نهاية عنوان البريد الإلكتروني، بينما تعمل FIND وLEN معًا لتحديد عدد الأحرف المطلوب استخراجه بدقة.
لنستخدم john.doe123@abc.com مثالًا — يتكون هذا العنوان من 19 حرفًا إجمالًا:
تعطينا LEN(B3) عددًا قدره 19 حرفًا.
تبحث FIND عن الرمز @ في john.doe123@abc.com وتخبرنا بأنه الحرف رقم 12:
تعطينا FIND("@",B3) الرقم 12.
نطرح 12 من 19، ثم تنتقل النتيجة 7 مباشرة إلى دالة RIGHT.
وهكذا نطلب من Excel جلب آخر 7 أحرف، لنحصل في النهاية على abc.com.
لتطبيق هذه الصيغة على جميع الخلايا، اسحب الزاوية السفلية اليمنى للخلية التي تحتوي على الصيغة الأصلية إلى أسفل.
إذا كنت تفضل العمل باستخدام CSV، فيمكنك استخراج النطاقات في Datablist ومراجعة المخرجات بجانب العمود المصدر.
راجع استثناءات البريد الإلكتروني قبل نسخ الصيغة
تعرض TEXTAFTER(B3,"@") خطأً عند غياب الفاصل. ومن أمثلة صيغ المراجعة: =IFERROR(LOWER(TRIM(TEXTAFTER(B3,"@"))),"Review email"). تستخرج هذه الصيغة النص فحسب؛ ولا تتحقق من وجود العنوان أو إمكانية تسليم الرسائل إليه. ارفض الصفوف التي تحتوي على عدة عناوين أو عدة رموز @ بدلًا من اعتبار النص المستخرج بعد الرمز نطاق شركة موثوقًا.
على سبيل المثال، يعطينا alex@example.com النطاق example.com، بينما يحتاج alex.example.com إلى المراجعة. احتفظ بعنوان البريد الإلكتروني كاملًا بجانب النطاق كي لا يُدمج شخصان يعملان في الشركة نفسها عن طريق الخطأ باعتبارهما جهة اتصال واحدة.
تنظيف أرقام الهواتف وتنسيقها
سنقوم الآن بتنظيف أرقام الهواتف وتنسيقها في خطوتين.
تتمثل الخطوة الأولى في تنظيف أرقام الهواتف باستخدام الصيغة التالية:
=TEXTJOIN("",TRUE,TEXTSPLIT(B3,{"(",")","-"," ","."},,TRUE))
لا تزيل هذه الصيغة سوى علامات الترقيم المذكورة، وتعيد النتيجة في صورة نص. لا تضف +0 ولا تحوّل النتيجة إلى رقم بأي طريقة أخرى: فهذا يحذف الأصفار في البداية وعلامة + الدولية. استورد عمود الهاتف الأصلي كنص أيضًا؛ إذ لا تستطيع الصيغة استعادة الأصفار التي حذفها Excel بالفعل. تشرح Microsoft هذا السلوك في دليل الأصفار البادئة.
تتطلب TEXTSPLIT إصدارًا مدعومًا من Excel. يستخدم هذا المثال أسماء الدوال بالإنجليزية والفواصل للفصل بين الوسيطات؛ وقد تستخدم النسخة المترجمة أسماء أو فواصل مختلفة. كما أن الصيغة لا تتحقق من رموز الدول أو الأرقام الداخلية أو إمكانية الاتصال بالأرقام.
إليك شكل النتيجة بعد تطبيق هذه الصيغة.
طبّق الصيغة على جميع أرقام الهواتف بسحب الزاوية السفلية اليمنى للخلية الأصلية إلى أسفل، كما هو موضح أدناه.
على سبيل المثال، يتحول النص 020 7946 0958 إلى 02079460958، ويتحول +44 (20) 7946-0958 إلى +442079460958. احتفظ بالأرقام الداخلية بصورة منفصلة، وعلّم الصفوف التي تحتوي على حروف أو رموز غير متوقعة لمراجعتها. فرقم الهاتف معرّف وليس كمية تُستخدم في العمليات الحسابية.
توضح لقطات الشاشة التالية تنسيق عرض أمريكي اختياريًا. لا تستخدمه إلا مع أرقام أمريكية مكوّنة من عشرة أرقام بعد مراجعتها، وفي عمود عرض منفصل. احتفظ بالنص المنظف لعمليات التصدير الدولية والاستيراد إلى CRM؛ فخطوة العرض هذه ليست توحيدًا دوليًا للأرقام.
أنشئ عمودًا منفصلًا باسم أرقام الهواتف المنسقة.
حدد الخلية الموجودة أسفل العنوان، ثم استخدم اختصار Excel "Command" + "1" لفتح جميع خيارات التنسيق.
حدد "Special" بوصفه خيار التنسيق.
حدد "Phone Numbers" ثم اختر دولة أرقام الهواتف.
💡 ملاحظة حول تنسيق الأرقام
تغير تنسيقات الأرقام في Excel طريقة ظهور الخلايا الرقمية، لكنها لا تضيف رمز دولة مفقودًا ولا تتحقق من صحة رقم الهاتف. بالنسبة إلى القوائم الدولية، احتفظ بالقيم النصية وراجع معلومات الدولة قبل اختيار آلية مخصصة لتنظيف الهواتف.
يمكنك الآن إغلاق اللوحة، ثم التحقق من تطبيق Excel للتنسيق.
ولفعل ذلك، تحقق ببساطة من ظهور "Special" في حقل التنسيق الخاص بالعمود. وقد أبرزت ذلك في الصورة أدناه.
الخطوة الأخيرة هي إدخال صيغة في هذا العمود تنقل أرقام الهواتف التي نظفناها للتو إلى العمود الجديد المُعدّ لعرض الأرقام الأمريكية.
في مثال العرض المخصص للأرقام الأمريكية التي تمت مراجعتها، توفر =VALUE(D3) قيمة رقمية لعمود العرض. أبقِ D3 دون تغيير بوصفها نصًا. أما المرجع مثل =D3 وحده فيظل نصًا، ولن يكتسب تنسيق عرض أرقام الهواتف الرقمية.
بعد كتابة الصيغة، اضغط على "ENTER" لتطبيقها.
إذا نفذت كل شيء بطريقة صحيحة، فستبدو النتيجة كما يلي:
اسحب الآن الزاوية السفلية اليمنى إلى أسفل لتطبيق التنسيق على جميع أرقام الهواتف في Excel، كما هو موضح أدناه:
تنظيف أسماء الشركات
قد يكون تنظيف أسماء الشركات من أهم الخطوات التي ينبغي تنفيذها قبل بدء Outbound للعملاء المحتملين، ولهذا حرصت على تضمينه في هذا الدليل.
استخدم قاعدة للواحق يمكنك تفسيرها بوضوح. تستبدل SUBSTITUTE النص المطابق أينما ظهر. فعلى سبيل المثال، تعرض =SUBSTITUTE("Acme Consulting"," Co","") النتيجة Acmensulting، ما يعني حذف جزء من الاسم الحقيقي. كما أن المطابقة حساسة لحالة الأحرف: إذا استخدمت " co" بدلًا من ذلك، فسيبقى Acme Consulting دون تغيير ما لم تحوّل النص المصدر أولًا إلى أحرف صغيرة. وتعتمد سلسلة الاستبدالات أيضًا على ترتيبها. لذلك لا أنصح بتطبيق سلسلة الاستبدال الواسعة هذه على أحد أعمدة CRM.
بالنسبة إلى قائمة تعتمد القاعدة المعتمدة إزالة LLC عندما تسبقها مسافة وترد في نهاية الاسم، استخدم:
=IF(RIGHT(TRIM(B4),4)=" LLC",TRIM(LEFT(TRIM(B4),LEN(TRIM(B4))-4)),TRIM(B4))
هذه القاعدة ضيقة عن قصد. يتحول Acme LLC إلى Acme، بينما يظل كل من Acme LLC Services وAcme Consulting دون تغيير. ولا تغطي القاعدة L.L.C. أو جميع الأشكال القانونية أو كل البلدان. أضف قواعد منفصلة تمت مراجعتها عندما تتطلب بياناتك ذلك.
تعرض لقطات الشاشة الحالية أدناه المثال القديم للاستبدال الواسع. استخدم القاعدة الصريحة أعلاه لإنتاج النتيجة النهائية، وقارنها بالاسم الأصلي قبل قبول التغيير.
طريقة عمل الصيغة الأضيق: تزيل TRIM المسافات العادية المحيطة بالنص، وتفحص RIGHT الأحرف الأربعة الأخيرة، ثم تحذف LEFT هذه النهاية التي تم التحقق منها فقط. لا تراعي المقارنة حالة الأحرف في صيغ Excel العادية. ولا تفترض أن TRIM تزيل جميع محارف المسافات في Unicode.
إليك شكل النتيجة عند تطبيق صيغة تنظيف اسم الشركة:
ولا تنسَ سحب الزاوية السفلية اليمنى للخلية التي تحتوي على الصيغة الأصلية إلى أسفل لتطبيقها على جميع أسماء الشركات، كما هو موضح هنا:
لكن ما زال هناك أمر واحد: حالة الأحرف في أسماء الشركات غير منسقة.
استخدم =PROPER(D4) فقط عندما تكون حالة أحرف العناوين هي النتيجة المطلوبة. فهي تحول IBM إلى Ibm، لذا حافظ على حالة الأحرف الرسمية للعلامة التجارية أو أضف استثناءات صريحة عند الحاجة. توضح لقطات الشاشة عملية التحويل، ولا تضمن الحصول على الاسم التجاري الصحيح.
إليك شكل النتيجة عند تنفيذ الخطوة بطريقة صحيحة:
أخيرًا، اسحب الزاوية السفلية اليمنى إلى أسفل لتطبيق الصيغة على قائمة أسماء الشركات كاملة.
إذا كانت لديك بالفعل قائمة معتمدة بالأسماء القديمة والجديدة، فاتبع آلية استبدال قيم متعددة لتطبيق عمليات الربط هذه.
بالنسبة إلى الأسماء الأكثر تنوعًا، يقدم دليل تنظيف أسماء الشركات باستخدام AI خيارًا بديلًا. احتفظ بالقيم الأصلية، وراجع اللواحق القانونية والاختصارات والحالات الملتبسة أيًا كانت الطريقة التي تختارها.




















