استبدال النص باستخدام SUBSTITUTE

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

تبحث الدالة SUBSTITUTE عن جزء من النص داخل خلية، وتستبدل كل تكرار له بنص جديد، من دون تغيير الخلية الأصلية.

وهي المكافئ القائم على الصيغ لميزة البحث والاستبدال، لكنها تتحدّث تلقائيًا عند تغيّر بياناتكم.

تقبل SUBSTITUTE ما يصل إلى أربع وسيطات:

• text السلسلة النصية أو الخلية الأصلية
• old_text النص المطلوب البحث عنه
• new_text النص الذي سيحل محلّه
• instance_num (اختيارية) رقم التكرار المطلوب استبداله

تكون البنية على الشكل =SUBSTITUTE(text, old_text, new_text, [instance_num]). إذا حذفتم الوسيطة الأخيرة، فسيتم استبدال كل التكرارات.

من الحيل الشائعة استبدال شيء بـلا شيء لحذفه. ويتم ذلك بتعيين new_text إلى سلسلة فارغة "".

إذا كانت A2 تحتوي على 555-123-4567، فستزيل الصيغة أدناه كل شرطة، وتعيد القيمة 5551234567.

ينظّف هذا النمط أرقام الهواتف والمعرّفات والقيم الأخرى التي تحتوي على علامات ترقيم.

تعمل SUBSTITUTE مع الكلمات الكاملة أيضًا، وليس مع الأحرف المفردة فقط.

إذا كانت A2 تحتوي على Annual report 2023، فيمكنكم تحديث السنة باستبدال الكلمة:

ستكون النتيجة Annual report 2024. ويُغيَّر كل تكرار مطابق في الخلية.

مهم: تطابق SUBSTITUTE النص بدقة، بما في ذلك حالة الأحرف.

إذا كانت A2 تحتوي على Cat cat CAT، فإن =SUBSTITUTE(A2, "cat", "dog") ستغيّر فقط cat المكتوبة بأحرف صغيرة، لتصبح النتيجة Cat dog CAT.

إذا كنتم بحاجة إلى استبدال غير حساس لحالة الأحرف، فطبّعوا حالة الأحرف أولًا باستخدام LOWER أو UPPER، أو استخدموا دوال التعبيرات النمطية في Sheets ضمن الدرس التالي.

تتيح لكم الوسيطة الرابعة الاختيارية، instance_num، استهداف تكرار واحد بدلًا من استبدالها كلها.

إذا كانت A2 تحتوي على a-b-c-d، وكنتم تريدون تغيير الشرطة الثانية فقط، فعيّنوا instance_num إلى 2.

ستكون النتيجة a-b/c-d: تحوّلت الشرطة الثانية فقط إلى شرطة مائلة.

تخيّلوا مبلغًا مستوردًا مخزّنًا كنص: $1,250.00. لتحويله إلى رقم قابل للاستخدام، يجب عليكم إزالة علامة الدولار والفاصلة.

ضمّنوا استدعاءين للدالة SUBSTITUTE، أحدهما داخل الآخر، لإزالة الحرفين.

يزيل الاستدعاء الداخلي الفاصلة، بينما يزيل الاستدعاء الخارجي علامة الدولار، لتبقى القيمة 1250.00.

يمكنكم تضمين العدد الذي تحتاجونه من استدعاءات SUBSTITUTE، بحيث ينظّف كل استدعاء حرفًا مختلفًا.

لتوحيد رمز فوضوي يحتوي على مسافات ونقاط وشرطات مائلة وتحويله إلى شرطات، اربطوا ثلاث عمليات استبدال.

تمرّر كل طبقة نتيجتها إلى الطبقة التالية، وبذلك تُبنى السلسلة النصية المنظّفة النهائية تدريجيًا.

لا تخلطوا بين SUBSTITUTE والدالة المشابهة REPLACE.

• تستبدل SUBSTITUTE النص عبر مطابقة محتواه (ابحثوا عن هذه الكلمة واستبدلوها).
• تستبدل REPLACE النص حسب موضعه (استبدلوا 4 أحرف بدءًا من الموضع 5).

استخدموا SUBSTITUTE عندما تعرفون ما الذي تريدون البحث عنه، واستخدموا REPLACE عندما تعرفون أين يوجد.

إضافة مفيدة: ادمجوا SUBSTITUTE مع LEN لعدّ عدد مرات ظهور شيء ما.

اطرحوا طول النص بعد إزالة حرف من الطول الأصلي. إذا كانت A2 تحتوي على a,b,c,d، فستعدّ هذه الطريقة الفواصل، وتعيد القيمة 3.

تُعدّ هذه الحيلة مفيدة جدًا لعدّ العناصر في قائمة مفصولة.

لنفترض أن A2 تحتوي على أرقام فوضوية فيها نقاط ومسافات مثل 555.123 4567. وتريدون تنسيقًا موحّدًا بالشرطات مثل 555-123-4567.

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

يؤدي تضمين عمليات الاستبدال إلى توحيد أي فواصل غير مرغوبة تظهر.

تعلّمتم البحث عن النص واستبداله داخل الصيغ:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• استخدموا "" بوصفها قيمة new_text من أجل حذف الأحرف.
• الدالة حساسة لحالة الأحرف؛ طبّعوا حالة الأحرف أولًا عند الحاجة.
• تستهدف instance_num الاختيارية تكرارًا واحدًا.
• ضمّنوا الاستدعاءات لتنظيف عدة أحرف، وقرنوها مع LEN لعدّ التكرارات.

لقد أكملتم قسمي ربط النص وتغيير حالته. أحسنتم العمل على إنشاء بيانات نظيفة ومتسقة!