حذف عدد از متن در اکسل – راهنمای کاربردی

حذف عدد از متن در اکسل

در آموزش‌های مرتبط با اکسل مشاهده کرده‌اید که محتویات هر سلول می‌تواند، متن (Text)، عدد (Number) و فرمول (Formula) باشد. همچنین مقدار خطا (Errors) نیز یکی از انواع محتویات سلول‌های اکسل است. حال در نظر بگیرید که متن و عدد درون یک سلول با یکدیگر ترکیب شده‌اند. از دیدگاه اکسل، سلولی که متن خالص داشته یا متن و عدد به صورت ترکیبی در آن ثبت شده‌اند، یکسان تلقی می‌شود. در نتیجه اکسل در چنین عبارت ترکیبی، نمی‌تواند به راحتی متن را از عدد تفکیک کند.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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

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

روال کار به این صورت خواهد که ابتدا به کمک تکرار استفاده از یک تابع عمل جداسازی یا حذف عدد از متن در اکسل را اجرا کرده، سپس با توابع برداری و همچنین تابع تعریف شده توسط کاربر، محاسبات را انجام خواهیم داد.

استفاده از تابع SUBSTITUTE برای حذف عدد از متن در اکسل

کاربرگ مربوط به تصویر ۱ را در نظر بگیرید. همانطور که می‌بینید در سلول‌ A1 این کاربرگ، متن و عدد به صورت ترکیبی قرار گرفته‌اند.

فیلم آموزش توابع و فرمول‌نویسی در Excel در فرادرس

کلیک کنید

می‌خواهیم بخش متن را از کل محتویات سلول جدا کنیم و در حقیقت عبارت مورد نظر را با حذف عدد از متن در اکسل در سلول مجاور نمایش دهیم.

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

پارامترهای این تابع نیز در ادامه معرفی شده‌اند.

• text: عبارتی که باید در آن جایگزینی صورت گیرد.
• old_text: متن یا عبارت قدیمی که باید جایگزین شود.
• new_text: متن یا عبارت جدیدی که باید به جای مقدار مربوط به پارامتر قبلی در عبارت قرار گیرد.
• instance_num: مقدار عددی که بیانگر جایگزینی در تکرار چندم عبارت old_text است. این پارامتر اختیاری است و در صورت وارد نکردن آن، جایگزینی از اولین محل وقوع پارامتر old_text صورت می‌گیرد و همه عبارت‌های old_text با new_text جایگزین می‌شود.

البته توجه داشته باشید که در اینجا برای جداسازی متن از عبارت مخلوط، چندین بار از تابع SUBSTITUTE استفاده خواهیم کرد. واضح است که باید ارقام ۰،۱،۲،۳،۴،۵،۶،۷،۸،۹ از عبارت مورد نظر با کاراکتر خالی ("") جایگزین شوند. حال به فرمولی که در تصویر ۱ و در سلول A2 نوشته شده، نگاهی می‌اندازیم.

=SUBSTITUTE(A1,B2,"")

مشخص است که عبارت مورد جستجو در این تابع در سلول A1 قرار دارد. همچنین هر یک از ارقام ۰ تا ۹ نیز در سلول‌های B2 تا B11 نوشته شده است. در هر بار استفاده از این تابع، یکی از ارقام در متن با عبارت خالی جایگزین می‌شود. با کپی کردن این فرمول به سلول‌های بعدی، عبارت حاصل، دوباره مورد جستجو قرار گرفته و رقم بعدی از درون آن حذف می‌شود تا جایی که در سلول A11، هیچ رقم یا عددی در رشته متنی باقی نمی‌ماند. به این ترتیب کافی است که فرمول مربوط به سلول A2 را در سلول‌های بعدی تا A11 کپی کنید.

استفاده از فرمول و توابع برداری برای حذف عدد از متن در اکسل

فرض کنید رشته متنی در سلول A1 مطابق با تصویر ۱، نوشته شده است. فرمول ترکیبی زیر برای جداسازی ارقام از این رشته متنی به کار رفته است.

نکته: از آنجایی که این فرمول، برداری است برای ثبت آن باید از کلیدهای ترتیبی Ctrl+Shift+Enter استفاده کنید. به این ترتیب در ابتدا و انتهای فرمول، علامت‌های } , { دیده می‌شود. اگر فرمول را به صورت برداری ثبت نکنید، عملیات محاسباتی به درستی انجام نمی‌شوند.

در ادامه توابع به کار رفته در این فرمول را به صورت خلاصه معرفی کرده و ویژگی‌ها هر یک را بازگو می‌کنیم.

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

  TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

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

• تابع IF: با این تابع، مقدار یک سلول براساس شرط تعیین می‌شود. شکل دستوری این تابع سه پارامتر داشته و پارامتر اول شرط (Logical) و دو پارامتر بعدی، مقدار سلول را براساس برقراری شرط و تناقض در شرط مشخص می‌کنند.

IF(logical_test,[Value_if_true],[Value_if_false])

• تابع ISERR: فرض کنید بخواهیم تشخیص دهیم سلولی دارای خطا (بدون خطای !N/A#) هست یا خیر، در صورتی که خطا وجود داشته باشد، مقدار TRUE و در غیر اینصورت، مقدار FALSE، بازگردانده می‌شود. پارامتر این تابع یک آدرس یا یک فرمول است که باید مورد ارزیابی قرار گیرد.

ISERR(value)

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

MID(text,star_num,num_chars)

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

• تابع ROW: این تابع شماره ردیف آدرس معرفی شده در پارامتر تابع را مشخص می‌کند. شکل دستوری این تابع با پارامتر آن در ادامه قابل مشاهده است.

ROW(reference)

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

INDIRECT(ref_text,[a1])

پارامتر اول، آدرس سلول یا مرجعی است که به صورت متنی ثبت شده و پارامتر دوم که اختیاری نیز هست به نوع آدرس دهی (A1) یا (R1C1) اشاره دارد.

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

ROW(INDIRECT("1:100"))

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

قسمت بعدی، تابع MID است که حروف این رشته متنی را حرف به حرف استخراج می‌کند. مقدار تولید شده در قسمت قبل (اعداد ۱ تا ۱۰۰) به عنوان پارامتر start_num در نظر گرفته شده و پارامتر num_chars نیز ۱ فرض شده است. به این ترتیب این تابع یک بردار از تک تک حروف رشته ترکیبی ایجاد می‌کند، شکل دستوری این تابع به صورت زیر است.

واضح است که هر درایه از بردار حاصل، از تفکیک حروف و اعداد رشته متنی در سلول A1 تشکیل شده است و به صورت زیر خواهد بود.

‎{2;3;A;B;C;D;7;8;9;E;F;5;4;6;G;0;H;...}‎‏

نکته: در عبارت بالا، علامت «...» نشانگر حروف بعدی تا ۱۰۰ امین حرف است. واضح است که از آخرین حرف این رشته تا ۱۰۰ امین حرف، رشته خالی ("") برای اعداد در فرمول باید منظور شود.

در انتهای فرمول مقدار این بردار را با صفر جمع کرده‌ایم. این کار به این علت صورت گرفته است که بردار از نظر اکسل تبدیل به یک بردار عددی شود. به این ترتیب کاراکترهای عددی، به عدد تبدیل شده و کاراکترهای متنی، باعث ایجاد خطای !Value# می‌شوند.

در قسمت بعدی با تابع ISERR و IF،‌ درایه‌ها با مقدار خطای !Value# را شناسایی کرده و در بردار با مقدار واقعی خود جایگزین می‌کنیم. در همین حال، مقادیر عدد، با رشته خالی جایگزین می‌شوند.

در انتها نیز بردار حاصل در تابع TEXTJOIN به عنوان پارامتر text1 قرار می‌گیرد. همچنین جداکننده برای هر بخش نیز رشته خالی ("") در نظر گرفته شده و با گزینه TRUE، صرف‌نظر کردن از رشته‌های خالی نیز در دستور کار قرار می‌گیرد.

با نوشتن این فرمول و ترکیب کردن توابع ذکر شده، ‌همه رشته‌های متنی به یکدیگر ملحق شده و رشته‌ای بدون ارقام و اعداد تولید می‌شود.

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

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

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

کلیک کنید

نکته: روش دیگر برای دسترسی به پنجره کد نویسی، استفاده از برگه Developer است. کافی است دکمه Visual Basic را از قسمت Code انتخاب کنید. ولی معمولا هنگام نصب اولیه اکسل این برگه در لیست برگه‌های قابل استفاده در اکسل قرار ندارد. در صورتی که برگه Developer دیده نمی‌شود، بهتر است از فهرست File، دستور Options را اجرا کرده و با کلیک در قسمت Customize Ribbon از کادر سمت چپ، گزینه Developer از کادر سمت راست را انتخاب کنید.

در پنجره ویژوال بیسیک، از فهرست Insert گزینه Module را انتخاب کرده و کد بالا را در آن کپی کنید.

همانطور که در کد مشاهده می‌کنید، ابتدا یک تابع به نام RemoveNumbers با ماهیت و نوع داده متنی معرفی شده است. مشخص است که یک عبارت متنی در متغیر Txt وارد تابع شده و براساس الگو (Pattern) با مقادیر 0 تا ۹، عمل جایگزینی (Replace) صورت می‌گیرد و همه مقادیر  الگوی معرفی شده با کاراکتر خالی ("") جایگزین می‌شوند. پس حاصل این تابع، رشته متنی است که هیچ رقمی یا عددی در آن وجود ندارد.

حال با فرض اینکه عبارت ترکیبی از متن و عدد در سلول A1 نوشته شده است، تابع RemoveNumbers را در یک سلول خالی و به صورت زیر می‌نویسیم.

= RemoveNumbers(A1)

بنابراین در سلول وابسته، با حذف ارقام و اعداد، فقط قسمت متن مربوط به سلول A1 باقی خواهد ماند. به بزرگ بودن حرف اول Remove و Numbers دقت کنید تا با خطای !Name# مواجه نشوید.

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

اگر فرض کنیم تابع در کارپوشه book1.xlsb ذخیره شده و می‌خواهیم در کارپوشه book2 از آن بهره ببریم باید فرمولی به صورت زیر بنویسیم:

book1.xlsb!RemoveNumbers(reference)

که reference آدرس سلولی است که باید ارقام درون عبارت متنی از آن خارج شود.

حذف فاصله‌های خالی در رشته متنی

ممکن است بعد از حذف عدد از متن در اکسل، هنوز رشته متنی شامل علائم مزاحمی مانند فاصله‌های خالی نیز باشد. اگر می‌خواهید این کاراکتر را نیز از رشته متنی خارج کنید از تابع TRIM به صورت زیر استفاده کنید.

TRIM(reference)

که در آن reference، آدرس سلولی است که ارقام از متن آن جدا شده است و فقط قرار است فضاهای خالی از آن حذف شوند.

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

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

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