توضیحات

یکی از مشکلات کاربران اکسل، علی الخصوص حسابداران، این است که نمی توانند به روش مطمئنی اعداد موجود در یک رشته متنی را از آن استخراج نمایند. فرض کنید که در یک سلول عبارتی شبیه به «چک شماره ۵۴۳۱۳۲ نزد بانک ملی شعبه ۱۳۲ مربوط به حساب ۱۳۲۱۳۵۱۲۱۵۱» دارید و قصد دارید اعداد موجود در آن را استخراج نمایید. برای جدا کردن عدد از متن در اکسل  چند روش وجود دارد که به ترتیب در زیر شرح می دهم.

۱- استخراج دستی اعداد


اصلا جالب نیست، منظورم این است که کار بسیار سختی است که بخواهید تک تک اعداد را تایپ نموده و یا بصورت دستی از سلول مبداء کپی نموده و در سلول مقصد جایگذاری نمایید. البته این روش اگر تعداد سلول ها شما کمتر از ۱۰ مورد باشد بهترین روش است و توصیه میشود، اما تصور کنید که بخواهید ۱۲۳۱۲۴۴۲۳ عدد را به این روش از ۲۳۴۲۳۴ سلول استخراج کنید. نه، نمیخواد تصور کنید. تصورش هم کار خوبی نیست.

 

جداکردن دستی اعداد در اکسل

 

۲- استخراج با استفاده از فرمولهای متنی


این روش روشی بسیار خوب و خداپسندانه است ولی مشکلش این است که فقط در مواردی به درد می خورد که نظم خاصی در محتوای سلول ها قابل مشاهده باشد. یعنی مثلاً همه اعدادی که قصد استخراج آن‌ها را داریم در از هشتمین کاراکتر شروع میشوند و همه ۶ رقمی هستند. یا مثلاً قبل و بعد از همه اعدادی که قصد استخراج آن‌ها را داریم پرانتز یا علامت مشخصه دیگری وجود دارد و…

در این روش جدا کردن عدد از متن در اکسل از ترکیب توابعی مانند Search, MID, LEN, LEFT, RIGHT استفاده میشود و طوری این توابع را با هم ترکیب میکنیم که خروجی آن‌ها به عدد مطلوب ما برسد

در مجموع این روش روش خوبیست اما محدودیت بزرگی دارد (همانی که اشاره شد) که بیشتر مواقع استفاده از این روش را غیر ممکن می کند.

 

تابع LEFT در اکسل

 

 

۳- استفاده از قابلیت Flash Fill


این قابلیت که فکر میکنم از آفیس ۲۰۱۳ به بعد به اکسل اضافه شد، هرچند قابلیت بسیار جالبی است اما در این قابلیت هم مثل مورد بالا باید نظمی مشهود باشد. منتها مزیت این روش نسبت به روش قبلی در این است که در این روش نیازی به فرمول نویسی و ترکیب توابع با یکدیگر نداریم. اما استفاده مستمر از این روش توصیه نمی شود. یعنی به نظرم برای استفاده موقتی روش مناسبی است.

FlashFill-Excel-2013

 

۴- استفاده از قابلیت Text to Column


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

 

convert-text-to-columns-wizard-step-2

 

مشاهده ویدئویی از دوره آموزش اکسل ویژه حسابداران که به مبحث جداکردن اعداد از متن پرداخته است.

۵- استفاده از افزونه‌های مخصوص


مثل همیشه آخرین راهی که وجود دارد استفاده از کد نویسی و توابع افزوده است. البته ایراد این روش هم این است که بعضی سیستم ها را کند می کند و در برخی به لحاظ امنیتی اصلاً اجرا نمیشود و در نهایت برخی را هم می ترساند.

من قبلاً یک تابع افزوده بنام GETNumber منتشر کرده بودم که با استفاده از آن می شد همه اعداد موجود در یک سلول را استخراج کرد. منتها ایرادی که داشت این بود که اگر تعداد اعداد موجود در سلول بیشتر از یکی بود، همه را به یکدیگر میچسباند و تبدیل به یک عدد می کرد. امروز تصمیم گرفتم این تابع رو کمی گسترش بدم تا کار دوستان رو ساده‌تر کنه. لذا دو تابع جدید به این افزونه اضافه کردم که عبارتند از GetNthNumber و GetArayNumber اما اینکه این توابع چیستند؟ و چه میکنند؟ و چطور؟، را خواهم گفت.

 

جدا کردن عدد از متن در اکسل

GETNthNumber


این تابع n امین عدد موجود در یک رشته متنی را برمیگرداند. دو ورودی دارد که ورودی اول strText همان متن شامل عدد است که میتوانید به سلول خود ارجاع دهید و N که مشخص میکند چندمین عدد موجود در این رشته را نیاز دارید. مثلا اگر strText‌همان متن بالا باشد («چک شماره ۵۴۳۱۳۲ نزد بانک ملی شعبه ۱۳۲ مربوط به حساب ۱۳۲۱۳۵۱۲۱۵۱») و N را برابر با ۲ قرار دهید خروجی تابع عدد ۱۲۳ خواهد بود

GetArayNumber


این تابع یک تابع آرایه‌ای است که میتوانید از آن در چند سلول بصورت همزمان و پیوسته استفاده کنید. در این صورت این تابع هر یک از اعداد موجود در متن را در یک سلول نمایش خواهد داد. همانطور که میدانید برای استفاده از توابع آرایه‌ای شبیه به این تابع (برای اینکه چند خروجی داشته باشید) باید ابتدا چند (نهایتاً ۱۰ خروجی خواهید داشت) سلول را انتخاب نمایید و با زدن کلید f2 اقدام به ویرایش سلول نموده و از تابع GetArayNumber استفاده نمایید. در نهایت بجای زدن کلید اینتر از ترکیب کلید های Ctrl+Shift+Enter استفاده کنید تا خروجی مطلوب نمایش داده شود.

این تابع رو برای استفاده حسابداران و حسابرسان عزیز بصورت رایگان در سایت قرار میدم (برید حالشو ببرید)