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

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

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

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

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

برای تقسیم رشته در اکسل، می بایست از توابع 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 عدد استخراج شده است که در تصویر زیر نیز نشان داده شده است:

تفکیک سلول ها بر اساس رشته

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

برای مثال، برای تقسیم یک جمله بر اساس کلمات ربط “and”  و “or” گروه Split by strings را باز کنید، و هر کدام از رشته های جداکننده را در یک سطر تایپ کنید.

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

نکته: کاراکترهای “or” و “and” میتوانند جزئی از کلمات دیگر باشند. برای مثال “orange” یا “Andalusia” . بنابراین حتماً قبل و بعد از این کاراکترها یک فاصله تایپ کنید تا از وقوع خطا جلوگیری شود.

حال به مثالی در مورد داده های واقعی میپردازیم. فرض کنید ستونی از داده را از یک منبع خارجی به ورک شیت خود منتقل کرده اید:

5.1.2016   12:20

5.2.2016  14:50

این فرمت برای اکسل قابل شناسایی نیست. در نتیجه هیچ یک از توابع تاریخ  اکسل این فرمت را شناسایی نمیکنند. برای تفکیک روز، ماه، سال، ساعت، دقیقه، در سلول های جداگانه، کاراکترهای زیر را در قسمت Split by strings وارد کنید:

• نقطه(.) برای تفکیک روز ، ماه و سال
• دو تقطه(:) برای تفکیک ساعت و دقیقه
• فاصله برای تفکیک تاریخ و زمان

دکمه split را بزنید و نتیجه را مشاهده کنید.

تفکیک سلول ها بر اساس الگو (mask)

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

این روش زمانی به کار برده میشود که میخواهید تعدادی از رشته های هم شکل را به عناصر و زیر رشته تقسیم کنید. مشکل اینجاست که در تمام تکرارهای جداکننده ها امکان تقسیم زیر رشته وجود ندارد و فقط در بعضی از تکرار(ها)ی این جداکننده ها امکان تفکیک رشته وجود دارد. مثال زیر این موضوع را به خوبی روشن خواهد کرد.

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

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

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

*ERROR:*Exception:*

که در آن علامت ستاره(*) نشاندهنده هر تعدادی از کاراکتر است.

دو نقطه(:) نیز بعنوان جداکننده در نظر گرفته شد زیرا نمیخواستیم بعنوان نتیجه در سلول قرار بگیرند.

حال، قسمت Split by mask را در پنجره Split Text باز کنید در Enter delimiters الگوی خود را وارد کنید و دکمه Split را بزنید:

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

نکته: تفکیک رشته بر اساس الگو نسبت به حروف بزرگ و کوچک حساس است. بنابراین حتماً کاراکترها را همان شکلی که هستند در الگو وارد کنید.

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

* *ERROR:*Exception:*

در این مورد الگو به افزونه دستور میدهد که رشته اصلی را در چهار قسمت قرار دهد:

• تمام کاراکترهای قبل از اولین فاصله
• کاراکترهای بین اولین فاصله و کلمه error
• متن های بین error و exception(کد خطا)
• هر چیزی که بعد از exception باشد

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