چهار روش تبدیل متن به عدد
تبدیل متن به عدد در اکسل
گاهی اوقات اعداد در اکسل با فرمت متنش نمایش داده میشن و خاصیت عددی ندارند که ممکنه هنگام استفاده این مقادیر در محاسبات و توابع دیگه، با خطا مواجه بشید. این موضوع در شرایط مختلفی رخ میده. مثلا وقتی داده ها را از یک نرم افزار دیگه وارد اکسل می کنیم، یا وقتی که از توابع متنی بر روی اعداد استفاده میکنیم، با این موضوع مواجه می شیم. علت ایجاد این مشکل اینه که اعداد به صورت متن در سلول قرار گرفته. پس برای حل این مشکل باید با روش های تبدیل متن به عدد ، دوباره به عدد تبدیل بشن.
در این بخش، به معرفی چهار روش متفاوت برای تبدیل متن به عدد میپردازیم.
- با استفاده از دستور Error Checking
- با استفاده از دستور text to columns
- با استفاده از دستور paste special
- با استفاده از توابع اکسل
اول- تبدیل متن به عدد با استفاده از دستور Error Checking
یکی از قابلیت های اکسل، امکان بررسی کردن خطا است که وجود محتوای نمایش متنی اعداد رو به شما تذکر میده. این قابلیت، طبق شکل ۱ به صورت یک مثلث کوچک رنگی (نشانگر خطا) در گوشه بالا سمت چپ سلول شما دیده خواهد شد.
شکل ۱- نشانگر خطا در سلول
اگر سلول شما این نشانگر خطا را نمایش بده، شما می تونید از قابلیت بررسی خطا برای تبدیل محتوای نمایش متنی اعداد به مقادیر عددی واقعی استفاده کنید.
برای اینکار مراحل زیر را انجام دهید:
- انتخاب سلول (یا سلولها) شامل مقادیری که می خواید به عدد تبدیل کنید.
با انجام این مرحله، یک نماد هشدار در بالای سلول(ها) ظاهر میشود. اگر ماوس را بر روی نماد هشدار نگه دارید، پیام هشدار به نمایش در می آید (شکل ۲).
شکل ۲- نمایش پیغام خطا
- مطابق شکل ۳ بر روی نماد هشدار کلیک کنید تا منوی بررسی خطا ظاهر بشه.
شکل ۳- مشاهده منو بررسی خطا
- گزینه Convert to Number را برای تبدیل مقادیر به اعداد انتخاب کنید.
برای انجام عملیات بالا، نیاز است مطمئن شوید که گزینه بررسی خطا برای اعداد ذخیره شده به عنوان متن، فعال است یا نه. برای بررسی این موضوضع از مسیر زیر،
File/ Excel Options/ Formulas/
مطمئن شوید که:
- در بخش Error Checking ، گزینه Enable background error checking و
- در بخش Error checking rules ، گزینه Number formatted as text or preceded by an apostrophe تیک خورده باشند.
دوم- تبدیل متن به عدد با استفاده از دستور Text to Columns
یکی از کاربردهای ابزار Text to Columns تبدیل فرمت انواع دادههای اکسلی است. برای استفاده از این ابزار مراحل زیر رو انجام بدید:
- بازه سلول (ها)ی مورد نظر برای تبدیل رو انتخاب کنید (نباید بیشتر از یک ستون باشد).
- در قسمت Data از نوار ابزار اکسل، گزینه Text to Columns رو انتخاب کنید.
با انجام این کار پنجره Convert Text to Columns Wizard باز می شه. در این پنجره:
- مطمئن بشید گزینه Delimited انتخاب شده باشه. سپس بر روی Next کلیک کنید.
- مطمئن بشید که هیچ کدام از گزینه های بخش Delimiter انتخاب نشده باشند، سپس بر روی Next کلیک کنید.
- حالا باید یک فرمت برای ستون داده های خود انتخاب کنید. پس گزینه General را انتخاب و بر روی دکمه Finish کلیک کنید.
شکل۴- تبدیل متن به عدد با استفاده از Text to Columns
سوم- با استفاده از دستور Paste Special
شما میتوانید از دستور Paste Special برای این تبدیل در بازه سلولهای انتخابی خود استفاده کنید.
روشی که در زیر توصیف شده است، مقدار صفر را به هر سلول در بازه انتخاب شده اضافه میکند. این روند، نمایش متنی اعداد را به مقادیر عددی واقعی تبدیل میکند، اما هر سلول که شامل متن غیر عددی است را بدون تغییر باقی میگذارد. برای این کار مراحل زیر رو انجام بدید:
- در یکی از سلول های صفحه گسترده اکسل عدد صفر را وارد کنید.
- سلولی را که شامل صفر است انتخاب کرده و این سلول را کپی کنید.
- سلول (ها) ی مقصد که جنس عدد نیستند و میخواید تبدیل به عدد بشن رو انتخاب کنید.
شکل ۵- مسیر Paste Special
- مطابق شکل ۵ از قسمت Home در نوار ابزار اکسل، گزینه Paste → Paste Special رو انتخاب کنید.
پنجره Paste Special همانند شکل ۶ ظاهر خواهد شد.
شکل ۶- پنجره Paste Special
- در پنجره Paste Special گزینه Add را در قسمت Operation انتخاب و سپس بر روی OK کلیک کنید.
کلید میانبر کپی Ctrl+C است.
کلید میانبر Paste Specialنیز ، Ctrl+Alt+V است.
چهارم- با استفاده از تابع عددی اکسل
تابع Value اکسل، مقادیر متنی را به عدد تبدیل میکند. این تابع هنگامی مفید است که بخواهید مقادیر عددی را از رشته های پیچیده متنی استخراج کنید.
مثالهایی از تابع Value
مثال ۱
ستون A از صفحه گسترده زیر شامل نمایش متنی اعداد است. برای تبدیل مقادیر متنی به عدد، از تابع Value اکسل، در ستون B صفحه گسترده استفاده می شه.
مثال ۲
در مثال زیر، از تابع Value اکسل به همراه تابع Left اکسل برای استخراج مقادیر عددی از رشته های متنی در سلول A1 استفاده شده است.
توجه داشته باشید، اگر متنی که قرار است به عنوان ورودی تابع عددی اکسل قرار بگیرد، قابلیت تبدیل به مقادیر عددی را نداشته باشد، تابع خطای #VALUE! Error را نمایش می دهد.
روش چهارم یا بعبارتی استخراج اعداد از رشته های متنی، با توجه به شرایط داده و الگوی قابل درک بین رشته های متنی بسیار متنوع و گاها پیشرفته و آرایه ای خواهد بود. پس این مثال نمونه ساده ای از استخراج عدد از یک عبارت متنی بود. برای حل حالت های پیشرفته تر و متنوع تر حتما باید روی توابع متنی تسلط کافی رو داشته باشیم.
سلام وقت بخیر …. من میخوام اعداد یک سطر با هم جمع کنم…ولی جمع اعداد برام نمیزنه و از طریق فرمت سلز هم تغییر فرمت اعداد زدم ولی باز تغییری پیدا نکرد میشه راهنمایی کنین ممنون….
درود
با تغییر فرمت به تنهایی به عدد تبدیل نمیشه
بعد از تغییر فرمت سل دوباره باید سلول فعال بشه و اینتر زده بشه
برای تبدیل جنس متن به عدد این مقاله رو مطالعه کنید
https://excelpedia.net/text-to-number/
سلام
یک پاسخ نامه با فرمت اکسل که جواب های آن شامل بسیار زیاد ، زیاد ، متوسط و … می باشد و بنده قصد تغییر مقادیر کیفی به کمی را دارم ، برای مثال برای تمامی جواب های بسیار زیاد مقدار ۹ و برای مقدار زیاد عدد۷ برای کلمه ی متوسط عدد ۵ را تعریف کنم.به طوری که نرم افزار به جای هر پاسخ کیفی مقدار عددی آن را جایگذین نماید.
درود
میتونید از تابع If یا ابزار Find & Replace استفاده کنید
سلام و وقت بخیر
من چندتا سلول دارم که میخوام جمع کنم ولی تو هر سلول هم عدد دارم هم حروف چکار باید بکنم
درود
باید تفکیک انجام بدید
حروف رو از اعداد جدا کنید. بسته به الگوی داده ها از روش های زیر میتونید استفاده کنید
یا با فرمول
یا با ابزار- flash fill– text to column
میتونید تفکیک کنید
سلام
وقتتون بخیر
بنده یک سری شماره کارت وارد اکسل می کنم و تبدیل به فرمول می شه!!
جالب اینکه رند (گرد) هم می شه
و بعد دیگه چون اون شماره کارت بجای رقم آخر ۰ قرار گرفته قابل استفاده نیست!!
چکار کنم که تبدیل به فرمول نشه؟
درود
چون شماره کارت ۱۶ رقم هست و اکسل ۱۵ رقم رو میپذیره و رقم های بعدی رو صفر میکنه
پس اول فرمت سلول رو روی تکست بذارید و بعد شماره کارت رو تایپ کنید
من با اکسل ۲۰۱۹ کار می کنم. یک سری دیتا از یک فایل text کپی و import می کنم. چند ستون عدد دارم، هر روشی رو امتحان کردم نتونستم فرمت اونها رو از استرینگ به عدد تبدیل کنم. به نظرتون مشکل از کجاست؟
درود
اگر روش های بالا هم جواب نداده، احتمالا کاراکتر اضافه دارید
len بگیرید ببینید با چیزی که میبینید یکیه؟
اگه نبود باید دنبال اون کاراکتر اضافه که اصولا بدقلق هم هست در حذف کردن باشید
سلام: خیلی ممنون خانم خاکزاد
باسلام وعرض خداقوت : من از پرینت حساب بانک شماره کارت هارا را با استفاده از تابع findوmidدر یک ستون جدا کردم حال می خواهم با استفاده از تابع indexوmatchبه این سلول ارجاع دهم ومبلغ واریزی را دربیاورم حال خطا می دهد فکر کنم چون اطلاعات ستونی که با استفاده از تابع mid وfindبدست آورده ام عدد نیست خطا می دهد میشه راهنمایی بفرمائید . با تشکر
درود
دقیقا علت همین هست
باید مقصد و مبدا جنس مشابه داشته باشن
یا هر دو عدد یا هر دو متن
با روش های بالا میتونید به عدد یا متن تبدیل کنید
اما اگر شماره کارت جدا کردید (که ۱۶ رقمه) دقت کنید که نباید عددی بشه. رقم ۱۶ام از بین میره.
هر دو رو متنی کنید
خیلی متشکرم
البته یه راه مشابه هم مانند مرحله ۳ هست که اون سلولی که میخواهیم تبدیل کنیم رو انتخاب و کپی میکنیم بعد جای دیگه Paste Special و تو قسمت paste گزینه values رو انتخاب میکنیم و operations هم همون Add و بعد هم OK
عمل نکرد هیچ کدوم از روش ها
تقریبا میشه گفت امکان نداره
یا روش رو به درستی اجرا نکردید، یا مشکل چیز دیکه ای هست
به هر حال میتونید فایلتون رو در گروه تلگرامی بذارید تا دوستان بررسی کنند
درود بر شما
ممکنه راهنمائی بفرمایید که توابع زیر رو چطور میشه در یک سل نوشت.
الان در حال حاضر در دو سل جداگانه نوشته شده
=INDEX(DO_Tbl,MATCH(AC,C6:C54,0),MATCH(Cw,W5:AK5,0))
=IF(Pantry=”A”,DO+0.3,IF(Pantry=”Z”,DO-1.2,DO))
درود
باید سوالتون و توضیح بدید!!!
اگر منظورتون چسباندن نتیجه دو تابع به هم هست از & استفاده کنید!