دليل Excel – استخراج نص أو رقم حسب الموضع المحدد

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

استخراج النص حسب الموضع

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

1. استخراج عدد الأحرف من اليسار أو اليمين

لاستخراج عدد الأحرف من الجانب الأيسر أو الأيمن من السلسلة النصية، يمكنك تجربة إحدى الطرق التالية.

1.1 استخراج أول أو آخر N أحرف باستخدام الصيغ

افترض أن لديك قائمة بسلاسل نصية في العمود B كما هو موضح في الصورة أدناه، لاستخراج أول حرفين وآخر حرفين من كل سلسلة، يمكنك تطبيق الصيغ التالية.

استخراج أول N أحرف من سلسلة نصية

يمكن أن تساعدك دالة LEFT في استخراج أول N أحرف بسهولة من سلسلة نصية في Excel.

الصيغة العامة

=LEFT(text_string,[num_chars])

المعاملات

الآن يمكنك تطبيق هذه الصيغة لاستخراج أول حرفين من الخلايا في العمود B.

1. حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة الأولى. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لتطبيق الصيغة على الخلايا الأخرى.

=LEFT(B5,2)

الآن تم استخراج أول حرفين في كل خلية من النطاق المحدد.

استخراج آخر N أحرف من سلسلة نصية

هنا نستخدم دالة RIGHT لاستخراج آخر N أحرف من سلسلة نصية في Excel.

الصيغة العامة

=RIGHT(text_string,[num_chars])

المعاملات

حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل للحصول على النتائج الأخرى.

=RIGHT(B5,2)

1.2 استخراج أول أو آخر N أحرف باستخدام أداة مذهلة

على الرغم من أن الصيغ أعلاه بسيطة، إلا أنه لاستخراج أول أو آخر n أحرف من قائمة طويلة من السلاسل النصية، ستحتاج إلى سحب مقبض التعبئة التلقائية من الأعلى إلى الأسفل، مما قد يستغرق بعض الوقت. هنا، نوصي باستخدام أداة "Extract Text" من Kutools لـ Excel لمساعدتك على استخراج أول أو آخر N أحرف من قائمة السلاسل النصية دفعة واحدة.

1. حدد مسبقًا قائمة السلاسل النصية التي تريد استخراج النص منها، ثم انقر على "Kutools" > "Text" > "Extract Text".

2. في مربع الحوار المنبثق "Extract Text"، تحتاج إلى ضبط الإعدادات كما يلي.

ملاحظة: لجعل النتيجة ديناميكية عند تغيير السلسلة النصية، يمكنك تحديد مربع "إدراج كصيغة".

3. في مربع الحوار المنبثق التالي "Extract Text"، اختر خلية لإخراج الأحرف المستخرجة ثم انقر على "موافق".

بعد ذلك، يتم استخراج أول أو آخر N أحرف من الخلايا المحددة دفعة واحدة.

انقر لمعرفة المزيد عن هذه الميزة.

2. استخراج النص قبل أو بعد حرف/كلمة معينة

لاستخراج النص قبل أو بعد حرف أو كلمة معينة، ستلبي السيناريوهات المختلفة في هذا القسم احتياجاتك.

2.1 استخراج النص قبل أو بعد أول فاصل (حرف)

كما هو موضح في الصورة أدناه، لاستخراج النص قبل أو بعد أول فاصل من كل خلية في النطاق B4:B10، يمكنك تطبيق إحدى الطرق أدناه.

2.1.1 استخراج النص قبل أول فاصل باستخدام الصيغة

تطبيق صيغة تعتمد على دالتي LEFT وFIND يمكن أن يساعدك على استخراج النص قبل أول فاصل من الخلية. يمكنك اتباع الخطوات التالية لإنجاز ذلك.

الصيغة العامة

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

المعاملات

حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة الأولى. ثم حدد خلية النتيجة الأولى واسحب مقبض التعبئة التلقائية لأسفل للحصول على النصوص قبل أول فاصل في الخلايا الأخرى.

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 استخراج النص بعد أول فاصل باستخدام الصيغة

الصيغة أدناه تساعدك على استخراج النص بعد أول فاصل من الخلية في Excel.

الصيغة العامة

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

المعاملات

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 استخراج النص قبل أو بعد أول فاصل باستخدام أداة مذهلة

هنا، نوصي بشدة باستخدام أداة "Extract Text" من Kutools لـ Excel. باستخدام هذه الميزة، يمكنك بسهولة استخراج النصوص قبل أو بعد أول فاصل من مجموعة من الخلايا دفعة واحدة.

1. حدد نطاق الخلايا الذي تريد استخراج النص منه، ثم انقر على "Kutools" > "Text" > "Extract Text".

2. في مربع الحوار "Extract Text"، تحتاج إلى ضبط الإعدادات كما يلي.

ملاحظة: لجعل النتيجة ديناميكية عند تغيير السلسلة النصية، يمكنك تحديد مربع إدراج كصيغة.

3. بعد ذلك يظهر مربع حوار آخر "Extract Text"، اختر خلية لإخراج النتائج ثم انقر على "موافق".

بعد ذلك يتم استخراج النصوص قبل أو بعد أول فاصل من الخلايا المحددة دفعة واحدة.

لمعرفة المزيد حول هذه الميزة، يرجى زيارة: استخراج نص معين بسرعة من الخلايا في Excel.

2.2 استخراج النص قبل أو بعد آخر فاصل (حرف)

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

2.2.1 استخراج النص قبل آخر فاصل باستخدام الصيغة

لاستخراج النص قبل آخر فاصل من الخلية، يمكنك استخدام دوال SEARCH وLEN وSUBSTITUTE داخل دالة LEFT.

الصيغة العامة

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

المعاملات

حدد خلية، أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لاستخراج النصوص من السلاسل النصية الأخرى في نفس العمود.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 استخراج النص بعد آخر فاصل باستخدام الصيغة

بعد استخراج النص قبل آخر فاصل من الخلية، يمكنك تطبيق الصيغة أدناه لاستخراج النص بعد آخر فاصل حسب الحاجة.

الصيغة العامة

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

المعاملات

حدد خلية، أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لاستخراج النصوص من السلاسل النصية الأخرى في نفس العمود.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 استخراج النص بعد الحرف الثاني والعشرين

انظر المثال أدناه، هناك قائمة بسلاسل نصية في النطاق B4:B10، لاستخراج النص بعد الحرف الثالث من كل خلية، يمكنك تطبيق صيغة تعتمد على دالتي MID وLEN.

الصيغة العامة

=MID(text_string,nth_char+1,LEN(text_string))

المعاملات

حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل للحصول على النتائج الأخرى.

=MID(B5,3+1,LEN(B5))

2.4 استخراج الكلمة الثانية والعشرين من سلسلة نصية

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

2.4.1 استخراج الكلمة الثانية والعشرين باستخدام الصيغة

يمكنك دمج دوال TRIM وMID وSUBSTITUTE وREPT وLEN لاستخراج الكلمة الثانية والعشرين من سلسلة نصية في الخلية.

الصيغة العامة

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

المعاملات

في هذا المثال، يحتوي النطاق B5:B10 على السلاسل النصية، ويحتوي D5:D10 على الأرقام التي تمثل الكلمة الثانية والعشرين، دعنا نطبق هذه الصيغة لاستخراج الكلمة الثانية والعشرين من السلسلة النصية.

حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة الأولى. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل للحصول على الكلمة الثانية والعشرين من الخلايا الأخرى.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

ملاحظة: يمكنك كتابة الرقم الثاني والعشرين مباشرة في الصيغة كما يلي.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 استخراج الكلمة الثانية والعشرين باستخدام دالة معرفة من قبل المستخدم

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

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

2. في نافذة "Microsoft Visual Basic for Applications"، انقر على "Insert" > "Module"، ثم انسخ الكود VBA أدناه إلى نافذة الكود.

كود VBA: استخراج الكلمة الثانية والعشرين من سلسلة نصية في خلية

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. اضغط على مفاتيح "Alt" + "Q" لإغلاق نافذة "Microsoft Visual Basic for Applications".

4. عد إلى ورقة العمل التي تحتوي على السلاسل النصية التي تريد استخراج الكلمة الثانية والعشرين منها. حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على الكلمة الثانية والعشرين.

=FindWord(B5,D5)

أو

=FindWord(B5,2)

ملاحظة: في الصيغة، D5 هي الخلية التي تحتوي على رقم يمثل الكلمة الثانية والعشرين. بدلاً من ذلك، يمكنك استبدال مرجع الخلية برقم مباشرة.

5. حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لاستخراج الكلمة الثانية والعشرين من السلاسل النصية في الخلايا الأخرى.

2.4.3 استخراج الكلمة الثانية والعشرين باستخدام أداة مذهلة

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

1. حدد خلية لوضع النتيجة، ثم انقر على "Kutools" > "Formula Helper" > "Text" > "Extract" "the nth word in cell". انظر الصورة:

2. في مربع الحوار "Formulas Helper"، تحتاج إلى ضبط الإعدادات كما يلي.

3. بعد ذلك يتم استخراج الكلمة الثانية والعشرين من السلسلة النصية في الخلية B5، ويمكنك رؤية أنه تم إنشاء صيغة في نفس الوقت. حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل للحصول على الكلمة الثانية والعشرين من السلاسل النصية الأخرى.

انقر لمعرفة المزيد عن هذه الميزة.

2.5 استخراج النص قبل أو بعد الظهور الثاني والعشرين للفاصل

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

2.5.1 استخراج النص قبل الظهور الثاني والعشرين للفاصل

يمكنك استخدام دالة LEFT مع دالتي SUBSTITUTE وFIND لاستخراج النص قبل الظهور الثاني والعشرين للفاصل من خلية في Excel.

الصيغة العامة

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

المعاملات

حدد خلية، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل للحصول على النتائج الأخرى في القائمة.

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

ملاحظة: في الصيغة، B5 هي الخلية التي تحتوي على السلسلة النصية التي تريد استخراج النص منها؛ “ ” هنا تمثل مسافة، والرقم2 يمثل الظهور الثاني للمسافة. يمكنك تغييرها حسب حاجتك.

2.5.2 استخراج النص بعد الظهور الثاني والعشرين للفاصل

لاستخراج النص بعد الظهور الثاني والعشرين للفاصل، يمكنك تطبيق دالة RIGHT مع دوال SUBSTITUTE وLEN وFIND.

الصيغة العامة

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n),1) +1)

المعاملات

الآن يمكنك تطبيق هذه الصيغة لاستخراج النص بعد الظهور الثاني للمسافة من كل خلية في النطاق B5:B10 كما يلي.

حدد خلية، أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل للحصول على النتائج الأخرى.

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 استخراج النص قبل أو بعد فاصل الأسطر

افترض أن لديك قائمة طلبات في العمود B وتريد استخراج جزء التاريخ فقط وجزء رقم المنتج من كل خلية. يمكنك استخدام الصيغ التالية في Excel لإنجاز ذلك.

2.6.1 استخراج النص قبل أول فاصل أسطر باستخدام الصيغة

كما ترى في الصورة أعلاه، يقع جزء التاريخ قبل أول فاصل أسطر داخل الخلية. سيعرض هذا القسم استخدام دالة LEFT مع دالة SEARCH لمساعدتك على استخراج النص قبل أول فاصل أسطر داخل الخلية.

الصيغة العامة

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

المعاملات

حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

بعد ذلك يمكنك رؤية النص قبل أول فاصل أسطر في كل خلية في النطاق B5:B8 تم استخراجه كما هو موضح في الصورة أدناه.

ملاحظة: في الصيغة، CHAR(10) يمثل فاصل الأسطر في نظام Windows.

2.6.2 استخراج النص بعد آخر فاصل أسطر باستخدام الصيغة

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

الصيغة العامة

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

المعاملات

حدد خلية فارغة، أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لتطبيق الصيغة على الخلايا الأخرى.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

بعد ذلك يتم استخراج جزء رقم المنتج من كل خلية في القائمة كما هو موضح في الصورة أعلاه.

ملاحظة: في الصيغة، CHAR(10) يمثل فاصل الأسطر في نظام Windows.

 2.7 استخراج النص قبل أو بعد كلمة

في الأجزاء السابقة، تعلمنا كيفية استخراج النص قبل أو بعد حرف أو فاصل. ماذا تفعل لاستخراج النص قبل أو بعد كلمة كاملة؟ سيقدم هذا القسم ثلاث طرق لمساعدتك على إنجاز هذه المهمة.

2.7.1 استخراج النص قبل كلمة معينة باستخدام الصيغة

الصيغة التالية تساعدك على استخراج النص قبل كلمة معينة داخل خلية في Excel.

الصيغة العامة

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

المعاملات

حدد خلية فارغة، أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.

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

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

ملاحظات:

2.7.2 استخراج النص بعد كلمة معينة باستخدام الصيغة

لاستخراج النص بعد كلمة معينة، يمكنك تطبيق الصيغة التالية لإنجاز ذلك.

الصيغة العامة

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

المعاملات

حدد خلية، أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

بعد ذلك يمكنك رؤية كل النصوص بعد كلمة "Excel" في كل خلية تم استخراجها كما هو موضح في الصورة أدناه.

ملاحظات:

2.7.3 استخراج النص قبل أو بعد كلمة معينة باستخدام أداة مذهلة

إذا شعرت أن استخدام الصيغ قد يسبب الكثير من الإزعاج، نوصي هنا بشدة باستخدام أداة "Extract Text" من Kutools لـ Excel. تساعدك هذه الميزة على أتمتة مهمة الاستخراج في Excel ببضع نقرات فقط.

1. انقر على "Kutools" > "Text" > "Extract Text" لتفعيل هذه الميزة.

2. في مربع الحوار "Extract Text"، قم بالإعدادات التالية.

2.1) تأكد من أنك في تبويب "استخراج حسب الموضع"؛

2.2) في مربع "النطاق"، انقر على زر لاختيار نطاق الخلايا الذي تريد استخراج النصوص منه؛

2.3) في قسم "الخيارات":

لاستخراج كل النصوص قبل كلمة، اختر زر الراديو "النص قبل" ثم أدخل الكلمة في مربع النص؛

لاستخراج كل النصوص بعد كلمة، اختر زر الراديو "النص بعد" ثم أدخل الكلمة في مربع النص.

2.4) انقر على زر "موافق". انظر الصورة:

ملاحظات: إذا كنت تريد إنشاء نتائج ديناميكية، حدد مربع "إدراج كصيغة". عندها ستتحدث النتائج تلقائيًا عند تغيير البيانات في النطاق.

3. بعد ذلك يظهر مربع حوار "Extract Text"، تحتاج إلى تحديد خلية لإخراج النتيجة ثم النقر على زر "موافق".

بعد ذلك يتم استخراج النصوص قبل أو بعد كلمة معينة داخل كل خلية في النطاق المحدد فورًا.

ملاحظة: هذه الميزة تفرق بين الحروف الكبيرة والصغيرة.

انقر لمعرفة المزيد عن هذه الميزة.

3. استخراج بين الأحرف/الكلمات

إذا كنت تريد استخراج نص بين أحرف أو كلمات معينة، جرب الطرق التالية.

3.1 استخراج النص بين حرفين

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

 3.1.1 استخراج النص بين حرفين متشابهين باستخدام الصيغة

كما هو موضح في الصورة أدناه، هناك قائمة بسلاسل نصية في العمود B، وتريد استخراج الجزء الرقمي بين الحرفين “/” من كل خلية في النطاق، يمكن للصيغة التالية مساعدتك.

حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل للحصول على نتائج الخلايا الأخرى في القائمة.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

بعد ذلك يتم استخراج النص بين الحرفين المتشابهين “/” من كل خلية في النطاق. انظر الصورة:

ملاحظة:

 3.1.2 استخراج النص بين حرفين مختلفين باستخدام الصيغة

بعد تعلم كيفية استخراج النص بين حرفين متشابهين داخل خلية، سنوضح هنا صيغة لاستخراج النص بين حرفين مختلفين. كما هو موضح في الصورة أدناه، لاستخراج عنوان البريد الإلكتروني فقط بين “<” و “>” من كل خلية في العمود B، يمكنك اتباع الخطوات التالية.

الصيغة العامة

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

المعاملات

حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

يمكنك رؤية أنه تم استخراج النص بين الحرفين المحددين فقط كما هو موضح في الصورة أعلاه.

3.1.3 استخراج النص بين حرفين باستخدام أداة مذهلة

هنا، نوصي بشدة باستخدام ميزة "استخراج النص بين النصوص المحددة" من Kutools لـ Excel لمساعدتك على استخراج النص بين حرفين متشابهين أو مختلفين داخل خلية في Excel بسهولة.

1. حدد خلية فارغة لإخراج النتيجة، ثم انقر على "Kutools" > "Formula Helper" > "Formula Helper".

2. في مربع الحوار "Formulas Helper"، قم بالإعدادات التالية.

3. بعد ذلك يتم استخراج النص بين “<” و “>” فقط داخل الخلية B5. في الوقت نفسه، تم إنشاء صيغة، يمكنك تحديد خلية النتيجة ثم سحب مقبض التعبئة التلقائية لأسفل لاستخراج النصوص من الخلايا الأخرى في نفس القائمة.

انقر لمعرفة المزيد عن هذه الميزة.

3.1.4 استخراج النص بين حرفين (بما في ذلك الحرفين) حسب القاعدة

إذا كنت تريد الاحتفاظ بالحرفين بعد الاستخراج، جرب تطبيق قاعدة في ميزة Extract Text من Kutools لـ Excel.

1. انقر على "Kutools" > "Text" > "Extract Text".

2. في مربع الحوار "Extract Text"، قم بالإعدادات التالية.

2.1) انقر على تبويب "استخراج حسب القاعدة"؛

2.2) في قسم "النطاق"، انقر على زر لاختيار نطاق الخلايا الذي تريد استخراج النص منه بين الحروف؛

2.3) في مربع "النص"، أدخل "<*>"؛

نصيحة: "<" و ">" هما الحرفان اللذان تريد استخراج النص بينهما، و * هو رمز بدل يمثل أي عدد من الأحرف. يمكنك إدخال الشرط حسب حاجتك.

2.4) انقر على زر "إضافة" لإضافة الشرط إلى مربع قائمة "محتوى القاعدة"؛

2.5) انقر على زر "موافق". انظر الصورة:

3. يظهر مربع حوار آخر "Extract Text". يرجى تحديد خلية لإخراج النتيجة، ثم انقر على زر "موافق".

بعد ذلك، يتم استخراج النص بين الحرفين المحددين (بما في ذلك الحرفين) من كل خلية في النطاق المحدد دفعة واحدة.

3.2 استخراج النص بين كلمتين

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

3.2.1 استخراج النص بين كلمتين باستخدام الصيغة

يمكنك استخدام صيغة تعتمد على دالتي MID وSEARCH لاستخراج جميع السلاسل النصية بين كلمتين داخل خلية.

الصيغة العامة

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

المعاملات

حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

ملاحظة: في الصيغة، الرقم3 يمثل طول كلمة "KTE" بالأحرف؛ الرقم4 يمثل طول كلمة "KTE" زائد1.

يمكنك رؤية أنه تم استخراج جميع السلاسل النصية بين الكلمتين المحددتين من كل خلية في العمود B.

3.2.2 استخراج النص بين كلمتين باستخدام أداة مذهلة

بالنسبة للعديد من مستخدمي Excel، قد يكون من الصعب تذكر الصيغ والتعامل معها. هنا، باستخدام ميزة "استخراج النص بين النصوص المحددة" من Kutools لـ Excel، يمكنك بسهولة استخراج النص بين كلمتين ببضع نقرات فقط.

1. حدد خلية لإخراج النتيجة، ثم انقر على "Kutools" > "Formula Helper" > "Formula Helper."

2. في مربع الحوار "Formula Helper"، تحتاج إلى ضبط الإعدادات كما يلي.

3. بعد ذلك يتم استخراج جميع السلاسل النصية بين كلمتي "KTE" و"feature" داخل الخلية B5. في الوقت نفسه، تم إنشاء صيغة، يمكنك تحديد خلية النتيجة ثم سحب مقبض التعبئة التلقائية لأسفل لاستخراج النصوص من الخلايا الأخرى في نفس القائمة.

استخراج الأرقام حسب الموضع

بالنسبة لقائمة من السلاسل النصية المكونة من أحرف وأرقام، قد توجد ثلاث حالات:

1. الرقم في بداية النص؛
2. الرقم في نهاية النص؛
3. الرقم يمكن أن يكون في أي مكان داخل النص.

في هذا القسم، سنوفر طرقًا مختلفة يمكن استخدامها لاستخراج الأرقام في كل حالة من الحالات المذكورة أعلاه.

1 استخراج الرقم من يسار السلسلة النصية

سيعرض هذا الجزء صيغة لمساعدتك على استخراج الأرقام التي تظهر قبل النص في الخلية فقط.

الصيغة العامة

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)),1) *1),0) -1)

المعاملات

ملاحظة:

حدد خلية فارغة، أدخل الصيغة أدناه واضغط على مفاتيح "Ctrl" + "Shift" + "Enter" أو مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل للحصول على أرقام الخلايا الأخرى.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

ملاحظات:

2 استخراج الرقم من يمين السلسلة النصية

كما هو موضح في الصورة أدناه، لاستخراج الأرقام التي تظهر بعد النص في الخلية فقط، جرب الصيغة التالية.

الصيغة العامة

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))),1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))),0)))

المعاملات

ملاحظة:

حدد خلية فارغة، أدخل الصيغة أدناه واضغط على مفاتيح "Ctrl" + "Shift" + "Enter" أو مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل للحصول على أرقام الخلايا الأخرى.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

ملاحظات:

3. استخراج جميع الأرقام من أي موضع في سلسلة نصية

الطرق أعلاه تساعد على استخراج الأرقام فقط من يسار أو يمين السلسلة النصية. إذا كنت تريد استخراج جميع الأرقام من أي مكان في السلسلة النصية، نوفر هنا ثلاث طرق لإنجاز ذلك.

3.1 استخراج جميع الأرقام من أي مكان في السلسلة باستخدام الصيغة

يمكنك تطبيق الصيغة التالية لاستخراج جميع الأرقام من أي مكان في السلسلة النصية في Excel.

1. حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" لاستخراج جميع الأرقام من الخلية B5.

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. حدد خلية النتيجة ثم اسحب مقبض التعبئة التلقائية لأسفل لاستخراج جميع الأرقام من الخلايا الأخرى.

3.2 استخراج جميع الأرقام من أي مكان في السلسلة باستخدام VBA

الصيغة أعلاه طويلة ومعقدة بالنسبة للعديد من مستخدمي Excel. في الواقع، يمكنك تشغيل كود VBA لأتمتة المهمة في Excel. يمكنك القيام بما يلي.

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

2. في نافذة "Microsoft Visual Basic for Applications" المفتوحة، انقر على "Insert" > "Module". ثم انسخ كود VBA أدناه إلى نافذة الكود الخاصة بالموديل.

كود VBA: استخراج جميع الأرقام من أي مكان في السلسلة النصية

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. اضغط على مفتاح "F5" لتشغيل الكود. في مربع الحوار "KutoolsforExcel" المفتوح، حدد نطاق الخلايا الذي تريد استخراج جميع الأرقام منه من كل خلية، ثم انقر على زر "موافق".

4. بعد ذلك يظهر مربع حوار آخر "KutoolsforExcel". في هذا المربع، حدد خلية الوجهة وانقر على "موافق".

بعد ذلك يتم استخراج جميع الأرقام من كل خلية في النطاق المحدد دفعة واحدة.

4. استخراج الأرقام بعد نص محدد

كما هو موضح في الصورة أدناه، لاستخراج أي أرقام بعد النص المحدد "No."، يوفر هذا القسم طريقتين لمساعدتك على إنجاز ذلك.

4.1 استخراج الأرقام بعد نص محدد باستخدام الصيغة

يمكنك تطبيق الصيغة التالية لاستخراج الأرقام بعد نص محدد في خلية في Excel.

الصيغة العامة:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

المعاملات

حدد خلية فارغة، ثم انسخ أو أدخل الصيغة أدناه واضغط على مفتاح "Enter" للحصول على النتيجة. ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

ملاحظات:

4.2 استخراج الأرقام بعد نص محدد باستخدام دالة معرفة من قبل المستخدم

يمكن أن تساعدك الدالة المعرفة من قبل المستخدم التالية أيضًا على استخراج الأرقام بعد نص محدد في خلية. يرجى القيام بما يلي.

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

2. في نافذة "Microsoft Visual Basic for Applications"، انقر على "Insert" > "Module"، ثم انسخ كود VBA أدناه إلى نافذة الكود الخاصة بالموديل.

كود VBA: استخراج الأرقام بعد نص محدد في خلية

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3. اضغط على مفاتيح "Alt" + "Q" لإغلاق نافذة "Microsoft Visual Basic for Applications".

4. حدد خلية، أدخل الصيغة أدناه واضغط على مفتاح "Enter". ثم حدد خلية النتيجة واسحب مقبض التعبئة التلقائية لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.

=GetNumberAfterTheChar(B5,"No. ")

ملاحظات:

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