جدا کردن عدد از متن در اکسل
اگر در سلول های اکسل مقادیر عددی را به همراه متن نوشته باشید (مانند واحد پول و …) و بخواهید عدد را از متن جدا کنید چه کار می کنید؟ با ما همراه باشید تا ببینید چگونه با روش های مطمئنی اعداد موجود در یک رشته متنی را از آن استخراج کنیم.
جدا کردن عدد از متن با Flash Fill
Flash Fill در اکسل ابزاری است که از نسخه ۲۰۱۳ به بعد در اکسل گنجانده شده است. یکی از کاربرد های این ابزار استخراج مقادیر عددی از درون متن است.
بیایید آموزش را با یک مثال کاربردی دنبال کنیم. داده های مثال زیر نام و نام خانوادگی کارمندان یک سازمان به همراه شماره کارمندی آن ها است. همانطور که مشاهده می کنید اسامی افراد و شماره کارمندی آن ها با هم به شکل نامرتبی ترکیب شده است. ما قصد داریم شماره کارمندی هر کارمند را در یک ستون جداگانه بنویسیم. به این منظور از قابلیت Flash Fill اکسل استفاده می کنیم.
بنابراین مانند تصویر زیر در اولین سلول شماره کارمندی اولین کارمند را می نویسیم.
سپس از سه روش زیر می توانیم اعداد را از متن جدا کنیم:
روش اول: از تب Data، ابزار Flash Fill را انتخاب کنیم.
روش دوم: روی نقطه مشخص شده در تصویر زیر دوبار کلیک می کنیم یا آن را Drag می کنیم.
مشاهده می کنید که شماره کارمندی اول برای همه کارمندان کپی شد. سپس از کادر مربعی کوچک که در کنار سلول نمایان است گزینه Flash Fill را انتخاب می کنیم.
روش سوم: کلیدهای ترکیبی Ctrl + E را فشار می دهیم.
در هر سه روش فوق اکسل به شکل فوق العاده ای، هدف ما را تشخیص داده و بقیه سلول ها را با سرعت پر می کند. دقت کنید که در این مثال فاصله ها و ویرگول ها هم مرتب نیستند و باز هم اکسل الگو را درست تشخیص می دهد.
جدا کردن عدد از متن با تابع MID
اگر ما بخواهیم تعدادی کاراکتر از وسط یک عبارت جدا کنیم می توانیم از تابع MID اکسل استفاده کنیم.
قاعده کلی تابع MID اکسل به صورت زیر است:
Text: در این آرگومان عبارتی که می خواهیم عدد مورد نظر خوذ را از آن جدا کنیم مشخص می کنیم. این پارامتر می تواند یک عدد، یک متن یا یک فرمول باشد.
Start_num: این آرگومان مشخص می کند که می خواهیم جداسازی از چندمین کاراکتر شروع شود. این پارامتر باید بزرگتر یا مساوی 1 باشد. اگر این پارامتر رو برابر 1 قرار بدهیم جداسازی از اولین کاراکتر موجود در متن شروع می شود. اگر مقدار این پارامتر رو بزرگتر از تعداد حروف موجود در پارامتر اول تابع تعیین کنیم، یک عبارت خالی به عنوان نتیجه تابع برگردانده می شود.
Num_chars: این آرگومان مشخص می کند که با آغاز از نقطه شروع مشخص شده در پارامتر دوم چند رقم باید از متن اصلی جدا شود.
مثالی از تابع MID اکسل
جدول زیر را در نظر بگیرید:
می خواهیم با تابع MID اکسل در ستون چهارم ماه عرضه هر سهم در بازار بورس را با استفاده از داده های ستون تاریخ عرضه بدست آوریم. برای اینکار فرمول را در سلول D2 می نویسیم و بعد آن را در سلول های زیرین کپی می کنیم.
- اولین پارامتر تابع MID اکسل که می خواهیم با جدا کردن بخشی از اطلاعات آن به ماه عرضه برسیم در ستون دوم قرار دارد. بنابراین پارامتر اول تابع را برابر C2 قرار می دهیم.
- ماه عرضه، کاراکترهای ششم و هفتم از سمت چپ هستند. در واقع ماه عرضه از ششمین کاراکتر شروع می شود پس پارامتر دوم تابع MID اکسل را برابر 6 قرار می دهیم.
- طول بخشی که مشخص کننده ماه عرضه است دو کاراکتر می باشد. بنابراین پارامتر سوم را برابر 2 قرار می دهیم تا هم کاراکتر ششم و هم کاراکتر هفتم از عبارت ما جدا شوند.
بنابراین تابع به شکل زیر نوشته می شود:
خروجی نهایی فرمول ما در شکل زیر نشان داده شده است.
درباره آمنه نوروزیان
دانش آموخته علم ژنتیک و بیوتکنولوژی دانشگاه تهران، علاقه مند به زیست محاسباتی و یادگیری مطالب جدید
نوشته های بیشتر از آمنه نوروزیان
سلام به نکته جالبی توجه کردید که خیلی کار راه اندازه ممنون از شما
چه عالی، ممنون از همراهیتون
خيلي خوب بود كارمو راه انداخت ممنون واقعا
سلام در اکسل 2010 ، گزینه flash fill وجود ندارد چطور اعداد را جدا کنم
سپاس
بستگی به شکل داده های شما دارد. اگر اعداد به شکل منظم و مناسبی ذخیره شده اند از توابع Mid, len, search باید استفاده کنید. در غیر اینصورت راه نسبتا طولانی دارد.
با سلام خیلی عالی بود من مدتها بود دنبال این قابلیت اکسل میگشتم خیلی بهم کمک کردید خدا بهتون خیر بده.
با تشکر از شما
خیلی پر کاربرد بود ممنون
خداراشکر. خواهش می کنم.
خیلی خیلی عالی
خوب این فایه نداره که چطوری باید اعداد رو از سلول های حذفی حذف کنیم؟ چون دیگه بعد جداسازی لازم نیست تو سلول اصلی باشن
معمولا در مسال های داده ای منظور از جدا سازی استخراج هست ولی اگر می خواهید عدد از متن کم شود می توانید از تابع substitute بعد از این مرحله استفاده کنید.
فدااااااایی دارید