جدا کردن عدد از متن در اکسل – راهنمای کاربردی

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

به منظور آشنایی بیشتر با نحوه فرمول‌نویسی و درج توابع در اکسل بهتر است ابتدا نوشتارهای دیگر مجله فرادرس مانند آموزش اکسل — به زبان ساده و فرمول نویسی در اکسل — به زبان ساده را مطالعه کنید. همچنین خواندن مطالب توابع جدید اکسل ۲۰۱۹ — راهنمای کاربردی و تابع برداری — به زبان ساده نیز خالی از لطف نیست.

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

در بسیاری از موارد اسناد و مدارک ثبت شده در سامانه‌های رایانه‌ای، بدون در نظر گرفتن ساختار مشخص، ذخیره و بازیابی می‌شوند و این امر ممکن است تهیه گزارشات متنوع روی چنین داده‌هایی را با سختی و مشکل همراه سازد. یکی از این موارد می‌تواند آمیختگی متن و عدد در یک عبارت یا رشته متنی باشد. کاربرگی که در تصویر ۱ مشاهده می‌کنید را در نظر بگیرید. واضح است که تنها راهی که برای استخراج شماره چک در این اسناد وجود دارد، حذف کردن حروف و عبارت‌های متنی است تا به این ترتیب باقی‌مانده رشته مربوط به شرح سند، نشانگر شماره چک باشد.

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

نکته: به این موضوع نیز توجه داشته باشید که اعداد درون این رشته متنی، فقط مربوط به شماره چک است و هیچ رقم یا عدد دیگری در متن نوشته نشده است. در ضمن ابتدای کار فرض می‌کنیم که شماره چک‌ها یک عدد چهار رقمی هستند.

جدا کردن عدد از متن در اکسل با فرمول و توابع ترکیبی

در این قسمت می‌خواهیم به شیوه استخراج و جداسازی ارقام از رشته متنی ترکیبی بپردازیم و در این بین از تابع FIND استفاده خواهیم کرد. البته برای آنکه بتوانیم این کار را در نهایت به درستی انجام دهیم، از یک فرمول برداری کمک خواهیم گرفت. ولی ابتدا تابع FIND را به کار گرفته و براساس آن جستجو ارقام در رشته متنی را اجرا می‌کنیم. البته عکس این عمل یعنی حذف و جدا کردن عدد از متن که به صورت ترکیبی از ارقام و متن بود را در نوشتار دیگری از مجله فرادرس با عنوان حذف عدد از متن در اکسل — راهنمای کاربردی مورد بررسی قرار داده‌ایم.

فیلم آموزش ترکیب داده‌ ها از چندین سلول در اکسل + استفاده از TEXTJOIN با مثال‌ (رایگان) در فرادرس

کلیک کنید

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

همانطور که در دیگر نوشتارهای مجله فرادرس خوانده‌اید، تابع FIND به دنبال یک عبارت در یک رشته متنی می‌گردد و موقعیت آن را نشان می‌دهد. در این قسمت با استفاده از یک فرمول که با تابع FIND همراه است، روشی برای جدا سازی ارقام از رشته متنی در اکسل معرفی می‌کنیم. البته در اینجا یک تصحیح روی کاربرگ اسناد مربوط به تصویر ۱ انجام داده‌ایم. برای اینکه بتوانیم با استفاده از تابع FIND عمل شناسایی و استخراج شماره چک را انجام دهیم، هر یک از شماره چک‌ها را تبدیل به چهار رقم کرده‌ایم تا کار جداسازی راحت‌تر صورت گیرد. ولی در قسمت بعدی حالتی را که براساس تصویر ۱ داشتیم، اجرا خواهیم کرد.

فرمول مورد نظر به صورت زیر است:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789"))

البته فرض بر این است که رشته متنی که باید ارقام از آن جدا شوند در سلول B2 قرار گرفته است. توجه داشته باشید که درونی‌ترین پرانتز، اولین محاسبه‌ای است که در اکسل صورت می‌گیرد. به این ترتیب مراحل انجام محاسبات را در ادامه مشخص می‌کنیم.

• تابع FIND: همانطور که مشاهده می‌کنید پارامتر اول تابع FIND، ارقام ۰ تا ۹ است. به این معنی که این اعداد باید در رشته متنی که در سلول B2 قرار دارد، جستجو شوند. از آنجایی که ممکن است رشته متنی، شامل هیچ رقمی نباشد، به محتویات سلول B2، رشته ارقام ۰ تا ۹ اضافه شده تا نتیجه تابع FIND‌، با خطا همراه نباشد.

FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")

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

• تابع MIN: در صورتی که رشته متنی شامل هیچ رقمی نباشد، تابع FIND پیغام خطای !value# را نشان می‌دهد. برای جلوگیری از ظاهر شده این پیغام، ابتدا به انتهای رشته متنی، ارقام ۰ تا ۹ را اضافه کرده و حداقل نتیجه موقعیت هر یک از ارقام را در تابع FIND محاسبه می‌کنیم. اگر رشته شامل هیچ رقمی نباشد، تابع FIND، طول رشته متنی را بر می‌گرداند.

برای سلول پیش نیاز B2، نتیجه محاسبه فرمول بالا برابر با ۱۱ است. پس نتیجه می‌گیریم که از حرف ۱۱ام شماره چک شروع شده و چهار حرف بعدی، شماره چک را تشکیل می‌دهند. به این ترتیب به کمک تابع MID به راحتی می‌توانیم شماره چک را استخراج کنیم.

• تابع MID: این تابع براساس سه پارامتر، یک زیر رشته را از رشته متن اصلی جدا می‌کند. پارامتر اول آن رشته متنی، پارامتر دوم، نقطه شروع استخراج و پارامتر سوم نیز تعداد ارقام قابل استخراج را مشخص می‌کند.

=MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),4)

به این ترتیب نتیجه به کارگیری این فرمول برای سلول‌های B2 تا B4، مطابق با تصویر ۲ خواهد بود.

یکی از مشکلاتی که استفاده از تابع FIND در این قسم دارد، مشخص بودن تعداد ارقام رشته عددی است. اگر شماره چک ارقام مشخص نداشته باشد، فرمول نوشته شده براساس تابع FIND پاسخگو نخواهد بود.

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

در این قسمت از یک فرمول پیچیده استفاده خواهیم کرد. در این بین از توابع INDIRECT ،ROW ،IFERROR و TEXTJOIN کمک خواهیم گرفت.

{=TEXTJOIN("",TRUE,IFERROR(MID(B2,ROW(INDIRECT("1:100")),1)+0,""))}

شرح پارامترها و نحوه عملکرد این توابع در نوشتار حذف عدد از متن در اکسل — راهنمای کاربردی آمده است. همانطور که در فرمول دیده می‌شود، علامت‌های } و {‌ در فرمول، نشانگر برداری بودن این فرمول هستند و باید بعد از نوشتن این فرمول برای ثبت آن از کلیدهای Ctrl+Shift+Enter استفاده شود تا اکسل برداری بودن فرمول را درک کند.

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

نکته: پارامتر 1:100 در تابع INDIRECT به طول رشته‌ متنی اشاره دارد. به این ترتیب فرمول یاد شده برای عبارت‌هایی که از ۱ تا ۱۰۰ حرف هستند معتبر است. اگر می‌خواهید استخراج ارقام را برای سلولی با بیش از ۱۰۰ حرف، اجرا کنید، باید حداکثر تعداد حروف در رشته متنی مورد نظرتان را به جای ۱۰۰ بنویسید.

به این ترتیب به کمک تابع TEXTJOIN، حروفی که استخراج شده‌اند در کنار یکدیگر قرار گرفته و عبارت نهایی را تولید می‌کنند. با توجه به توضیحات گفته شده در مورد فرمول معرفی شده و به کار بردن شیوه ثبت برداری برای این فرمول، بر اساس کاربرگی که در تصویر 3 مشاهده می‌کنید، می‌توانیم اعداد را از هر ترکیب قرارگیری متن و رقم، جدا کرده و در ستون C ثبت کنیم. فقط توجه داشته باشید که تابع TEXTJOIN از توابعی است که در اکسل ۲۰۱۹ معرفی شده و قابل استفاده در نسخه‌های قدیمی نیست. البته در این بین تابع CONCATENATE نیز قابل استفاده است ولی ویژگی‌های اصلی تابع TEXTJOIN را ندارد.

جدا کردن عدد از متن در اکسل با کد ویژوال بیسیک

این بار سعی می‌کنیم که به کمک یک قطعه کد به زبان «ویژوال بیسیک برای برنامه‌های کاربردی» (Visual Basic for Application) که به VBA، معروف است، برنامه‌ای برای حذف عبارت و حروف لاتین در یک رشته متنی بنویسیم تا به کمک آن، ارقام و اعداد را جدا کنیم.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

با ایجاد این قطعه کد در محیط ویرایشگر زبان برنامه‌نویسی ویژوال بیسیک در اکسل، به عنوان یک ماژول (Module)، می‌توانید از تابع RemoveText برای حذف رشته‌های متنی اقدام کنید و اعداد و ارقام را به صورت جداگانه در یک سلول دیگر ثبت و ذخیره کنید. کدی که در زیر مشاهده می‌کنید به این منظور نوشته شده است. ابتدا در کاربرگ اکسل، کلیدهای ترکیبی Alt+F11 را فشرده و در پنجره کدنویسی با ویژوال بیسیک، یک ماژول (Module) جدید از فهرست Insert ایجاد کرده و کد زیر را در آن وارد کنید.

نکته: کد بالا فقط براساس حروف کوچک لاتین، الگوی متنی را تشخیص و آن را حذف می‌کند. اگر می‌خواهید حروف بزرگ و کوچک لاتین از رشته متنی حذف شوند، کافی است کد VBA را مطابق با تصویر ۴، به عنوان یک «ماژول» (Module) وارد و ثبت کنید.

به این ترتیب تابع جدیدی در اکسل و کارپوشه شما با نام RemoveText ایجاد خواهد شد که عمل حذف حروف و علائم را انجام خواهد داد. به تصویر ۵ که نحوه به کارگیری این تابع را نمایش داده، توجه کنید. همانطور که می‌توان حدس زد، پارامتر این تابع، رشته متنی است که باید ارقام از آن استخراج شوند.

با کمی تغییر در قسمت مربوط به تشخیص الگو (که توسط پارامتر Pattern. مشخص شده) می‌توان، این تابع را برای حذف کردن حروف فارسی و لاتین به طور همزمان تغییر داد. در تصویر ۶، قطعه کدی را مشاهده می‌کنید که همه حروف فارسی، لاتین و علائم متنی را از رشته ترکیبی حذف کرده و فقط ارقام و اعداد را به جای می‌گذارد.

به این ترتیب تابع جدیدی به نام EXTRACTNUM در کارپوشه ساخت می‌شود که عمل جدا کردن عدد از متن برای رشته‌های متنی که به صورت مخلوطی از حروف فارسی، لاتین و علائم دیگر است، را انجام می‌دهد. نتیجه اجرای تابع EXTRACTNUM در یک کاربرگ اطلاعاتی در تصویر ۷ قابل مشاهده است. همانطور که می‌بینید همه حروف (فارسی و لاتین)‌ حذف شده و فقط اعداد باقی مانده‌‌اند.

نکته: در تصویر 6، پارامتر "+[d\^]"، به کدی اشاره دارد که به معنی همه حروف به جز اعداد (digits) است. در حقیقت علامت ^ نقش عملگر not یا نقیض را ایفا می‌کند. علامت + نیز در انتهایی این کد، بیانگر هر تعداد تکرار از این حروف (به جز اعداد) است. این علائم «عبارت با قاعده» (Regular expression) یا به اختصار Regex گفته می‌شوند. به این ترتیب همه حروف به عنوان الگو معرفی شده و توسط تابع Replace. با رشته خالی "" جایگزین می‌شوند. البته برای تعیین همین الگو می‌توان از [9-0^] نیز استفاده کرده و همین نتیجه را گرفت.

به منظور دریافت کدها و همچنین توابع و فرمول‌هایی که در این متن به کار رفته، فایلی فشرده را از اینجا دریافت کنید و پس از خارج کردن از حالت فشرده در اکسل باز کنید. توابعی که در این متن به آن‌ها اشاره شد، به این طریق قابل استفاده بوده و همچنین از طریق برگه Developer و انتخاب گزینه Visual Basic، به کدهای ویژوال بیسیک هر یک از توابع معرفی شده برای جدا کردن عدد از متن نیز دسترسی خواهید داشت.

در این فایل، دو تابع RemoveText و RemoveNumber وجود دارند که به ترتیب باعث حذف حروف و حذف اعداد و ارقام از یک رشته متنی می‌شوند. با به کارگیری هر یک از این توابع در سلول‌های مجزا، می‌توانید بخش متنی و عددی یک رشته متنی مخلوط (عدد و حروف) را در سلول‌های جداگانه ثبت و ذخیره کنید.

خلاصه و جمع‌بندی

در این متن به کمک فرمول و توابع اختصاصی اکسل، ارقام و اعداد را از یک سلول یا چند سلول با محتویات ترکیبی (متن و عدد) جدا کردیم. البته به چند شیوه این کار صورت گرفت. برای جدا کردن عدد از متن ابتدا ترکیبی از توابع و فرمول‌ها را به کار بردیم، و در بخشی به صورت فرمول ساده و در بخش دیگر به صورت برداری، ترکیب توابع و فرمول را به کار بردیم. در انتها نیز با استفاده از کدنویسی در محیط «ویژوال بیسیک در اکسل» (VBA- Visual Basic for Applications)، قطعه کدی کوتاه تولید کردیم که به راحتی به عنوان یک تابع جدید قابل استفاده بود. با ذخیره سازی این تابع به عنوان یک ماژول در اکسل، می‌توانید از این تابع در کارپوشه‌های دیگر نیز بهره ببرید. کد انتخاب رشته حرفی نیز با دو شیوه برای جداسازی ارقام از متن معرفی و به کار گرفته شد.