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

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

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

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

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

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

1MID(A1,ROW(INDIRECT("1:100")),1)

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

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

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

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

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

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

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

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

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

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

1Function RemoveNumbers(Txt As String) As String
2With CreateObject("VBScript.RegExp")
3.Global = True
4.Pattern = "[0-9]"
5RemoveNumbers = .Replace(Txt, "")
6End With
7End Function

برای ثبت و ایجاد تابع براساس این کد،‌ در کاربرگ اکسل، از کلیدهای ترکیبی Alt+F11 استفاده کنید. با این کار پنجره کدنویسی با ویژوال بیسیک برای کاربرگ Sheet1 باز می‌شود.

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

کلیک کنید

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

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

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

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

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

= RemoveNumbers(A1)

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

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

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

کلیک کنید

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

book1.xlsb!RemoveNumbers(reference)

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

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

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

TRIM(reference)

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

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

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

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