برنامج Excel التعليمي - استخراج نص أو رقم حسب موضع معين
في كثير من الحالات ، قد تحتاج فقط إلى استخراج المحتوى المفيد من جملة أو سلسلة نصية في خلية ، مثل استخراج المقاطعة من العنوان ، واستخراج عنوان البريد الإلكتروني من جملة ، واستخراج رقم فاتورة البريد السريع من محادثة ، وما إلى ذلك وهلم جرا. يضيق هذا البرنامج التعليمي الاستخراج إلى موضع معين في خلية ويجمع طرقًا مختلفة للمساعدة في استخراج نص أو أرقام من خلية حسب موضع معين في Excel.
جدول المحتويات: [ يخفي ]
استخراج النص حسب الموضع
يجمع هذا القسم المواقف الشائعة حيث يمكن استخراج نص من خلية ويوفر طرقًا مقابلة للتعامل معها خطوة بخطوة. يمكنك التصفح لمزيد من التفاصيل.
1. استخراج عدد الأحرف من اليسار أو اليمين
لاستخراج عدد الأحرف من الجانب الأيسر أو الأيمن من السلسلة ، يمكنك تجربة إحدى الطرق أدناه.
1.1 استخراج أول أو آخر حرف N مع الصيغ
لنفترض أن لديك قائمة سلسلة نصية في العمود B كما هو موضح في لقطة الشاشة أدناه ، لاستخراج أول حرفين وآخر حرفين من كل سلسلة ، يمكنك تطبيق الصيغ التالية.
استخرج أول حرف N من سلسلة نصية
يمكن أن تساعد وظيفة LEFT في استخراج الأحرف N الأولى بسهولة من سلسلة نصية في Excel.
صيغة عامة
=LEFT(text_string,[num_chars])
الحجج
يمكنك الآن تطبيق هذه الصيغة لاستخراج أول حرفين من الخلايا في العمود B.
1. حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة الأولى. حدد خلية النتيجة واسحب مقبض الملء التلقائي لأسفل لتطبيق الصيغة على الخلايا الأخرى.
=LEFT(B5,2)
الآن تم استخراج أول حرفين في كل خلية من النطاق B2: B5.
استخرج الأحرف N الأخيرة من سلسلة نصية
هنا نطبق الدالة RIGHT لاستخراج آخر حرف N من سلسلة نصية في Excel.
صيغة عامة
=RIGHT(text_string,[num_chars])
الحجج
حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. ثم حدد خلية النتيجة هذه واسحب مقبض الملء التلقائي لأسفل للحصول على نتائج أخرى.
=RIGHT(B5,2)
1.2 استخرج أول أو آخر حرف N بأداة مذهلة
على الرغم من أن الصيغ أعلاه بسيطة ، لاستخراج الأحرف n الأولى أو الأخيرة من قائمة طويلة من السلسلة النصية ، ما زلت بحاجة إلى سحب مقبض الملء التلقائي من أعلى إلى أسفل والذي قد يستغرق وقتًا طويلاً. هنا توصي كوتولس ل إكسيلالصورة استخراج النص أداة مساعدة للمساعدة في استخراج الأحرف الأولى أو الأخيرة من قائمة السلسلة النصية بشكل مجمّع.
1. حدد قائمة السلاسل النصية التي تريد استخراج النص منها مسبقًا وانقر فوق كوتولس > نص > استخراج النص.
2. في ظهرت استخراج النص مربع الحوار ، تحتاج إلى تكوين على النحو التالي.
ملاحظات: لجعل النتيجة ديناميكية عندما تتغير السلسلة النصية ، يمكنك التحقق من أدخل كصيغة مربع.
3. في القادم ظهرت استخراج النص في مربع الحوار ، اختر خلية لإخراج الأحرف المستخرجة ثم انقر فوق OK.
ثم يتم استخراج الأحرف الأولى أو الأخيرة N المحددة من الخلايا المحددة بكميات كبيرة.
انقر لمعرفة المزيد عن هذه الميزة.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
2. استخراج النص قبل أو بعد حرف / كلمة معينة
لاستخراج نص قبل أو بعد حرف أو كلمة معينة ، ستلبي السيناريوهات المختلفة في هذا القسم احتياجاتك.
2.1 استخراج النص قبل أو بعد المحدد الأول (حرف)
كما هو موضح في لقطة الشاشة أدناه ، لاستخراج النص قبل أو بعد المحدد الأول من كل خلية في النطاق B4: B10 ، يمكنك تطبيق إحدى الطرق التالية.
2.1.1 استخراج النص قبل المحدد الأول بالصيغة
يمكن أن يساعد تطبيق صيغة بناءً على الدالتين LEFT و FIND في استخراج النص قبل المحدد الأول من خلية. يمكنك اتباع الخطوات أدناه لإنجازها.
صيغة عامة
=LEFT(text_string,FIND("delimiter",text_string,1)-1)
الحجج
حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه فيها واضغط على أدخل مفتاح للحصول على النتيجة الأولى. حدد خلية النتيجة الأولى واسحب مقبض الملء التلقائي لأسفل للحصول على النصوص قبل المحدد الأول للخلايا الأخرى.
=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 استخراج النص قبل أو بعد المحدد الأول بأداة رائعة
هنا يوصي بشدة ب استخراج النص فائدة كوتولس ل إكسيل. باستخدام هذه الميزة ، يمكنك بسهولة استخراج النصوص قبل أو بعد المحدد الأول من نطاق من الخلايا بشكل مجمّع.
1. حدد نطاق الخلايا حيث تريد استخراج النص ، ثم انقر فوق كوتولس > نص > استخراج النص.
2. في ال استخراج النص مربع الحوار ، تحتاج إلى تكوين على النحو التالي.
ملحوظة: لجعل النتيجة ديناميكية عند تغيير سلسلة النص ، يمكنك تحديد المربع إدراج كصيغة.
3. ثم آخر استخراج النص ينبثق مربع الحوار ، اختر خلية لإخراج النتائج وانقر حسنا.
ثم يتم استخراج النصوص قبل أو بعد المحدد الأول من الخلايا المحددة مرة واحدة.
لمعرفة المزيد عن هذه الميزة ، يرجى زيارة: استخرج نصًا معينًا بسرعة من الخلايا في Excel.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
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)
الحجج
حدد خلية ، أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه واسحب مقبض الملء التلقائي لأسفل لاستخراج النصوص من سلاسل نصية أخرى في نفس العمود.
=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","")))))
الحجج
حدد خلية ، أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه واسحب مقبض الملء التلقائي لأسفل لاستخراج النصوص من سلاسل نصية أخرى في نفس العمود.
=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))
الحجج
حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه فيها واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه واسحب مقبض الملء التلقائي لأسفل للحصول على نتائج أخرى.
=MID(B5,3+1,LEN(B5))
2.4 استخراج الكلمة n من سلسلة نصية
لنفترض أن لديك قائمة بسلسلة نصية كما هو موضح في لقطة الشاشة أدناه ، وتريد استخراج الكلمة التاسعة فقط من السلسلة النصية ، فإن هذا القسم يوفر لك ثلاث طرق لإنجازها.
2.4.1 استخراج الكلمة n مع الصيغة
يمكنك دمج وظائف TRIM و MID و SUBSTITUTE و REPT و LEN لاستخراج الكلمة n من سلسلة نصية في خلية.
صيغة عامة
=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))
الحجج
في هذه الحالة ، النطاق B5: B10 يحتوي على سلاسل نصية ، D5: D10 يحتوي على الأرقام التي تمثل الكلمة التاسعة ، دعنا نطبق هذه الصيغة لاستخراج الكلمة التاسعة من سلسلة نصية.
حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه فيها واضغط على أدخل مفتاح للحصول على النتيجة الأولى. حدد خلية النتيجة هذه واسحب مقبض الملء التلقائي لأسفل للحصول على الكلمة رقم n للخلايا الأخرى.
=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. اضغط على قديم + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
2. في ال ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة، ثم انسخ VBA أدناه في نافذة Code.
رمز فبا: استخراج الكلمة nth من سلسلة نصية في خلية
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. اضغط على قديم + Q مفاتيح لإغلاق ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
4. ارجع إلى ورقة العمل التي تحتوي على السلاسل النصية التي تريد استخراج الكلمة التاسعة منها. حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه فيها واضغط على أدخل مفتاح للحصول على الكلمة nth.
=FindWord(B5,D5)
Or
=FindWord(B5,2)
ملحوظة: في الصيغة ، D5 هي الخلية التي تحتوي على رقم يمثل الكلمة التاسعة. بدلاً من ذلك ، يمكنك استبدال مرجع الخلية مباشرةً برقم.
5. حدد خلية النتيجة واسحب مقبض الملء التلقائي لأسفل لاستخراج الكلمة n من السلاسل النصية للخلايا الأخرى.
2.4.3 استخرج الكلمة التاسعة بأداة رائعة
إذا كنت لا ترغب في تطبيق صيغة يدويًا أو وظيفة معرّفة من قبل المستخدم مذكورة أعلاه ، فننصحك هنا باستخدام Kutools for Excel استخراج الكلمة nth في الخلية خدمة. باستخدام هذه الميزة ، يمكنك بسهولة استخراج الكلمة التاسعة من سلسلة نصية في خلية ببضع نقرات فقط.
1. حدد خلية لوضع النتيجة ، وانقر فوق كوتولس > مساعد الصيغة > نص > مقتطف الكلمة nth في الخلية. انظر لقطة الشاشة:
2. في ال مساعد الصيغ مربع الحوار ، تحتاج إلى تكوين على النحو التالي.
3. ثم يتم استخراج الكلمة nth (الثانية) من السلسلة النصية في الخلية B5 ، ويمكنك رؤية صيغة يتم إنشاؤها في نفس الوقت. حدد خلية النتيجة هذه واسحب مقبض الملء التلقائي لأسفل للحصول على الكلمة التاسعة من سلاسل نصية أخرى.
انقر لمعرفة المزيد عن هذه الميزة.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
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)
الحجج
حدد خلية ، وانسخ أو أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه واسحبها مقبض الملء التلقائي لأسفل للحصول على نتائج أخرى في القائمة.
=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 على النحو التالي.
حدد خلية ، أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه واسحبها مقبض الملء التلقائي لأسفل للحصول على نتائج أخرى.
=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)
الحجج
حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه فيها واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه ثم اسحب مقبض الملء التلقائي لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.
=LEFT(B5, SEARCH(CHAR(10), B5)-1)
ثم يمكنك رؤية النص قبل فاصل الأسطر الأول في كل خلية في النطاق B5: يتم استخراج B8 كما هو موضح في لقطة الشاشة أدناه.
ملحوظة: في الصيغة ، يمثل CHAR (10) فاصل أسطر في Windows.
2.6.2 استخرج النص بعد فاصل السطر الأخير بالصيغة
في الخطوة السابقة ، تحدثنا عن كيفية استخراج النص قبل فاصل السطر الأول داخل الخلية. ويرشدك هذا الجزء إلى كيفية استخراج النص بعد فاصل السطر الأخير داخل خلية باستخدام صيغة مختلفة.
صيغة عامة
=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))
الحجج
حدد خلية فارغة ، أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه ثم اسحب مقبض الملء التلقائي لأسفل لتطبيق الصيغة على الخلايا الأخرى.
=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)
الحجج
حدد خلية فارغة ، أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه ثم اسحب مقبض الملء التلقائي لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.
في هذا المثال ، سنقوم باستخراج جميع النصوص قبل كلمة "Excel" ، لذلك نكتب الكلمة مباشرةً في الصيغة ونضعها بين علامتي اقتباس. أو يمكنك الإشارة إلى خلية تحتوي على كلمة "Excel".
=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)
الملاحظات:
2.7.2 استخراج نص بعد كلمة معينة مع صيغة
لاستخراج نص بعد كلمة معينة ، يمكنك تطبيق الصيغة التالية لإنجازه.
صيغة عامة
=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))
الحجج
حدد خلية ، أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة ثم اسحب مقبض الملء التلقائي لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.
=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))
ثم يمكنك رؤية جميع النصوص بعد استخراج كلمة "Excel" في كل خلية كما هو موضح في لقطة الشاشة أدناه.
الملاحظات:
2.7.3 استخراج النص قبل أو بعد كلمة معينة بأداة رائعة
إذا شعرت أن استخدام الصيغة قد يسبب الكثير من الإزعاج ، فننصحك بشدة باستخدام استخراج النص فائدة كوتولس ل إكسيل. تساعد هذه الميزة في أتمتة مهمة الاستخراج في Excel ببضع نقرات فقط.
1. انقر كوتولس > نص > استخراج النص لتمكين هذه الميزة.
2. في ال استخراج النص في مربع الحوار ، قم بالإعدادات التالية.
الملاحظات: إذا كنت تريد إنشاء نتائج ديناميكية ، فتحقق من أدخل كصيغة صندوق. ثم يتم تحديث النتائج تلقائيًا عندما تتغير البيانات الموجودة في النطاق.
3. ثم أ استخراج النص ينبثق مربع الحوار ، تحتاج إلى تحديد خلية لإخراج النتيجة ثم النقر فوق OK .
ثم يتم استخراج النصوص قبل أو بعد كلمة معينة داخل كل خلية في النطاق المحدد على الفور.
ملحوظة: هذه الميزة حساسة لحالة الأحرف.
انقر لمعرفة المزيد عن هذه الميزة.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
3. استخراج بين الأحرف / الكلمات
إذا كنت تريد استخراج نص بين أحرف أو كلمات معينة ، فجرّب الطرق التالية.
لاستخراج نص بين حرفين ، يمكن أن يكونا متطابقين أو حرفين مختلفين. يقدم هذا القسم عدة طرق ، ويمكنك اختيار إحداها حسب احتياجاتك.
3.1.1 استخراج نص بين حرفين متشابهين باستخدام الصيغة
كما هو موضح في لقطة الشاشة أدناه ، هناك قائمة من السلاسل النصية في العمود B ، وتريد استخراج جزء الرقم بين الأحرف "/" من كل خلية في النطاق ، يمكن للصيغة التالية أن تفيدك.
حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة ثم اسحب مقبض الملء التلقائي لأسفل للحصول على نتائج الخلايا الأخرى في القائمة.
=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))
الحجج
حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه ثم اسحب مقبض الملء التلقائي لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.
=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))
يمكنك أن ترى أنه يتم استخراج النص الموجود بين الأحرف المحددة فقط كما هو موضح في لقطة الشاشة أعلاه.
3.1.3 استخراج نص بين حرفين بأداة مذهلة
هنا يوصي بشدة ب استخراج السلاسل بين النص المحدد سمة من سمات كوتولس ل إكسيل لمساعدتك في استخراج النص بسهولة بين حرفين متشابهين أو مختلفين داخل خلية في Excel.
1. حدد خلية فارغة لإخراج النتيجة ، ثم انقر فوق "نعم" كوتولس > مساعد الصيغة > مساعد الصيغة.
2. في ال مساعد الصيغ في مربع الحوار ، قم بالإعدادات التالية.
3. ثم يتم استخراج النص الموجود بين "<" و ">" داخل الخلية B5 فقط. في غضون ذلك ، تم إنشاء صيغة ، يمكنك تحديد خلية النتيجة هذه ثم سحب مقبض الملء التلقائي لأسفل لاستخراج النصوص من الخلايا الأخرى في نفس القائمة.
انقر لمعرفة المزيد عن هذه الميزة.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
3.1.4 استخلاص النص بين حرفين (بما في ذلك الأحرف) بواسطة القاعدة
إذا كنت تريد الاحتفاظ بالحرفين بعد الاستخراج ، فحاول تطبيق قاعدة في ميزة استخراج النص في Kutools for Excel.
1. انقر كوتولس > نص > استخراج النص.
2. في ال استخراج النص في مربع الحوار ، قم بالإعدادات التالية.
3. آخر استخراج النص ينبثق مربع الحوار ، يرجى تحديد خلية لإخراج النتيجة ، ثم انقر فوق OK .
ثم يتم استخراج النص الموجود بين الأحرف المحددة (بما في ذلك الأحرف) من كل خلية في النطاق المحدد بشكل مجمّع.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
بالإضافة إلى استخراج نص بين حرفين ، قد تحتاج أيضًا إلى استخراج نص بين كلمتين. على سبيل المثال ، استخرج جميع السلاسل النصية بين كلمتين "KTE" و "feature" من كل خلية في العمود B كما هو موضح في لقطة الشاشة أدناه. يمكنك تجربة إحدى الطرق التالية لإنجازها.
3.2.1 استخراج نص بين كلمتين مع صيغة
يمكنك استخدام صيغة تستند إلى دالة MID ووظيفة SEARCH لاستخراج كل السلاسل النصية بين كلمتين داخل خلية.
صيغة عامة
=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)
الحجج
حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه ثم اسحب مقبض الملء التلقائي لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.
=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)
ملاحظات: في الصيغة ، الرقم 3 يمثل طول الحرف لكلمة "KTE" ؛ الرقم 4 يمثل طول الحرف لكلمة "KTE" زائد 1.
يمكنك رؤية جميع السلاسل النصية بين الكلمتين المحددتين مستخرجة من كل خلية في العمود B.
3.2.2 استخراج نص بين كلمتين بأداة مذهلة
بالنسبة للعديد من مستخدمي Excel ، قد يكون من الصعب تذكر الصيغ والتعامل معها. تطورات أخرى استخراج السلاسل بين النص المحدد سمة من سمات كوتولس ل إكسيل، يمكنك بسهولة استخراج نص بين كلمتين ببضع نقرات.
1. حدد خلية لإخراج النتيجة ، ثم انقر فوق "نعم" كوتولس > مساعد الصيغة > مساعد الصيغة.
2. في ال مساعد الصيغة مربع الحوار ، تحتاج إلى تكوين على النحو التالي.
3. ثم يتم استخراج كل السلاسل النصية بين الكلمتين "KTE" و "الميزة" داخل الخلية B5. في غضون ذلك ، تم إنشاء صيغة ، يمكنك تحديد خلية النتيجة هذه ثم سحب مقبض الملء التلقائي لأسفل لاستخراج النصوص من الخلايا الأخرى في نفس القائمة.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
استخراج الأرقام حسب الموضع
للحصول على قائمة بالسلاسل الأبجدية الرقمية ، قد تكون هناك ثلاث حالات:
- الرقم في بداية النص;
- الرقم في نهاية النص;
- يمكن أن يكون الرقم في أي مكان في النص.
في هذا القسم سوف نقدم طرق مختلفة يمكن استخدامها لاستخراج الأرقام في كل حالة المذكورة أعلاه.
1 استخراج الرقم من يسار السلسلة
سيقدم هذا الجزء صيغة لمساعدتك في استخراج الأرقام التي تظهر قبل النص في الخلية فقط.
صيغة عامة
=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)
الحجج
ملحوظة:
حدد خلية فارغة ، أدخل الصيغة أدناه واضغط CTRL + تغير + أدخل or أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه ثم اسحب مقبض الملء التلقائي لأسفل للحصول على أرقام الخلايا الأخرى.
=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 + تغير + أدخل or أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه ثم اسحب مقبض الملء التلقائي لأسفل للحصول على أرقام الخلايا الأخرى.
=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. حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على جميع الأرقام من الخلية 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. اضغط على قديم + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
2. في الافتتاح ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة. ثم انسخ 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 مفتاح لتشغيل الكود. في الافتتاح كوتولسفورإكسيل في مربع الحوار ، حدد نطاق الخلايا حيث تريد استخراج كافة الأرقام من كل خلية ، ثم انقر فوق OK .
4. ثم آخر كوتولسفورإكسيل ينبثق مربع الحوار. في مربع الحوار هذا ، حدد خلية وجهة وانقر حسنا.
ثم يتم استخراج جميع الأرقام من كل خلية في النطاق المحدد بشكل مجمّع.
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}))
الحجج
حدد خلية فارغة ، وانسخ أو أدخل الصيغة أدناه واضغط على أدخل مفتاح للحصول على النتيجة. حدد خلية النتيجة هذه ثم اسحب مقبض الملء التلقائي لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.
=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. اضغط على قديم + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
2. في ال ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة، ثم انسخ رمز 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. اضغط على قديم + Q مفاتيح لإغلاق ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
4. حدد خلية ، أدخل الصيغة أدناه واضغط على أدخل مفتاح. حدد خلية النتيجة هذه ثم اسحب مقبض الملء التلقائي لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى.
=GetNumberAfterTheChar(B5,"No. ")
الملاحظات:
مقالات ذات صلة:
برنامج Excel التعليمي: انقسام النص والرقم وخلايا التاريخ (منفصلة إلى أعمدة متعددة)
ينقسم هذا البرنامج التعليمي إلى ثلاثة أجزاء: تقسيم الخلايا النصية وخلايا الأرقام المقسمة وخلايا التاريخ المنقسمة. يقدم كل جزء أمثلة مختلفة لمساعدتك في معرفة كيفية التعامل مع وظيفة التقسيم عند مواجهة نفس المشكلة.
انقر لمعرفة المزيد ...
يقوم Excel بإضافة نص ورقم إلى الموضع المحدد للخلية
في Excel ، تعد إضافة نصوص أو أرقام إلى الخلايا مهمة شائعة جدًا. مثل إضافة مسافة بين الأسماء ، وإضافة بادئة أو لاحقة إلى الخلايا ، وإضافة شرطات إلى الأرقام الاجتماعية. هنا في هذا البرنامج التعليمي ، يسرد تقريبًا جميع سيناريوهات الإضافة في Excel ويوفر لك الطرق المقابلة.
انقر لمعرفة المزيد ...
يقوم Excel بإزالة الأحرف والكلمات والأرقام من السلاسل النصية
لنفترض أن لديك قائمة طويلة من السلاسل النصية التي تحتوي على أحرف أو أرقام أو رموز أخرى محددة. في حالة معينة ، قد تحتاج إلى إزالة بعض الأحرف بناءً على الموضع ، مثل من اليمين أو اليسار أو الوسط من السلاسل النصية ، أو حذف بعض الأحرف غير المرغوب فيها والأرقام من قائمة السلاسل. سيؤدي العثور على الحلول واحدًا تلو الآخر إلى حدوث صداع ، حيث يجمع هذا البرنامج التعليمي جميع أنواع الطرق لإزالة الأحرف أو الكلمات أو الأرقام في Excel.
انقر لمعرفة المزيد ...
أفضل أدوات إنتاجية المكتب
عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!