استخراج الأرقام من السلاسل في Excel (باستخدام الصيغ أو VBA)

لا توجد وظيفة مضمنة في Excel لاستخراج الأرقام من سلسلة في خلية (والعكس صحيح - إزالة الجزء الرقمي واستخراج جزء النص من سلسلة أبجدية رقمية).

ومع ذلك ، يمكن القيام بذلك باستخدام وظيفة Excel أو بعض تعليمات VBA البرمجية البسيطة.

دعني أخبرك أولاً بما أتحدث عنه.

افترض أن لديك مجموعة بيانات مثل أدناه وتريد استخراج الأرقام من السلاسل (مثل أدناه):

تعتمد الطريقة التي تختارها أيضًا على إصدار Excel الذي تستخدمه:

• بالنسبة لإصدارات Excel قبل 2016 ، تحتاج إلى استخدام صيغة أطول قليلاً
• بالنسبة لبرنامج Excel 2016 ، يمكنك استخدام وظيفة TEXTJOIN المقدمة حديثًا
• طريقة VBA متاحة لجميع إصدارات Excel

استخراج الأرقام من السلاسل في Excel (صيغ Excel 2016)

تعمل هذه الصيغة فقط في Excel 2016 لأنها تستخدم وظيفة TEXTJOIN التي تم تقديمها حديثًا.

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

لاحظ أن صيغة TEXTJOIN الموضحة في هذا القسم تجمع كل الأحرف الرقمية معًا.على سبيل المثال ، إذا كان النص "10 تذاكر هي 200 دولار" ، تكون النتيجة 10200.

افترض أن لديك مجموعة بيانات مثل التالية ، وتريد استخراج الأرقام من سلاسل في كل خلية:

ستمنحك الصيغة أدناه الجزء الرقمي من سلسلة في Excel.

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

هذه صيغة صفيف ، لذا عليك استخدام " التحكم + التحول + أدخل "بدلاً من استخدام Enter.

ستعيد هذه الصيغة فارغة (سلسلة فارغة) إذا لم تكن هناك أرقام في السلسلة النصية.

كيف تعمل هذه الصيغة؟

دعني أقسم هذه الصيغة وأحاول شرح كيفية عملها:

• ROW (غير مباشر ("1:" & LEN (A2))) - سيعطي هذا الجزء من الصيغة سلسلة من الأرقام تبدأ من واحد.ترجع الدالة LEN في الصيغة العدد الإجمالي للأحرف في السلسلة.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
• (MID (A2، ROW (INDIRECT (“1:” & LEN (A2)))، 1) * 1) - سيرجع هذا الجزء من الصيغة صفيفًا من #VALUEs!سلسلة من الأخطاء أو الأرقام.تصبح كل أحرف النص في السلسلة #VALUE!تظل الأخطاء وجميع القيم كما هي.يحدث هذا عندما نضرب دالة MID في 1.
• IFERROR ((MID (A2، ROW (INDIRECT ("1:" & LEN (A2))))، 1) * 1)، "") - عند استخدام وظيفة IFERROR ، تتم إزالة جميع #VALUEs!خطأ ، ستبقى الأرقام فقط.يبدو إخراج هذا الجزء كالتالي - {"" ؛ "" ؛ "" ؛ "" ؛ "" ؛ "" ؛ "" ؛ "" ؛ "" ؛ "" ؛ "" ؛ "" ؛ "" ؛ " "؛" "؛" "؛ 1 ؛ 0 ؛ 0}
• = TEXTJOIN (""، TRUE، IFERROR ((MID (A2، ROW (INDIRECT ("1:" & LEN (A2)))، 1) * 1)، "")) - الآن ببساطة تجمع دالة TEXTJOIN أحرف السلسلة. (أرقام فقط) ويتجاهل السلاسل الفارغة.

نصيحة محترف:إذا كنت تريد التحقق من ناتج صيغة جزئية ، فحدد الخلية ، واضغط على F2 للدخول في وضع التحرير ، وحدد جزء الصيغة الذي تريد إخراجه ، واضغط على F9.سترى النتائج على الفور.ثم تذكر الضغط على Control + Z أو الضغط على Escape.لا تضغط دخول.

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

=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))

تغيير بسيط في هذه الصيغة هو أنه يتم استخدام الدالة IF للتحقق من الأخطاء في الصفيف الذي نحصل عليه من دالة MID.إذا كان هناك خطأ ، احتفظ بالقيمة ، وإلا استبدلها بفارغ.

ثم استخدم TEXTJOIN لدمج كل أحرف النص.

注意: بالرغم من أن هذه الصيغة صالحة ، إلا أنها تستخدم دالة متغيرة (الدالة INDIRECT).هذا يعني أنه إذا كنت تستخدمه مع مجموعة بيانات ضخمة ، فقد يستغرق الأمر بعض الوقت لإعطائك النتائج.من الأفضل إنشاء نسخة احتياطية قبل استخدام هذه الصيغة في Excel.

استخراج الأرقام من السلاسل في Excel (لبرنامج Excel 2013/2010/2007)

إذا كان لديك Excel 2013. 2010. أو 2007 ، فلا يمكنك استخدام صيغ TEXTJOIN ، لذلك عليك استخدام الصيغ المعقدة للقيام بذلك.

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

الصيغة أدناه ستفعل هذا:

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")

ستعيد هذه الصيغة فارغة (سلسلة فارغة) إذا لم تكن هناك أرقام في السلسلة النصية.

على الرغم من أن هذه صيغة صفيف ، أنتلا حاجةاستخدم "Control-Shift-Enter" لاستخدامه.يعمل إدخال بسيط لهذه الصيغة.

الفضل في هذه الصيغة يذهب إلى المذهلمنتدى السيد اكسل.

مرة أخرى ، ستقوم هذه الصيغة باستخراج جميع الأرقام في السلسلة بغض النظر عن الموضع.على سبيل المثال ، إذا كان النص "10 تذاكر هي 200 دولار" ، تكون النتيجة 10200.

注意: بالرغم من أن هذه الصيغة صالحة ، إلا أنها تستخدم دالة متغيرة (الدالة INDIRECT).هذا يعني أنه إذا كنت تستخدمه مع مجموعة بيانات ضخمة ، فقد يستغرق الأمر بعض الوقت لإعطائك النتائج.من الأفضل إنشاء نسخة احتياطية قبل استخدام هذه الصيغة في Excel.

افصل بين النصوص والأرقام في Excel باستخدام VBA

إذا كنت تحتاج بشكل متكرر إلى فصل النص والأرقام (أو استخراج الأرقام من النص) ، يمكنك أيضًا استخدام طريقة VBA.

كل ما عليك فعله هو إنشاء دالة مخصصة معرّفة من قبل المستخدم (UDF) في Excel باستخدام رمز VBA بسيط ، ثم استخدام صيغة VBA بدلاً من صيغة طويلة ومعقدة.

دعني أوضح لك كيفية إنشاء صيغتين في VBA - واحدة لاستخراج الأرقام والأخرى لاستخراج النص من السلاسل.

استخراج الأرقام من السلاسل في Excel (باستخدام VBA)

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

فيما يلي رمز VBA الذي سنستخدمه لإنشاء هذه الوظيفة المخصصة:

الدالة GetNumeric (CellRef as String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef، i، 1)) ثم النتيجة = النتيجة والمتوسطة (CellRef، i، 1) التالي i GetNumeric = نتيجة وظيفة النهاية

فيما يلي خطوات إنشاء هذه الوظيفة ثم استخدامها في ورقة عمل:

ستتمكن الآن من استخدام وظيفة GetText في ورقة العمل.نظرًا لأننا قمنا بالفعل بكل المهام الثقيلة في الكود نفسه ، فكل ما عليك فعله هو استخدام الصيغة = GetNumeric (A2). 

سيعطيك هذا على الفور الجزء الرقمي من السلسلة.

لاحظ أنه نظرًا لأن المصنف يحتوي الآن على تعليمات برمجية لـ VBA ، فستحتاج إلى حفظه بامتداد .xls أو .xlsm.

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

استخراج نص من سلسلة في Excel (باستخدام VBA)

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

فيما يلي رمز VBA الذي سنستخدمه لإنشاء هذه الوظيفة المخصصة:

الدالة GetText (CellRef as String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef، i، 1))) ثم النتيجة = النتيجة والوسط (CellRef، i، 1 ) التالي i GetText = وظيفة إنهاء النتيجة

فيما يلي خطوات إنشاء هذه الوظيفة ثم استخدامها في ورقة عمل:

ستتمكن الآن من استخدام وظيفة GetNumeric في ورقة العمل.نظرًا لأننا قمنا بكل المهام الثقيلة في الكود نفسه ، فكل ما عليك فعله هو استخدام الصيغة = GetText (A2).

سيعطيك هذا على الفور الجزء الرقمي من السلسلة.

لاحظ أنه نظرًا لأن المصنف يحتوي الآن على تعليمات برمجية لـ VBA ، فستحتاج إلى حفظه بامتداد .xls أو .xlsm.

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