برگزیده های پرشین تولز

جدا کردن عدد از متن در اکسل

S.Cheraghi

کاربر تازه وارد
تاریخ عضویت
28 فوریه 2017
نوشته‌ها
48
لایک‌ها
4
جدا کردن قسمتی از یک عبارت در یک سلول یکی از رایج ترین مسائل در اکسل هست. خیلی وقت ها اطلاعات از نرم افزارهای دیگه وارد اکسلمیشن و برای همین همه اطلاعات به هم چسبیده و در یک سلول هستن. پس لازمه که که این اطلاعات از هم تفکیک بشن تا بشه ازشون استفاده لازم رو برد. جدا کردن عدد از متن جزو پرتکرارترین مسائل در این زمینه هست.

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

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

در ادامه میخواهیم نحوه جدا کردن عدد از متن بوسیله توابع متنی رو بررسی کنیم:

data.jpg
فرض کنید داده هایی مطابق شکل روبرو داریم. میخواهیم شماره فاکتور رو جدا کنیم. (دقت کنید Space بین همه کلمات وجود نداره. اگر داشت به راحتی از ابزار Text To Column استفاده میکردیم)

همونطور که میبینید الگوی موجود در این داده ها وجود کلمه فاکتور قبل از کد مورد نظر هست. پس اولین کاری که باید بکنیم اینکه که مکان آخرین حرف از کلمه فاکتور رو پیدا کنیم.

حتما بخوانید: نکات پیشرفته از فیلترها در اکسل
پیدا کردن کلمه فاکتور

برای پیدا کردن مکان کلمه فاکتور، از تابع Find استفاده میکنیم.

=FIND(“فاکتور”,A2)

خروجی این تابع مکان حرف “ف” رو به ما میده و چون کلمه “فاکتور” 6 حرف هست، خروجی این تابع رو با 6 جمع میکنیم که مکان آخرین حرف از کلمه “فاکتور” مشخص بشه.

=FIND(“فاکتور”,A2)+6

چون تعداد ارقام فاکتور، ثابت نیست، باید مکان دومین کلمه “شماره” رو هم پیدا کنیم. اگر مکان “ش” رو پیدا کنیم، فاصله بین “ش” و “ر” از فاکتور میشه کد مربوط به فاکتور. پس:

=FIND(“شماره”,A2,5)

چرا آرگومان سوم تابع Find رو 5 گذاشتیم؟

چون 2 تا کلمه شماره داریم و ما دومی رو لازم داریم. برای همین میگیم از پنجمین حرف به بعد شروع به جستجو کنه و شماره رو پیدا کنه. اینطوری، “شماره” اول رو کاری نداره.

تا اینجا مکان حرف “ر” از فاکتور و مکان “ش” از شماره دوم رو پیدا کردیم. کافیه از طریق تابع MID کد مربوط به فاکتور رو استخراج کنیم:

تابع MID از بین یک عبارت، قسمت خاصی رو استخراج میکنه. این تابع 3 آرگومان داره:

Text: عبارت مورد نظر که میخوایم قسمتی از اون رو جدا کنیم.

Start_Num: مکان اولین حرف از قسمتی که میخوایم جدا کنیم.

Num-Chars: تعداد حرفی که از Stat_Num به بعد قراره جدا بشه.

=MID (مکان حرف “ر”-مکان حرف “ش”,مکان حرف “ر”,سل مورد نظر)

=MID (A2,FIND(“فاکتور”,A2)+6,FIND(“شماره”,A2,5)-(FIND(“فاکتور”,A2)+6))

***حالا اگه بخوایم شماره رسید رو جدا کنیم چه فرمولی باید بنویسیم؟(با رعایت این فرض که تعداد رقم شماره رسید متغیر است)

نظرتون رو در ادامه همین پست در قسمت کامنت بنویسید

حتما بخوانید: مقایسه دو لیست در اکسل
Mid-Find.jpg


شکل 2- جدا کردن عدد از متن – فراخوانی شماره فاکتور از بین عبارت

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

فایل رو دانلود کنید و سعی کنید فرمول رو تحلیل کنید.

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

منبع آموزش:
جدا کردن عدد از متن در اکسل
 
بالا