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

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

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

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

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

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

کلیک کنید

البته اگر ترکیب متن و عدد دارای قاعده خاصی باشند، شاید بتوان با استفاده از Flash Fill هر یک از بخش‌ها را جدا کرد ولی در صورتی که قانونی برای تشخیص محل آغاز عدد در کنار متن وجود نداشته باشد، باید از راه‌کارهای دیگری که گاهی پیچیده نیز هستند، کمک گرفت. منظور از وجود قانون، جداکننده (Delimiter) مثل کاما (,) یا طول ثابت (Fixed width) حروف متن یا ارقام است.

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

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

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

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

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

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

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

کلیک کنید

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

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

1SUBSTITUTE(text, old_text, new_text, [instance_num])

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

• 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 کپی کنید.

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

در این بخش از چند تابع استفاده خواهیم کرد و یک فرمول ترکیبی با توابع تو در تو خواهیم نوشت. البته بیشتر این توابع را در نوشتارهای دیگر مجله فرادرس، معرفی کرده‌ایم. برای مثال تابع IF و INDIRECT که بسیار پرکاربرد هم هستند، قبلا مورد بررسی قرار گرفته‌اند. تابع TEXTJOIN نیز از توابع جدیدی است که در اکسل ۲۰۱۹ معرفی شده است و رشته‌های متنی را به یکدیگر پیوند می‌زند. همچنین با نحوه محاسبات برداری و شیوه نوشتن آن‌ها در اکسل نیز در مطلب دیگری از مجله فرادرس آشنا شدید. در این قسمت همه این مهارت‌ها را در کنار یکدیگر گذاشته و برای حذف عدد از متن در اکسل به کار می‌گیریم.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

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

1=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),""))

نکته: از آنجایی که این فرمول، برداری است برای ثبت آن باید از کلیدهای ترتیبی 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 آن را به عنوان یک بردار با ۱۰۰ سطر در نظر می‌گیرد. این بردار شامل مقادیر ۱ تا ۱۰۰ خواهد بود. توجه داشته باشید که ۱۰۰ در اینجا به معنی تعداد حروف رشته ترکیبی است. بنابراین فرمول مورد نظر برای رشته‌های متنی با ۱۰۰ حرف مناسب است. اگر لازم است در رشته‌های متنی طولانی‌تر کار حذف عدد از متن در اکسل را انجام دهید، مقدار ۱۰۰ را تغییر و دلخواه تنظیم کنید.