آیا در اکسل میتوان عدد را از متن جدا کرد؟
بله – با کمک روشهای مختلفی که در این مطلب ذکر شده است میتوان در اکسل عدد را از متن جدا نمود
آیا جدا کردن عدد از متن بهم چسبیده در اکسل ممکن است؟
بله با کمک text to column این کار قابل انجام است. مراحل ان را در متن ببینید
نحوه جداسازی عدد از متن در داده های بدون ساختار چگونه است؟
روش جدا کردن عدد از متن برای داده های با ساختار منظم و بدون ساختار منظم در متن ارائه شده است
جدا کردن عدد از متن در اکسل یکی از دغدغههای بزرگ بسیاری از حسابداران است. تفکیک رشتههای ترکیبی اعداد و متن که به دلایل متفاوتی نظیر انتقال اطلاعات از سایر نرم افزارها به نرم افزار اکسل ایجاد شدهاند، مشکلاتی برای آنان ایجاد میکند. از این رو در این مقاله آموزش رایگان حسابداری قصد داریم شما را با چندین روش برای جدا کردن عدد از متن در اکسل به صورت تصویری آشنا نماییم. همراه ما بمانید.
تکنیک های جدا کردن عدد از متن در اکسل
برای جدا سازی عدد از متن در اکسل بر اساس ساختار رشته موجود در اکسل، روشهای مختلفی وجود دارد. به این ترتیب میتوان روشهای جدا کردن عدد از متن در اکسل را به دو دسته کلی زیر تقسیم نمود:
- دادهها ساختار نسبتا منظمی دارند
- دادهها هیچ گونه ساختاری ندارند
فرمول جدا کردن عدد از متن در اکسل برای داده های با ساختار نسبتا منظم
گاهی دادهها بر اساس ساختار نسبتا منظمی داخل سلول قرار گرفتهاند؛ به عنوان مثال بین هر دو مقدار (اعم از اعداد و حروف) جدا کنندهای مانند فاصله، ویرگول (،)، نقطه ویرگول (؛) قرار دارد، اعداد به دنبال کلمه خاصی درج شدهاند و یا شماره کارکتر درج اعداد مشخص باشد. در چنین مواردی استفاده از ابزارها و یا توابع مختلفی برای تفکیک عدد از متن در اکسل مفید هستند. برخی از این ابزارها عبارتند از:
- استفاده از ابزار Flash Fill
- استفاده از ترکیب توابع نظیر Find، MID، Right، Left
- استفاده از ابزار Text To Columns
توصیه میکنیم بخوانید: راهکارهای تبدیل عدد به حروف در اکسل
جدا کردن عدد از متن در اکسل با کمک Flash Fill
یکی از ابزارهای کاربردی برای تفکیک اعداد از متن ابزار Flash Fill است. این ابزار معمولا برای زمانی که تنها یک مقدار عددی در بین رشتهای از حروف قرار دارد، مورد استفاده قرار میگیرد. روش کار به این صورت است:
مرحله 1: دادهها را به نرم افزار اکسل منتقل نمایید.
مرحله 2: عدد موجود در اولین رشته ترکیبی را در ستونی که قصد دارید بخش عددی درج شود، وارد نمایید و با کشیدن نشانگر تا انتهای ستون آن عدد را کپی کنید.
مرحله 3: روی آیکن نمایش داده شده در گوشه ستون کلیک نموده و عبارت Flash Fill را انتخاب نمایید.
بدین ترتیب اکسل به صورت خودکار مقدار عددی موجود در هر رشته را برمیگرداند.
! نکته: استفاده از ابزار Flash Fill تنها برای اکسل 2013 و بالاتر امکان پذیر است.
جدا کردن عدد از متن در اکسل با استفاده از ترکیب توابع نظیر Find ،MID ،MIN ،Right ،Left
استفاده از ابزار Flash Fill برای مواردی که اعداد به صورت نامنظم در رشته چیده شده و یا با جداکنندههای متفاوتی جدا شدهاند، مناسب نیست. همچنین در برخی موارد جدا سازی بیش از یک رشته عددی از متن با کمک این ابزار با مشکل مواجه میشود.
در چنین مواقعی در صورتی که رشته ترکیبی ما دارای ساختار منظمی باشد، به صورتی که با کمک استفاده از توابع در اکسل بتوان برای آنها یک ترتیب منطقی یافت (به عنوان مثال اعداد از کارکتر 6 به بعد در کلیه رشتهها قرار گرفته باشند) میتوانیم از توابعی نظیر Find، MID، Right، Left و یا ترکیب آنها استفاده نماییم.
به عنوان مثال در رشته دادههای فوق، با استفاده از یکی از توابع زیر میتوانیم مقادیر عددی را از رشته حروف تفکیک نماییم.
= Right(A1,6)
=MID(A1,10,6)
همچنین میتوانید از ترکیب توابعی مانند تابع Find و MID استفاده نمایید.
! نکته: یکی از بزرگترین مشکلات استفاده از توابع و ترکیب آنها، الزام به وجود نظم نسبی در رشتههای ترکیبی است. به عنوان مثال، در صورتی که تعداد ارقام در رشته ترکیبی برای تابع Find مشخص نباشد، این تابع کار نخواهد کرد.
پیشنهاد میکنیم بخوانید: نحوه استفاده از کلیدهای میانبر در اکسل
جدا کردن عدد از متن در اکسل با استفاده از ابزار Text to Columns
یکی از ابزارهایی که برای جدا کردن عدد از متن در اکسل مورد استفاده قرار میگیرد، ابزار Text to Columns از منوی Data است. با کمک این ابزار میتوان دادهها را بر اساس یک جداکننده در ستونهای مجزایی تفکیک نمود. در ادامه نحوه استفاده از دستور text to column را به دو روش برای شما ارائه مینماییم.
- روش اول استفاده از ابزار Text to Columns
در این روش اعداد از حروف با یک جداکننده یکسان، جدا شدهاند. مراحل جدا کردن عدد از متن در اکسل در این روش به صورت زیر است:
مرحله 1: دادهها را به نرم افزار اکسل منتقل نمایید.
مرحله 2: پس از انتخاب ستون مورد نظر، از بخش Data گزینه Text to Columns را انتخاب نمایید.
مرحله 3: بر اساس نحوه جدا شدن دادهها، گزینه مناسب را انتخاب نمایید.
مرحله 4: نوع جداکننده را بر اساس جداکننده موجود بین دادهها انتخاب نمایید.
مرحله 5: ساختار هر ستون را انتخاب نمایید.
! نکته: بهتر است در صورتی که ترتیب قرار گرفتن دادهها منظم نیست، از ساختار پیش فرض (General) استفاده نمایید.
مرحله 6: با انتخاب گزینه finish، دادهها در ستونهای مجزایی تفکیک میشوند.
- روش دوم استفاده از ابزار Text to Columns
این روش برای حالتی که بین اعداد و حروف هیچ جداکنندهای وجود ندارد، کاربرد دارد. مراحل تفکیک اعداد از رشته متنی در این روش عبارتند از:
مرحله 1: دادهها را به نرم افزار اکسل منتقل نمایید.
مرحله 2: پس از انتخاب ستون مورد نظر، از بخش Data گزینه Text to Columns را انتخاب نمایید.
مرحله 3: بر اساس نحوه جدا شدن دادهها، گزینه مناسب را انتخاب نمایید.
مرحله 4: محل جدا شدن را با کمک خط نشانگر تعیین نمایید.
مرحله 5: ساختار هر ستون را انتخاب نمایید.
! نکته: بهتر است در صورتی که ترتیب قرار گرفتن دادهها منظم نیست، از ساختار پیش فرض (General) استفاده نمایید.
مرحله 6: با انتخاب گزینه finish، دادهها در ستونهای مجزایی تفکیک میشوند.
! نکته: همان گونه که مشاهده میکنید، در صورتی که قرار گرفتن دادهها در رشته ترکیبی، ترتیب منظمی نداشته باشد، استفاده از این گزینه کارایی چندانی نخواهد داشت.
برای یادگیری بهتر استفاده از دستور text to column میتوانید ویدیوی آموزش استفاده از این دستور را در مطلب “آموزش text to column در اکسل” مشاهده نمایید.
حسابداران بخوانند:
آشنایی با نحوه اجرای اکسل در حسابداری، یکی از ضروریترین مهارتها برای کلیه حسابداران است؛ زیرا آموزش توابع در اکسل و استفاده از این نرم افزار از یک سو باعث افزایش سرعت و دقت در عملکرد میشود و از سوی دیگر، بسیاری از نرم افزارها و سامانهها قابلیت فراخوانی اطلاعات از اکسل را دارا هستند.
این فرصت را از دست ندهید.
فرمول جدا کردن عدد از متن در اکسل برای داده هایی که هیچ گونه ساختاری ندارند
در این حالت برای تفکیک اعداد از متن دو روش وجود دارد:
- استفاده از ترکیب توابع پیچیده نظیر ترکیب توابع برداری
- تفکیک اعداد از حروف با کمک VBA
تفکیک اعداد از حروف با استفاده از ترکیب توابع پیچیده
برای جدا کردن عدد از متن در اکسل از توابع پیچیده و یا ترکیبات پیچیدهتری از توابع نیز میتوان استفاده نمود. از جمله این توابع عبارتند از ترکیب توابع ROW ،IFERROR ، INDIRECT و TEXTJOIN. ترکیب این توابع به صورت یک بردار برای تفکیک اعدادی که بدون هیچ نظمی در رشته ترکیبی قرار دارند، کاربرد دارد.
! نکته: لازم است بدانید استفاده از تابع TEXTJOIN تنها برای اکسل 2019 و بالاتر امکان پذیر است.
تفکیک اعداد از حروف در اکسل با کمک VBA
تفکیک عدد از متن در اکسل با کمک ابزارها و یا توابع، در صورتی که با ویژگیهای توابع آشنا باشید، به سادگی امکان پذیر است. اما این کار با دو مشکل اصلی مواجه است:
- گاهی این کار مستلزم ترکیب فرمولی از توابع پیچیده و گیج کننده است
- ممکن است ترکیب توابع در برخی موارد برای تفکیک رشتههای عدد و متنی، موثر نباشد
برای حل این مشکلات، میتوان از قابلیت برنامه نویسی در اکسل استفاده نمود. کدنویسی این امکان را به شما میدهد که ارقام را با هر تعداد و از هر کجای رشته ترکیبی تفکیک نمایید.
برای این کار باید به بخش VBA (برنامه ویژوال بیسیک) در نرم افزار اکسل وارد شده و بر اساس قوانین کدنویسی در این نرم افزار نسبت به ثبت کدهای مناسب جهت تفکیک عدد از متن اقدام نمایید.
مراحل انجام این کار عبارتند از:
- با کمک دکمههای ترکیبی Alt و F11 وارد بخش کدنویسی اکسل (ویژوال بیسیک) شوید
- از منوی Insert گزینه Module را انتخاب نمایید
- کد مورد نظر خود را درج و اجرا نمایید
کلام آخر
جدا کردن عدد از متن در اکسل یکی از فرآیندهای زمانبر و ناخوشایند برای بسیاری از حسابداران است. در این نوشتار چندین روش برای ساده سازی تفکیک اعداد از متون ارائه نمودیم. نکته قابل توجه این است که تفکیک اعداد از حروف اولین قدم در تجزیه و تحلیل دادههای حسابداری است. کسب مهارت در ثبت و تحلیل دادهها در زمان مناسب به صورتی که برای تصمیم گیری بهتر به مدیران کمک نماید، در سایه کسب آموزش حسابداری جامع و کاربردی امکان پذیر است. پرشین حساب این فرصت را برای کلیه حسابداران فراهم نموده است تا با کمک آموزش جامع حسابداری، نقش موثری در بهبود عملکرد فعالیتهای اقتصادی ایفا نمایند.