استخراج اعداد از یک رشته در اکسل

این آموزش نحوه استخراج شماره از رشته های متن مختلف در Excel با استفاده از فرمول ها و ابزار Extract را نشان می دهد.

وقتی نوبت به استخراج قسمتی از یک رشته متن با طول معین می رسد ، Excel سه عملکرد Substring (چپ ، راست و وسط) را برای رسیدگی سریع به کار ارائه می دهد. وقتی صحبت از استخراج یک عدد از یک رشته الفبایی می شود ، Microsoft Excel هیچ چیزی ارائه نمی دهد.

برای بدست آوردن یک عدد از یک رشته در اکسل ، کمی نبوغ ، کمی حوصله و مجموعه ای از توابع مختلف که به یکدیگر چسبیده اند نیاز است. یا می توانید ابزار Extract را اجرا کرده و کار را با کلیک ماوس انجام دهید. در زیر جزئیات کامل هر دو روش را خواهید یافت.

نحوه استخراج شماره از انتهای رشته متن

وقتی ستونی از رشته های الفبایی دارید که تعداد آنها بعد از متن آمده است ، می توانید از فرمول زیر برای بدست آوردن آن استفاده کنید.

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

کمی بعد روی منطق فرمول می پردازیم. در حال حاضر ، به سادگی سلول را با اشاره به سلول حاوی رشته اصلی جایگزین کنید (در مورد ما A2) و فرمول را در هر سلول خالی در همان ردیف ، مثلاً در B2 وارد کنید.

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

این فرمول فقط از انتها عدد می گیرد. اگر یک رشته نیز دارای اعداد در ابتدا یا وسط باشد ، آنها نادیده گرفته می شوند :

استخراج با عملکرد RIGHT انجام می شود که متعلق به دسته توابع Text است. خروجی این تابع همیشه متن است. در مورد ما ، نتیجه یک زیر رشته عددی است که از نظر Excel نیز متن است ، نه عدد.

اگر نیاز دارید که نتیجه یک عدد باشد (که می توانید در محاسبات بیشتر از آن استفاده کنید) ، سپس فرمول را در تابع VALUE بپیچید یا یک عملیات حسابی را انجام دهید که نتیجه را تغییر ندهد ، مثلاً ضرب در 1 یا اضافه کردن 0. خطاها در رشته هایی که شامل یک عدد نیستند ، از تابع IFERROR استفاده می کنند.
بطور مثال :

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

و یا

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

نحوه عملکرد این فرمول:

برای استخراج عدد از یک رشته الفبایی ، اولین چیزی که باید بدانید این است که استخراج را از کجا شروع کنید. موقعیت آخرین نویسه غیر عددی در یک رشته با کمک این فرمول پیچیده تعیین می شود :

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

برای درک فرمول، بیایید آن را از درون بررسی کنیم:

ترکیب ROW (INDIRECT ("1:" & LEN (A2))) دنباله ای ایجاد می کند که اعدادی مربوط به تعداد کل کاراکترهای رشته منبع (A2) است و ما این اعداد متوالی را به عنوان شماره های اولیه به MID ارائه می دهیم: :

MID(A2, {1;2;3;4;5;6;7;8}, 1)

تابع MID هر کاراکتر جداگانه را از A2 می کشد و آنها را به عنوان یک آرایه باز می گرداند :

{"0";"5";"-";"E";"C";"-";"0";"1"}

از آنجا که MID یک تابع متن است ، خروجی آن همیشه متنی است (همانطور که می بینید ، همه کاراکترها در علامت نقل قول محصور شده اند). برای تبدیل عددهای عددی به اعداد ، آرایه را در 1 ضرب می کنیم (نفی مضاعف -MID () همان تأثیر را خواهد داشت). نتیجه این عملیات یک آرایه از اعداد و #ارزش است! خطاهایی که کاراکترهای غیر عددی را نشان می دهند :

ISNUMBER({0;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0;1})

تابع ISNUMBER هر عنصر آرایه را ارزیابی می کند و حکم خود را در قالب مقادیر بولی ارائه می دهد - TRUE برای اعداد ، FALSE برای سایر موارد:

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}

این آرایه به آزمایش منطقی تابع IF می رود ، جایی که هر عنصر آرایه با FALSE مقایسه می شود:

IF({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}=FALSE, ROW(INDIRECT( "1:"&LEN(A2))), 0)

برای هر FALSE (مقدار غیر عددی) ، یک تابع ROW (INDIRECT ()) موقعیت نسبی خود را در رشته برمی گرداند. برای هر TRUE (مقدار عددی) ، یک صفر بازگردانده می شود. آرایه به دست آمده به صورت زیر است :

{0;0;3;4;5;6;0;0}

بقیه آسان است. تابع MAX بالاترین عدد را در آرایه بالا پیدا می کند ، که موقعیت آخرین مقدار غیر عددی در رشته است (در مورد ما 6). به سادگی ، آن موقعیت را از طول کل رشته ای که توسط LEN بازگردانده می شود ، کم کنید و نتیجه را به RIGHT منتقل کنید تا به او اطلاع دهید که چند کاراکتر از سمت راست رشته استخراج کند. :

RIGHT(A2, LEN(A2) - 6)

Done!

نحوه استخراج شماره از ابتدای رشته متن

اگر با پرونده هایی کار می کنید که متن بعد از شماره ظاهر می شود ، می توانید با استفاده از این فرمول عمومی ، شماره را از ابتدای یک رشته استخراج کنید. :

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

با رشته اصلی در A2 ، از فرمول زیر برای بدست آوردن شماره استفاده کنید :

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

مهم نیست که چند عدد در وسط یا انتها وجود دارد ، فقط شماره شروع استخراج می شود :

نحوه عملکرد این فرمول :

در اینجا ، ما مجدداً از ترکیب توابع ROW ، INDIRECT و LEN برای ایجاد توالی اعدادی برابر با تعداد کل کاراکترهای رشته منبع بعلاوه 1 استفاده می کنیم (نقش آن شخصیت اضافی کمی دیرتر مشخص می شود) .

ROW(INDIRECT( "1:"&LEN(A2)+1))

MID و ISNUMBER کار مشابهی را در مثال قبلی انجام می دهند - MID کاراکترهای جداگانه را می کشد و ISNUMBER آنها را به مقادیر منطقی تبدیل می کند. آرایه حاصل از TRUE's و FALSE به تابع MATCH به عنوان یک آرایه جستجو می رود :

MATCH(FALSE, {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}, 0)

MATCH موقعیت نسبی اولین FALSE را محاسبه می کند و موقعیت اولین کاراکتر غیر عددی در رشته (3 در A2) را به ما می دهد. برای استخراج اعداد قبلی ، 1 را از موقعیت اولین کاراکتر متن کم می کنیم و تفاوت را در آرگومان num_chars تابع LEFT ارائه می دهیم :

LEFT(A2, 3-1)

اکنون ، به یک کاراکتر "اضافی" در دنباله تولید شده توسط ROW (INDIRECT ()+1)) بازگردید. همانطور که قبلاً می دانید ، این دنباله نقاط شروع عملکرد MID را فراهم می کند. بدون 1+ ، MID دقیقاً به تعداد کاراکترهای موجود در رشته منبع استخراج می کند. اگر رشته فقط شامل اعداد باشد ، ISNUMBER فقط TRUE را برمی گرداند در حالی که MATCH به حداقل یک FALSE نیاز دارد. برای اطمینان از این امر ، یک کاراکتر دیگر به طول کل رشته اضافه می شود ، که عملکرد MID آن را به یک رشته خالی تبدیل می کند. به عنوان مثال ، در B7 ، MID این آرایه را برمی گرداند :

{"1";"2";"3";"4";""}

توجه داشته باشید. همانطور که در مورد تابع RIGHT ، LEFT یک زیر رشته عددی را نیز برمی گرداند ، که از نظر فنی متنی است ، نه عددی. برای بدست آوردن نتیجه به عنوان یک عدد و نه یک رشته عددی ، فرمول را در تابع VALUE قرار دهید یا نتیجه را در 1 ضرب کنید ، همانطور که در مثال اول نشان داده شده است .

نحوه بدست آوردن شماره از هر موقعیتی در یک رشته

اگر وظیفه شما شامل استخراج شماره از هر نقطه در یک رشته است ، می توانید از فرمول ذهنی زیر منتشر شده در انجمن MrExcel استفاده کنید :

=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)

جایی که A2 رشته متن اصلی است.

شکستن این فرمول به مقاله جداگانه ای نیاز دارد ، بنابراین می توانید آن را به سادگی در کاربرگ خود کپی کنید تا مطمئن شوید که واقعا کار می کند :)

با بررسی نتایج ، ممکن است متوجه یک اشکال ناچیز شوید - اگر رشته منبع عددی نداشته باشد ، فرمول صفر را باز می گرداند ، مانند ردیف 6 در تصویر بالا. برای رفع این مشکل ، می توانید فرمول را در دستور IF بپیچید ، که تست منطقی آن بررسی می کند که آیا رشته منبع حاوی عددی است یا خیر. در این صورت ، فرمول عدد را استخراج می کند ، در غیر این صورت یک رشته خالی را برمی گرداند :

=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),"")

همانطور که در تصویر زیر نشان داده شده است ، فرمول بهبود یافته به زیبایی کار می کند :

بر خلاف نمونه های قبلی ، نتیجه این فرمول عدد است. برای اطمینان از این امر ، فقط به مقادیر راست تراز شده در ستون B و صفرهای برش کوتاه توجه کنید.

نکته : در Excel 365 و Excel 2019 ، یک راه حل بسیار ساده تر با استفاده از تابع TEXTJOIN وجود دارد. لطفاً نحوه حذف متن و حفظ اعداد را ببینید