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

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

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

• نحوه تقسیم سلول ها در اکسل با استفاده از فرمول
• تقسیم رشته با استفاده از کاما، دو نقطه، ممیز، خط تیره و سایر جداکننده
• تقسیم رشته با استفاده از line break
• فرمول هایی برای تقسیم رشته به متن و عدد
• نحوه تفکیک سلول ها با استفاده از ویژگی split text

نحوه تقسیم متن در اکسل با استفاده از فرمول

برای تقسیم رشته در اکسل، می بایست از توابع right، left، mid در ترکیب با یکی از توابع find  و search استفاده کرد. در نگاه اول ممکن است بعضی از فرمول ها پیچیده به نظر برسد ولی منطق این توابع بسیار ساده است و مثال های در زیر آمده درک روشنی برای شما ایجاد خواهد کرد.

تقسیم رشته به وسیله کاما، ممیز، خط تیره و دیگر جداکننده ها

زمان تقسیم سلول در اکسل، نکته کلیدی جایی است که جداکننده را در میان سلول قرار میدهید. بسته به نیاز شما این کار میتواند توسط تابع Serach (عدم حساس به حروف بزرگ و کوچک) یا find (حساس به حروف بزرگ و کوچک) انجام شود. زمانی که جای جداکننده را پیدا کردید با استفاده از توابع right، left، mid، قسمت مربوطه را از متن استخراج کنید. برای درک بهتر به مثال زیر توجه کنید:

فرض کنید لیستی از  sku (واحد نگهداری موجودی) بر اساس الگوی آیتم-رنگ-اندازه  در اختیار دارید و میخواهید این ستون را به سه ستون جداگانه تفکیک کنید.

1. برای استخراج نام آیتم (تمام کاراکترها قبل از اولین خط تیره)، فرمول زیر را در سلول B2 قرار دهید، سپس آن را به سمت پایین در همان ستون کپی کنید.

=LEFT(A2, SEARCH("-",A2,1)-1)

در این فرمول، تابع serach محل اولین خط تیره را مشخص میکند و تابع left کاراکترهای سمت چپ آن را استخراج میکند (1 را از محل خط تیره کم میکنیم زیرا نمیخواهیم خط تیره هم برگردانده شود)

2. برای استخراج رنگ (تمام کاراکترها بین خط تیره اول و خط تیره دوم) فرمول زیر را در سلول C2 وارد کنید، سپس آن را به سلول های پایین کپی کنید:

=MID(A2, SEARCH("-",A2) + 1, SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)

همانطور که احتمالاً میدانید ترکیب تابع mid به شکل زیر است:

MID(text, start_num, num_chars)

Text: رشته ای که میخواهید متن مورد نظر را از آن استخراج کنید.

start_num: محل اولین کاراکتری که میخواهید استخراج کنید.

num_chars: تعداد کاراکتری که میخواهید استخراج کنید.

در فرمول بالا، متن از سلول A2 استخراج میشود، و دو پارامتر بعدی توسط 4 تابع مختلف search  محاسبه میشود:

start_num: محل اولین خط تیره به اضافه یک:

SEARCH("-",A2) + 1

num_chars: تفاوت محل دومین خط تیره با اولین خط تیره منهای 1:

SEARCH("-", A2, SEARCH("-",A2)+1) - SEARCH("-",A2) -1

برای استخراج اندازه(Size)، یعنی تمام کاراکترهای بعد از خط تیره سوم، فرمول زیر را در D2 قرار میدهیم :

=RIGHT(A2,LEN(A2) - SEARCH("-", A2, SEARCH("-", A2) + 1))

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

به همین صورت، میتوانید ستونهایی را که متن درون آنها توسط دیگر جداکننده تفکیک شده است را جداسازی کنید . فقط کافیست به جای “-”  در فرمول، ممیز، نقطه ویرگول فاصله یا هرچیز دیگری را قرار دهید.

نکته: در فرمول بالا، 1+ و 1- مربوط به تعداد کاراکتر جداکننده میشود.  در این مثال خط تیره یک کاراکتر داشت. در صورتی که جداکننده شما بیش از یک کاراکتر داشته باشد برای مثال یک فاصله و یک کاما، فقط کاما “,” را درون تابع search قرار دهید و از 2+ و 2- به جای 1+ . 1- استفاده کنید.

نحوه تقسیم رشته با استفاده از line break (قرار گرفتن در سطر بعدی در سلول یا همان alt+enter)

برای تقسیم متن با استفاده از فاصله میتوانید از فرمول هایی که در بالا ارائه شد استفاده کنید. تنها تفاوت اینجاست که می بایست از تابع char برای قرار دادن کاراکتر line break استفاده کنید زیرا نمیتوانید مستقیماً آن را در فرمول وارد کنید.

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

به جای خط تیره(-) در فرمول های بالا cahr(10) قرار دهید.10 کد ASCII برای تعویض خط است.

برای استخراج نام آیتم:

=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)

برای استخراج رنگ:

=MID(A2,SEARCH(CHAR(10),A2)+1,SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1) - SEARCH(CHAR(10),A2) - 1)

برای استخراج اندازه:

=RIGHT(A2,LEN(A2) - SEARCH(CHAR(10), A2, SEARCH(CHAR(10), A2) + 1))

و نتیجه شبیه به زیر خواهد شد:

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

برای شروع باید گفت که هیچ روش کلی در مورد رشته های الفبا عددی وجود ندارد. اینکه از چه فرمولی استفاده شود بستگی به الگوی رشته مورد نظر دارد. در زیر فرمول هایی برای سه سناریو متفاوت ارائه شده است:

تفکیک رشته “متن+عدد”

فرض کنید، ستونی دارید که در آن متن و عدد با هم ترکیب شده است و همیشه یک عدد بعد از متن قرارگرفته است. شما میخواهید رشته اصلی را تفکیک کرده و عدد و متن را در سلول های متفاوتی قرار دهید؛ شبیه تصویر زیر:

نتیجه ممکن است به دو روش مختلف به دست آید.

روش 1: شمارش ارقام و استخراج آن کاراکترها

آسان ترین راه برای جدا کردن رشته متن وقتی که اعداد بعد از متن می آیند، این است:

برای استخراج عدد از فرمول آرایه ای زیر استفاده کنید و کلیدهای Ctrl + Shift + Enter را فراموش نکنید:

=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))

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

=LEFT(A2,LEN(A2)-LEN(C2))

A2 رشته اصلی، C2 عدد اسخراج شده است که در تصویر زیر نیز نشان داده شده است:

روش 2: یک راه حل دیگر (فرمول غیر آرایه ای)

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

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))

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

زمانی که محل اولین عدد پیدا شد، میتوانید با استفاده از فرمول های ساده right و left به راحتی متن را از عدد تفکیک کنید.(به یاد داشته باشید که متن همیشه قبل از عدد می آید)

برای استخراج متن:

=LEFT(A2, B2-1)

برای استخراج عدد:

=RIGHT(B2, LEN(A1)-B2+1)

که A2 رشته اصلی و B2 محل اولین عدد است. تصویر زیر این فرمول را به صورت عملی نشان میدهد:

برای خلاص شدن از دست ستون کمکی که محل اولین عدد در آن قرار گرفته، میتوانید تابع min را در درون توابع right و left قرار دهید.

فرمول استخراج متن:

=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

فرمول استخراج عدد:

=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)

مثال2. تفکیک رشته الگوی “عدد+متن”

اگر میخواهید رشته ای را تفکیک کنید که در آن متن بعد از عدد قرار گرفته، میتوانید اعداد را با استفاده از فرمول آرایه زیر بدست آورید:

=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))

این فرمول شبیه به فرمول آرایه ای مثال بالاست با این تفاوت که از تابع left به جای Right استفاده می کنید زیرا در اینجا اعداد در سمت راست رشته هستند.

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

=RIGHT(A2,LEN(A2)-LEN(B2))

در فرمول های بالا، A2 رشته اصلی   و B2 عدد استخراج شده است که در تصویر زیر نیز نشان داده شده است:

از مطالعه این مطلب سپاسگذاریم . به امید دیدار