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

سابقه بروزرسانی ها:
1400/07/20:
حل مسئله با استفاده از تابع TextJoin در اکسل ۲۰۱۹ به بالا

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

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

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

در ادامه میخواهیم نحوه جدا کردن عدد از متن بوسیله توابع متنی (با الگو) و همچنین جدا کردن همه اعداد یک رشته (بدون نیاز به الگو) رو بررسی کنیم:

جدا کردن عدد برای رشته هایی که الگوی مشخصی دارن

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

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

پیدا کردن کلمه فاکتور

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

جدا کردن عدد برای رشته هایی که الگوی مشخصی ندارن

در این روش همه اعداد موجود در یک سلول پشت سر هم قرار میگیرن. از فرمول نویسی آرایه ای استفاده شده و تابع Textjoin که از ورژن ۲۰۱۹ به بعد موجود هست. (برای ورژن های قبلی، فایل نمونه رو دانلود کنید و ببینید)

برای این کار باید تک تک حروف موجود در سلول رو  با ترکیب تابع mid و indirect(len)) از هم تفکیک کنیم و بعد هر کدوم رو چک کنیم که عدد هست یا نه. اگر عدد بود دوباره به هم بچسبونیم. اینطوری هر چی کاراکتر غیر عددی هست بینش حذف میشه. فرمول به شرح زیر هست:

=TEXTJOIN(“”, TRUE, IFERROR(MID(A2, ROW(INDIRECT( “1:”&LEN(A2))), 1) *1, “”))

فقط دقت داشته باشید که فمرول آرایه ای هست و حتما باید کلید ترکیبی Ctrl+Shift+Enter زده بشه تا کار کنه.

شرح فرمول

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

MID(A2, ROW(INDIRECT( “1:”&LEN(A2))), 1)

که نتیجه این قسمت بصورت زیر خواهد بود:

MID(A2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38}, 1)

اینجا نشون میده که ۳۸ کاراکتر در سلول وجود داره که هر بار یکیش تفکیک میشه. (اگر تایع MID رو نمیشناسید حتما مقاله رو بخونید)

بعد از اینکه تک تک این کاراکترها تفکیک شدن، مقدار هر کدوم رو در ۱ ضرب میکنیم. یعنی این قسمت فرمول:

MID(A2, ROW(INDIRECT( “1:”&LEN(A2))), 1) *1

حالا اگر اون کاراکتر عدد باشه، نتیجه عدده و اگه متن باشه نتیجه خطای Value. چرا چون ضرب عدد در متن معنی نداره.

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 5;1;1;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5;4;4;4;3;7;6;3;3;6}

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

حالا با یک iferror خطاها رو از بین میبریم و کاراکترهای باقیمانده که همون اعداد هستن رو با تابع Textjoin به هم میچسبونیم.

در انتهای آموزش فایلی قرار داده شده که بوسیله یک فرمول آرایه ای (مشابه فرمول بالا اما بدون استفاده از textjoin که قابل استفاده برای ورژن های قبل از ۲۰۱۹ هست) و مشابه فرمول بالا همه اعداد موجود در یک سلول رو جدا میکنه و کنار هم قرار میده. در واقع اگر از این فایل و فرمول بالا (همونطور که مشاهده کردید) روی داده های مثال بالا استفاده کنیم، عدد فاکتور و رسید رو کنار هم میذاره و بهمون میده. برای مواقعی که هیچ الگویی وجود نداره، میشه از این فرمول استفاده کرد. فقط به این نکته توجه داشته باشید که همه اعداد موجود در یک سلول رو استخراج میکنه و باید بدونید روی چه نوع داده هایی استفاده از این فایل بدرد میخوره.

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

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

دانلود فایل این آموزش

برای دانلود فایل این آموزش روی دکمه زیر کلیک کنید: