آموزش تبدیل متن به عدد در اکسل (همه ی روش های موجود)

در این آموزش روش های مختلفی برای تبدیل متن به عدد در اکسل وجود دارد: استفاده از Convert to Number ، فرمول ها، عملیات ریاضی، paste special و موارد دیگر.

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

• کپی جدول در اکسل بدون تغییر (و همه ی نکات کاربردی)
• ۴۰ کلید میانبر در اکسل که سرعت کارتان را دو برابر می کنند

فهرست این مطلب

• نحوه شناسایی اعداد با فرمت متن
• نحوه تبدیل متن به عدد در اکسل
• ویژگی Convert to Number اکسل
• تبدیل متن به عدد با تغییر فرمت
• تغییر متن به عدد با Paste special
• تغییر متن به عدد با Text to Columns
• تبدیل متن به عدد با فرمول
• تبدیل متن به عدد با عملیات ریاضی

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

اکسل یک ویژگی بررسی خطای داخلی دارد که در مورد مشکلات احتمالی در مورد مقادیر سلولی به شما هشدار می دهد. این کار با یک مثلث سبز کوچک در گوشه سمت چپ بالای سلول ظاهر می شود. انتخاب سلول با نشانگر خطا، علامت احتیاط را با علامت تعجب زرد نشان می دهد (لطفا تصویر زیر را مشاهده کنید). نشانگر ماوس را روی علامت بگذارید تا اکسل در مورد اشکال احتمالی به شما اطلاع دهد : عدد موجود در این سلول به صورت متن قالب بندی شده یا قبل از آن علامت اپوستروف است.

در بعضی موارد، نشانگر خطا برای اعدادی که به صورت متن قالب بندی شده اند نشان داده نمی شود. اما شاخص های تصویری دیگری برای اعداد متنی نیز وجود دارد:

در تصویر زیر می توانید اعداد متنی را در سمت راست و اعداد واقعی را در سمت چپ مشاهده کنید:

• دانلود ۱۸ درس کاربردی آموزش نرم افزار اکسل (رایگان+جزوه)
• آموزش مدیریت فرمول ها در اکسل (Copy, Paste, Autofill)

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

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

تبدیل متن به عدد در اکسل با بررسی خطا

اگر سلول های اکسل یک نشانگر خطا (مثلث سبز در گوشه بالا و سمت چپ) را نشان می دهند، با دو کلیک رشته های متنی را به عدد تبدیل کنید:

۱- تمام سلول های شامل اعداد که به عنوان متن قالب بندی شده اند را انتخاب کنید.

۲- روی علامت هشدار کلیک کنید و گزینه Convert to Number را انتخاب کنید.

انجام شد!

تبدیل متن به عدد با تغییر فرمت سلول

راه سریع دیگر برای تبدیل مقادیر عددی که به صورت متن هستند، این است:

۱- سلول های شامل اعدادی که به عنوان متن قالب بندی شده اند را انتخاب کنید.

۲- در تب Home ، در group Number، از لیست کشویی Number Format ، گزینه General یا Number را انتخاب کنید.

نکته: این روش در بعضی سناریوها کار نمی کند. به عنوان مثال، اگر فرمت Text را بر روی یک سلول اعمال می کنید، یک عدد را وارد کنید و سپس فرمت سلول را به Number تغییر دهید، سلول با فرمت Text باقی می ماند.

تغییر متن به عدد با Paste special

در مقایسه با تکنیک های قبلی، این روش تبدیل متن به عدد به چند مرحله بیشتر نیاز دارد، اما تقریباً همیشه کار می کند.

برای تبدیل عدد های با فرمت متن با Paste special، مراحل زیر را طی کنید:

۱- سلول های شامل اعداد متنی را انتخاب کنید و فرمت آن ها را مطابق توضیحات بالا روی General تنظیم کنید.

۲- یک سلول خالی را کپی کنید. برای این کار یک سلول را انتخاب کرده و Ctrl + C را فشار داده یا راست کلیک کرده و از منو copy را انتخاب کنید.

۳- سلول هایی را که می خواهید به عدد تبدیل کنید، کلیک راست کرده و سپس بر روی Paste Special کلیک کنید. روش دیگر، میانبر Ctrl + Alt + V را فشار دهید.

۴- در کادر گفتگوی Paste Special ، Values ​​را در بخش Paste و در بخش Operation، Add را انتخاب کنید.

۵- OK را بزنید.

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

تبدیل متن به عدد با Text to Columns

این یک روش دیگر بدون فرمول برای تبدیل متن به عدد در اکسل است. هنگامی که برای اهداف دیگر استفاده می شود، به عنوان مثال برای تقسیم سلول ها، ویزارد Text to Columns یک فرآیند چند مرحله است. برای تبدیل متن به عدد، در اولین مرحله بر روی دکمه Finish کلیک کنید.

۱- سلول هایی را که می خواهید به عدد تبدیل کنید، انتخاب کنید و مطمئن شوید که فرمت آن ها روی General تنظیم شده است.

۲- به سربرگ Data، به گروه Data Tools بروید و بر روی دکمه Text to Columns کلیک کنید.

۳- در مرحله ۱ از Convert Text to Columns Wizard، گزینه Delimited را در زیر Original data type انتخاب کنید و روی گزینه Finish بزنید.

تبدیل متن به عدد با فرمول

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

فرمول ۱- تبدیل رشته متنی به عدد در اکسل

مایکروسافت اکسل برای تبدیل یک رشته متنی به عدد تابع VALUE را دارد. این تابع هم رشته هایی را که با علامت نقل قول بسته شده اند و هم مرجع سلول را که حاوی متن برای تبدیل به عدد باشد، می پذیرد.

تابع VALUE حتی می تواند عددی را که توسط برخی از کاراکتر های “اضافی” احاطه شده است، شناسایی کند – این کاری است که هیچ یک از روش های قبلی نمی توانند انجام دهند.

به عنوان مثال ، یک فرمول VALUE عددی را با نماد های ارز و هزار جداکننده، تشخیص می دهد:

=VALUE(“$1,000”)

=VALUE(A2)

برای تبدیل یک ستون از مقادیر متنی، فرمول را در سلول اول وارد می کنید و fill handle را می کشید تا فرمول را در زیر ستون کپی کنید:

فرمول ۲- تبدیل رشته متنی به تاریخ

به غیر از اعداد متنی، تابع VALUE می تواند تاریخ هایی را که به صورت رشته های متنی هستند تبدیل کند.

مثلا:

=VALUE(“1-Jan-2018”)

=VALUE(A2)

جایی که A2 شامل تاریخ متنی است.

به طور پیش فرض، یک فرمول VALUE یک شماره سریال را نشان می دهد که تاریخ است. برای اینکه نتیجه به عنوان یک تاریخ واقعی ظاهر شود فقط باید فرمت Date را در سلول فرمول اعمال کنید.

با استفاده از تابع DATEVALUE نتیجه مشابهی حاصل می شود:

=DATEVALUE(A2)

فرمول ۳- استخراج عدد از رشته متنی

تابع VALUE نیز وقتی با استفاده از یکی از توابع Text مانند LEFT ، RIGHT و MID ، عددی را از رشته متنی استخراج می کنید، مفید است.

به عنوان مثال ، برای بدست آوردن ۳ کاراکتر آخر از یک متن در A2 و برگرداندن نتیجه به عنوان عدد، از این فرمول استفاده کنید:

= VALUE (RIGHT (A2،۳))

تصویر زیر فرمول تبدیل متن به عدد را در عمل نشان می دهد:

اگر تابع RIGHT را در VALUE استفاده نکنید، نتیجه به صورت متن، دقیق تر یک رشته عددی برمی گردد، که محاسبات را با مقادیر استخراج شده غیرممکن می کند.

• آموزش کپی و چسباندن در اکسل (همه ی ترفندهای کاربردی)
• آموزش کامل spelling (غلط یاب املایی) در اکسل (تصویری)

تغییر رشته های متنی اکسل به عدد با انجام عملیات ریاضی

یک روش آسان تر برای تبدیل متن به عدد در اکسل، انجام یک عمل حساب ساده است که در واقع مقدار اصلی را تغییر نمی دهد. به عنوان مثال، اضافه کردن صفر، ضرب یا تقسیم بر ۱٫

=A2+0

=A2*1

=A2/1

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

نکته: اگر می خواهید نتایج را به عنوان مقادیر، و نه فرمول ها، داشته باشید از ویژگی special Paste برای جایگزینی فرمول ها با مقادیر آن ها استفاده کنید.

به این ترتیب که شما با فرمول ها و ویژگی های درونی، متن را به عدد در اکسل تبدیل می کنید.