در نرم‌افزار Excel ممکن است سلولی از نوع متنی یا Text باشد و در عین حال داخل آن عدد تایپ شده باشد. اگر بخواهید از این سلول در محاسبات استفاده کنید، لازم است ابتدا عدد داخل آن جدا یا با فرمولی استخراج شود و به عبارت دیگر از حالت متنی به عددی تبدیل شود. این کار بدون تغییر دادن نوع سلول امکان‌پذیر است.
در ادامه به نحوه‌ی استخراج عدد و همین‌طور متن از سلول‌ها در نرم‌افزار اکسل مجموعه آفیس مایکروسافت می‌پردازیم. با ما باشید.
تبدیل فرمت سلول متنی به عددی
کسانی که به وفور از اکسل برای پردازش عبارت‌های متنی و عددی که از منابع مختلف کپی شده استفاده میکنند، احتمالاً تاکنون با این مشکل روبرو شده‌اند که سلولی حاوی عدد است اما نوع سلول Text یا متن است. در نتیجه استفاده از عدد داخل سلول در محاسبات ریاضی امکان‌پذیر نیست. ممکن است فقط یک یا چند سلول خاص از یک ستون عددی، این مشکل را داشته باشد و کنار چنین سلول‌هایی علامتی نمایش داده شود.
به مثالی از سلول متنی حاوی عدد توجه کنید:
دو راهکار ساده برای حل مشکل وجود دارد: راهکار اول تبدیل فرمت سلول است و راهکار بعدی پیست کردن فرمت عددی در سلول‌ها است. در ادامه هر دو روش را بررسی می‌کنیم.
اگر در گوشه‌ی سلول‌های حاوی عدد علامت پرچم سبز مشاهده می‌کنید، سلول‌ها را انتخاب کنید و روی علامت هشدار کلیک کنید. در منوی باز شده روی Convert to Number کلیک کنید.
اگر در گوشه‌ی سلول علامتی دیده نمی‌شود، سلول‌های موردنظر را انتخاب کنید و سپس از منوی کرکره‌ای Number Format در تب Home نوار ابزار بالای صفحه، گزینه‌ی Number را انتخاب نمایید.
اگر شیوه‌ی نمایش عدد مطلوب نیست و می‌خواهید ارقام پس از اعشار به شکل خاصی نمایش داده شود و همین‌طور می‌خواهید برای جدا کردن هر ۳ رقم، از نماد , استفاده شود، روی یکی از سلول‌های انتخاب شده راست‌کلیک کنید و گزینه‌ی Format Cells را انتخاب کنید.
- در تب Number می‌توانید تعداد ارقام پس از ممیز را از منوی Decimal places انتخاب کنید.
- در همان تب با تیک زدن گزینه‌ی Use 1000 Separator (,)، هر سه رقم با , جدا می‌شود که خوانایی اعداد بزرگ را بهبود می‌بخشد.
- می‌توانید شیوه‌ی نمایش اعداد منفی را با رنگ قرمز، با علامت منفی، با رنگ قرمز و علامت منفی انتخاب کنید.
در نهایت روی OK کلیک کنید.
روش بعدی استفاده از Paste Special است. در این روش به سلولی نیاز دارید که فرمت آن عددی یا Number باشد. می‌توانید فرمت این سلول را کپی کرده و روی سلول‌هایی که فرمت متنی دارند، پیست کنید. اما چطور این نوع خاص پیست کردن را انجام بدهیم؟
ابتدا سلولی از نوع عددی را انتخاب کرده و با کلید میانبر Ctrl + C یا با راست‌کلیک و انتخاب Copy، کپی کنید. سپس سلول‌های متنی موردنظر را انتخاب کنید و روی یکی از سلول‌ها راست‌کلیک کنید. از منوی راست‌کلیک گزینه‌ی Paste و سپس Paste Special را انتخاب کنید. در پنجره‌ی باز شده گزینه‌ی Format را انتخاب کنید و روی OK کلیک کنید. به این ترتیب فقط فرمت سلول کپی شده پیست می‌شود.
استخراج عدد یا متن از سلول‌های Excel
گاهی اوقات موضوع به سادگی اشتباه بودن نوع سلول نیست و ممکن است عدد در جملات موجود در سلول‌ها موجود باشد و بخواهید عددها را استخراج کنید. حالت دیگر استخراج بخشی از متن از جملات درون سلول‌ها است. خوشبختانه امکان تقسیم کردن یک سلول به چند کلمه و عدد به روش‌های مختلف وجود دارد. در ادامه چند راهکار جالب و ساده را معرفی می‌کنیم.
جدا کردن عدد از جملات
برای جدا کردن کلمات و اعداد موجود در جملات، می‌توانید از Text to Columns اکسل استفاده کنید که در واقع یک ستون حاوی داده را به چند ستون تبدیل می‌کند.
روش کار ساده است: ابتدا سلول‌ها را انتخاب کنید و سپس در تب Data نوار ابزار بالای صفحه، گزینه‌ی Text to Columns را انتخاب کنید. در پنجره‌ی باز شده مراحل را طی کنید و در مرحله‌ی انتخاب Delimiter یا کاراکتر جدا کننده، کاراکتر مناسب را انتخاب کنید که می‌تواند Space یا ویرگول یا هر کاراکتر دیگری باشد.
دقت کنید که اگر همه‌ی عددها از نظر تعداد رقم، یکسان باشند، می‌توانید به جای مشخص کردن کاراکتر جداکننده، از روش Fixed Width اقدام کنید و تعداد رقم‌ها را مشخص کنید.
نکته‌ی دیگر این است که با این روش می‌توانید سلول متنی را به چند سلول متنی تبدیل کنید! لذا در نهایت تبدیل کردن سلول متنی به سلولی از نوع عدد نیز باید انجام شود.
برای اطلاعات بیشتر در مورد ابزار Text to Columns اکسل به مقاله‌ی زیر توجه فرمایید:
پردازش جملات و جدا کردن با توابع RIGHT و LEFT و SEARCH و LEN
در نرم‌افزار Excel توابعی برای جدا کردن بخش‌های چپ یا راست یا میانی عبارت‌ها وجود دارد و می‌توانید تقریباً هر نوع پردازشی روی عبارت‌های متنی انجام دهید. به کارکرد دو تابع LEFT و SEARCH زیر توجه کنید:
- تابع LEFT یک یا چند کاراکتر سمت چپ عبارت را برمی‌گرداند و البته تعداد کاراکتر می‌تواند یک عدد ثابت یا خروجی یک تابع باشد.
- تابع RIGHT یک یا چند کاراکتر سمت راست عبارت را برمی‌گرداند و تعداد کاراکتر دلخواه است.
- تابع LEN طول عبارت یا در واقع تعداد کاراکترهای یک عبارت را برمی‌گرداند.
- تابع SEARCH برای پیدا کردن چند کاراکتر در یک عبارت و مشخص کردن موقعیت کاراکترها کاربرد دارد.
فرض کنید که در سلول‌های یک ستون، عددی در ابتدای جمله قرار دارد که تعداد رقم‌ها متنوع است. روشن است که می‌توان یک فرمول با ترکیبی از این دو تابع نوشت که موقعیت اولین اسپیس یا فاصله را جستجو کرده و بخش قبل از فاصله را برگرداند.
برای پیدا کردن موقعیت اولین اسپیس در سلول A1 از فرمول زیر استفاده می‌شود:
=SEARCH(" ", A1, 1)
و می‌توانید خروجی فرمول فوق را به تابع LEFT بدهید تا بخش سمت چپ اولین اسپیس را برگرداند:
=LEFT(A1, SEARCH(" ", A1, 1))
پس از نوشتن این فرمول، سلول حاوی فرمول را کپی کرده و در سلول‌های بعدی پیست کنید. به این ترتیب می‌توانید عدد ابتدای همه‌ی جملات ستون A را استخراج کنید.
تابع دیگری که معرفی کردیم یعنی RIGHT نیز می‌تواند بخش پس از اولین اسپیس را برگرداند. برای این کار می‌بایست طول جمله را محاسبه کرده و منهای تعداد کاراکترهای قبل از اولین اسپیس کنیم. بنابراین به فرمول زیر می‌رسیم:
=RIGHT(A1, LEN(A1)-SEARCH(" ", A1, 1))
به خروجی فرمول فوق در ستون G و H مثال ما توجه کنید:
اگر بخواهید در نهایت عبارت‌های متنی در سلول‌های مختلف را به هم بچسبانید، می‌توانید از تابع CONCATENATE استفاده کنید. به عنوان مثال برای یکی کردن عبارت موجود در سلول E1 و F1 می‌توان از فرمول زیر استفاده کرد:
=CONCATENATE(E1, F1)
البته بسته به نوع داده‌های سلول‌ها، ممکن است نیاز به پردازش‌های دیگری نیز باشد.
جدا کردن عددی چسبیده به عبارت متنی
اگر در انتها یا ابتدای عبارت متنی، یک عدد وجود دارد و بینشان فاصله دیده نمی‌شود، می‌توانید از دو فرمول زیر استفاده کنید تا اعداد ابتدا و انتها جدا شود. روش کار این است که عددهای موجود در ادامه‌ی جملات به کمک تابع SUBSTITUTE حذف می‌شود و تعداد کاراکترها محاسبه می‌شود و سپس عدد انتها یا ابتدا به کمک تابع RIGHT یا LEFT جدا می‌شود.
=LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))
=RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))
به نتیجه‌ی استفاده از این فرمول‌ها توجه کنید:
می‌توانید از یک روش دیگر نیز اقدام کنید. ابتدا با استفاده از تابع SEARCH موقعیت اولین رقم را با استفاده از تابع MIN و SEARCH پیدا کنید:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
و در ادامه از خروجی تابع فوق برای جدا کردن بخش سمت راست استفاده کنید:
=RIGHT(A1, LEN(A1)-B1+1)
جدا کردن اعداد از ابتدا و انتهای جمله
ممکن است جمله‌ای داشته باشید که در ابتدا و انتهای آن عدد موجود است و فاصله یا ویرگول و غیره بین عدد و دیگر کاراکترها درج نشده است. در این صورت برای جدا کردن هر دو عدد ابتدا و انتهای عبارت می‌توانید از فرمول‌های به مراتب پیچیده‌تر استفاده کنید.
به عنوان مثال اگر بخواهید اعداد موجود در عبارت 45t*&65/ جدا شود و به عدد 4565 برسید، می‌توانید از فرمول عجیب زیر استفاده کنید:
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
این فرمول از یک فروم کپی شده و شخصاً درکی از روش کار آن ندارم! مقصود من صرفاً این است که توانمندی‌ها و فرمول‌های پیچیده‌ای که در اکسل می‌توان نوشت را کم و بیش توصیف کنم.
makeuseofسیاره‌ی آی‌تی