فیلتر و جستجو در اکسل می تواند مانند یک بازی حدس باشد. توابع regex آن را تغییر می دهد. اکنون ، من می توانم دقیقاً آنچه را که به آن نیاز دارم – الگوهای پیچیده ، مسابقات جزئی یا استخراج داده های ساختاری – بدون شکستن عرق ، مشخص کنم.
پیوندهای سریع
- regex چیست؟
- جستجوی الگوها
- داده های اضافی با استفاده از Regex
- با استفاده از regex پیدا کنید و جایگزین کنید
- regex را با سایر توابع ترکیب کنید
خلاصه
- الگوهای Regex به استفاده از نمادها به جستجوی رشته های متن به طور کارآمد (به عنوان مثال ، ایمیل ، رقم ، توالی خاص) کمک می کند.
- اکسل توابع Regex از پیش تعریف شده (Regextest ، RegexExtract ، RegexReplace) را برای جستجو ، استخراج و جایگزینی داده ها ارائه می دهد.
- برای دستکاری و اعتبار سنجی داده های پیشرفته تر ، توابع Regex را با سایر توابع اکسل ترکیب کنید.
فیلتر و جستجو در اکسل می تواند مانند یک بازی حدس باشد. توابع regex آن را تغییر می دهد. اکنون ، من می توانم دقیقاً آنچه را که به آن نیاز دارم – الگوهای پیچیده ، مسابقات جزئی یا استخراج داده های ساختاری – بدون شکستن عرق ، مشخص کنم.
regex چیست؟
Regex نوعی الگویی است که برای جستجوی رشته متن یا دنباله کاراکترها برای یک مسابقه استفاده می شود. آیا تا به حال فکر کرده اید که چگونه وب سایت ها می توانند به شما بگویند که الگوی ایمیل که در صفحه ورود به سیستم وارد کرده اید نامعتبر است؟ این یک نمونه از الگوی Regex با استفاده از امضای ایمیل در محل کار است.
عبارات منظم منحصر به فرد برای اکسل نیستند-آنها در چندین ویرایشگر متن ، زبانهای برنامه نویسی ، ابزارهای خط فرمان ، IDE ها و حتی رقیب اکسل ، Google Sheets موجود هستند.
Regex ممکن است پیچیده به نظر برسد ، و اگر می خواهید از آن در تمام توان خود استفاده کنید ، اما نیازی به برنامه نویس برای استفاده مؤثر نیست. در بعضی موارد ، شما می توانید فقط با دانستن نحوه استفاده از چند نماد و الگوهای اساسی از بین بروید. من این راهنما را تا حد امکان ساده نگه می دارم تا بتوانید استفاده از آنها را شروع کنید.
در اینجا نمادهایی که در این راهنما استفاده خواهیم کرد وجود دارد:
نماد
شرح
طیف وسیعی از شخصیت ها را در براکت ها مشخص می کند.
با شروع یک رشته مطابقت داشته باشید.
پایان یک رشته را مطابقت دهید.
با هر شخصیتی غیر از شخصیت خط جدید مطابقت داشته باشید.
با 0 یا بیشتر شخصیت قبلی مطابقت داشته باشید.
1 یا بیشتر از شخصیت های قبلی را مطابقت دهید.
()
شخصیت های همسان را به یک گروه تبدیل کنید.
[]
با هر یک از شخصیت های داخل براکت مطابقت داشته باشید.
[^]
با هر شخصیتی که در داخل براکت ها نباشد مطابقت داشته باشید.
{n}
دقیقاً با مواردی از شخصیت قبلی مطابقت داشته باشید.
{n ،}
مطابقت n یا بیشتر از شخصیت های قبلی
الگوهای regex ساده که می توانید با این نمادها بسازید عبارتند از:
الگوی regex
شرح
[0-9]
با یک رقم از 0 تا 9 مطابقت داشته باشید
[a-za-z0-9]
این یک محدوده ترکیبی است که با یک شخصیت واحد از حروف کوچک A تا Z ، حروف بزرگ A تا Z و از 0 تا 9 مطابقت دارد.
^طرفدار
با هر رشته ای که با Pro شروع می شود مطابقت دهید.
[^$]
با هر شخصیتی که $ نباشد مطابقت داشته باشد.
(کنفرانس)
گروه الگوی را گروه کنید.
A {3 ،}
با 3 یا بیشتر وقایع دومی A (به عنوان مثال ، A ، AA یا AAA) مطابقت داشته باشید.
توابع Regex فرمول های اکسل از پیش تعریف شده هستند که می توانند برای تعریف الگویی برای جستجو و دستکاری رشته های متنی استفاده شوند. اکنون سه عملکرد Regex وجود دارد ، و ما می خواهیم نحوه استفاده جداگانه و با سایر کارکردها را بررسی کنیم.
جستجوی الگوها
اولین عملکردی که می خواهیم به آن نگاه کنیم Regextest است. این یک رشته متنی را که می خواهید برای جستجو و الگوی Regex استفاده کنید ، طول می کشد و از دومی برای یافتن مسابقه در قبلی استفاده می کند. این عملکرد درست یا نادرست باز می گردد.
نحو برای عملکرد Regextest به شرح زیر است:
REGEXTEST(string_to_search, regex_pattern_to_use, [case_senstivity])
دو پارامتر اول ، string_to_search و regex_pattern_to_use ، خود توضیحی هستند. پارامتر [CASE_SENSESITIVE] اختیاری است-هر چیزی که هنگام صحبت در مورد نحو اکسل در براکت های مربع قرار می گیرد اختیاری است-و بیانگر این است که آیا می خواهید جستجو حساس به مورد باشد (0) یا حساس به مورد (1). پیش فرض حساس به مورد است.
در مثال ما ، ما از Regextest استفاده خواهیم کرد تا ببینیم آیا کاربر با استفاده از فرمول زیر یک آدرس ایمیل معتبر وارد کرده است:
REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")
در اینجا ، ما در سلول B3 جستجو می کنیم تا ببینیم آیا با استفاده از الگوی Regex در زیر آدرس ایمیل دارد:
^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$
اگر فرمول را در سلول C3 قرار دهم و در سلول B3 john.doe@example.com را وارد کنم ، درست باز می گردد زیرا با امضای یک ایمیل مطابقت دارد.
داده های اضافی با استفاده از Regex
در مرحله بعد ، اجازه می دهیم به عملکرد REXEXTRACT نگاه کنیم. این بستر (بخشی از رشته) را که با الگوی Regex ارائه شده مطابقت دارد ، برمی گرداند.
نحو برای عملکرد rexextract به شرح زیر است:
REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])
در ادامه با مثال ایمیل ، بیایید یک فرمول در سلول B4 اضافه کنیم تا نام کاربری بخشی از ایمیل را استخراج کنیم.
در اینجا فرمول به نظر می رسد:
=REGEXEXTRACT(B3, "([^@]+)")
در این فرمول ، همه چیز را قبل از نماد @ در آدرس ایمیل وارد شده در B3 استخراج می کنیم.
با استفاده از regex پیدا کنید و جایگزین کنید
عملکرد نهایی regex که به آن خواهیم پرداخت ، RegexReplace است. این عملکرد مشابه عملکرد جایگزین اکسل است ، اما از Regex نیز پشتیبانی می کند. این رشته متن را که می خواهید اصلاح کنید و بررسی می کند ، می گیرد تا ببینید که آیا بستر وجود دارد که با الگوی Regex تعریف شده مطابقت داشته باشد یا خیر. اگر آن را پیدا کند ، آن را با رشته جایگزین ارائه شده جایگزین می کند.
نحو عملکرد RegexReplace به شرح زیر است:
REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_senstivity])
در اینجا پارامترهای مهم برای توجه به این عملکرد آورده شده است:
- string_to_modify: رشته متنی که می خواهید اصلاح کنید.
- replaction_string: رشته ای برای جایگزینی بستر با.
- NUMBER_OF_OCCURRENCS: نمونه دقیقی که می خواهید جایگزین کنید.
در اینجا نمونه ای از استفاده از تابع برای جایگزینی قسمت نام کاربری ایمیل با رشته متن دیگر آورده شده است:
=REGEXREPLACE(B3, "^[^@]+", "jane.doe")
مقدار B3 John.doe@example.com است و بعد از اینکه فرمول بالا را در سلول C3 وارد می کنیم ، jane.doe@example.com را برمی گرداند.
regex را با سایر توابع ترکیب کنید
همچنین می توانید توابع REGEX را با سایر توابع در اکسل ترکیب کنید. به عنوان مثال ، می توانید در صورت بیانیه ، عملکرد Regextest را با اکسل ترکیب کنید و پیام مناسب را بر اساس نتیجه نمایش دهید.
در اینجا یک فرمول مثال وجود دارد:
=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")
این فرمول از عبارت IF استفاده می کند تا بررسی کند که آیا آدرس ایمیل وارد شده در سلول B6 معتبر است و سپس نمایش این یک آدرس ایمیل معتبر است! اگر درست است یا آدرس ایمیل نامعتبر است! اگر نادرست است از طرف دیگر ، می توانید این کار را با عملکرد Find جفت کنید تا به سرعت داده ها را در اکسل پیدا کنید.
این به معنای راهنمای گسترده ای برای عبارات منظم نیست – که به تنهایی به چندین مقاله نیاز دارد. با این حال ، این یک روش خوب برای شروع استفاده از Regex در اکسل است. موارد استفاده و امکانات فقط با تخیل شما محدود می شود.