جداسازی رشته های متنی نامنظم با استفاده از VBA:

جداسازی رشته های متنی نامنظم با استفاده از VBA اکسل

فرض کنید از نرم افزار نت (نگهداری و تعمیرات) واحد خودتان، مورد تجهیزات استقرار یافته در قالب فایل اکسل خروجی گرفته اید. اکنون محدوده ای با ۳۰۰۰۰ ردیف اکسل و یک ستون اکسل خروجی شما است. و وضعیت رشته های متنی شما در این ۳۰۰۰۰ ردیف، این گونه است که، تمامی رشته های متنی شما در تمامی سلول ها دارای سه کاراکتر ” – “ است.

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

شما به تمامی رشته های متنی هر سلول از این ۳۰۰۰۰ ردیف نیاز ندارید و می خواهید، فقط واژه ای که بین کاراکتر ” – ” دوم و ” – ” سوم است رو داشته باشید و به جداسازی متن در اکسل به صورت کاملا شخصی سازی شده، بپردازید(-،/،*،_ و… فرقی ندارند.) .

اکنون شرایط به نحوی است که ما نمیدانیم با گذر از چند کاراکتر به علامت ” – ” دوم و حتی علامت ” – ” سوم می رسیم ! مورد بعدی اینکه، نمیدانیم واژه ای که بین کاراکتر دوم” – ” و کاراکتر سوم” – ” (محل استقرار) است چند کاراکتر دارد که بخواهیم به جداسازی متن در اکسل بپردازیم.!

به منظور جداسازی متن در اکسل،  ابزار ها و حتی روش های متفاوتی است.  اما با توجه به صورت مسئله ای که در بالا خدمت شما توضیح داده شد. در این شرایط امکان جداسازی متن در اکسل با ابزار ها و روش ها ممکن نیست و بایستی به کد نویسی VBA در اکسل بپردازیم.

اما از کدنویسی شیرین و ساده VBA نترسید، شما با همین آموزش، کدنویسی را شروع و VBA را یاد بگیرید. پیش نیاز این آموزش  و به منظور یادگیری جداسازی متن با استفاده از VBA دو حلقه اصلی For Each-Next و  For-Next است که آموزش اینها رو به ترتیب از اینجا و از اینجا ببینید.

شروع کدنویسی VBA و جداسازی قسمتی از متن سلول ها:

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

در ابتدا یک حلقه For Each تشکیل می دهیم از نوع محدوده یا Range با نام دلخواه Rng. این حلقه به منظور حرکت روی یکایک سلول های مورد نظر ما که قصد جداسازی واژه بین کاراکتر ” – ” دوم و ” – ” سوم (محل استقرار) آن را داریم، ایجاد می شود. نوشتن این حلقه هر بار، موجب میشود که یکی از سلول های A2:A6 برای ادامه عملیات انتخاب شود. (۱) سپس دو ظرف یا متغیر با نام های Num2_of_character و Num3_of_character ایجاد کرده ایم که درون هردوی آنها تعداد کاراکتر های سلولی که حلقه بر میگزیند قرار داده میشود. همچنین دو ظرف دیگر با نام های Num1 و Num2 تعریف و مقدار آنها را برابر صفر قرار داده ایم. (۲) در مرحله بعدی، حلقه For-Next تشکیل داده ایم که مشخص کرده ایم از عدد ۱ تا تعداد کاراکتر آن سلول (۳) خب حالا با استفاده از دستور IF گفته شده است که اگر ( ۱ , i ,سلول مورد بررسی و انتخاب شده توسط For Each )تابع MID = ” – “ شد و مقدار درون ظرف Num1<2 بود (۴)، آنگاه یکی به مقدار ظرف یا متغیر Num1 اضافه کن و درون ظرف Num1main، مقدار i یا همان شماره کاراکتری که برابر ” – “ شده است، قرار بده (۵) و مجدد حلقه را تا زمانی که که Num1<2 (کاراکتر دوم ” – ” پیدا شود.) به چرخش در بیاور.

نکته در مورد جداسازی متن ( ۱ , i , سلول مورد بررسی و انتخاب شده توسط For Each )تابع MID:

با استفاده از این تابع هر بار سلول مورد بررسی Rng در محدوده A2:A6 (ورودی اول تابع) انتخاب و سپس با چرخش i از کاراکتر ۱ تا تعداد کل کاراکتر که با تابع Len شناسایی شد (مقدار i – ورودی دوم تابع)یکی از کاراکتر ها انتخاب می شود.  همان کاراکتر (عدد ۱ – ورودی سوم) را خروجی میگیریم و بررسی میکنیم که آیا کاراکتر برابر ” – “ است یا نه؟!

این حلقه For-Next، تمامی حروف سلول را بررسی می کند، و اگر برابر ” – “ شد، آنگاه یک شرط دیگر را بررسی میکند که آیا مقدار درون ظرف Num1<2 است یا نه؟بار اول که کاراکتر انتخاب شده توسط تابع Mid برابر ” – ” شد و مقدار NUm1= 0  بوده است پس شروط IF برقرار است و یکی به مقدار Num1 اضافه می شود و شماره i که نشان دهنده شماره کاراکتری است که برابر ” – ” شده است را درون ظرف Num1main قرار می دهیم. دوباره سایر حروف بررسی می شوند و زمانی که برای بار دوم کاراکتر انتخاب شده توسط تابع Mid برابر ” – ” شد شرط اول تابع IF برقرار و شرط دوم هم برقرار است؛ چون NUm1 که تا الان مقدار ۱ داشته و کوچکتر از ۲ است. یکی به مقدار Num1 اضافه می شود و برابر ۲   میگردد. شماره i که نشان دهنده شماره کاراکتری است که برابر ” – ” شده است را مجددا درون ظرف Num1main قرار می دهیم. (پس Num1main، نشان میدهد که با گذر از چند کاراکتر به علامت ” – ” دوم می رسیم.) تا به اینجا، محل قرار گیری کاراکتر ” – ” دوم را شناسایی کرده ایم. در چرخش های بعدی حلقه For-Next، چون مقدار Num1 برابر ۲ است و شرط Num1<2 را رعایت نمیکند پس دیگر کاری صورت نمیگیرد تا به انتهای حلقه برسیم.)

اگر توجه کنید به همین روش محل کاراکتر سوم ” – ” مشخص شده است. (۶)

سپس با استفاده از ویژگی Offset نسبت به سلول مورد بررسی در جهت سطر پایین نمی آییم و در جهت ستون ، یک ستون به جلو حرکت کرده و به سلول کنار سلول مورد بررسی، می رسیم. اکنون با دانستن محل کاراکتر دوم ” – ” و کارامتر سوم ” – “ به راحتی با استفاده از تابع جداسازی متن در اکسل (Mid) واژه بین کاراکتر دوم ” – ” و کارامتر سوم ” – “ را بیرون می کشیم.

جداسازی رشته های متنی نامنظم با استفاده از VBA

نکته بسیار مهم: اجرای کد ها، هر بار با زدن دکمه F8 باعث می شود کدها به صورت خط به خط اجرا شوند و شما به روند اجرای کار بهتر پی ببرید.

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