آموزش کامل استفاده از REGEX در اکسل برای مدیریت داده‌ها

عناوین منظم (یا REGEX) الگوهایی برای جستجو هستند که می‌توانند بررسی کنند آیا یک رشته متنی با یک الگوی خاص تطابق دارد یا خیر، همچنین برای استخراج یا جایگزینی رشته‌هایی که با الگو مطابقت دارند، استفاده می‌شوند. به دلیل پیچیدگی آنها، این مقاله خلاصه‌های ساده و مثال‌هایی از استفاده آنها در اکسل را ارائه می‌دهد.

توابع REGEX در اکسل

توابع REGEX برای کاربران اکسل در Microsoft 365 (ویندوز، مک) و همچنین نسخه وب اکسل در دسترس هستند.

چگونه از REGEXTEST استفاده کنیم؟

این تابع بررسی می‌کند آیا یک رشته متنی با یک الگوی خاص مطابقت دارد یا نه و نتیجه TRUE یا FALSE را برمی‌گرداند. این تابع برای بررسی اینکه داده‌ها از یک ساختار خاص پیروی می‌کنند، بسیار مفید است.

نحو (Syntax):  

REGEXTEST(a,b,c)

1. a (الزامی): متنی که می‌خواهید بررسی کنید.
2. b (الزامی): الگویی که برای بررسی استفاده می‌شود.
3. c (اختیاری): اگر 0 باشد بررسی حساس به حروف بزرگ و کوچک است، و اگر 1 باشد حساسیت ندارد.

مثال:

در این مثال، یک لیست از کدهای محصول داریم که باید ساختاری مشخص داشته باشند:

یک کد معتبر شامل:

1. اندازه محصول (مثل “xs” برای خیلی کوچک، “s” برای کوچک، و …)،
2. یک یا دو عدد که مواد محصول را نشان می‌دهند،
3. سه حرف بزرگ که محل ساخت محصول را مشخص می‌کند،
4. یک خط فاصله میان هر بخش.

فرمول زیر در سلول B2 بررسی می‌کند آیا کدهای ستون A معتبر هستند یا خیر:

=REGEXTEST([@Code],”[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}”,0)

توضیح:

1. [xs|s|m|l|xl]: اولین بخش که اندازه محصول را تعیین می‌کند.
2. [0-9]{1,2}: دومین بخش که عدد 1 یا 2 رقمی را بررسی می‌کند.
3. [A-Z]{3}: سومین بخش که سه حرف بزرگ را بررسی می‌کند.
4. 0: حساسیت به حروف بزرگ و کوچک.

نتیجه نشان می‌دهد تنها دو کد معتبر هستند.

چگونه از REGEXEXTRACT استفاده کنیم؟

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

نحو (Syntax):

REGEXEXTRACT(d,e,f,g)

1. d (الزامی): متنی که می‌خواهید از آن استخراج کنید.
2. e (الزامی): الگویی که می‌خواهید استخراج شود.
3. f (اختیاری): اگر 0 باشد اولین تطابق، 1 تمام تطابق‌ها به صورت آرایه، و 2 گروه‌هایی از اولین تطابق استخراج می‌شود.
4. g (اختیاری): اگر 0 باشد حساس به حروف بزرگ و کوچک است و اگر 1 باشد حساسیت ندارد.

مثال:

در این مثال، می‌خواهیم نام و شماره تلفن مشتریان را از ستون A استخراج کنیم.

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

=REGEXEXTRACT(A2,”[A-Z][a-z]+”,1)

توضیح:

1. [A-Z][a-z]+: کلمه‌ای که با حرف بزرگ شروع می‌شود و پس از آن حروف کوچک دارد.
2. 1: هر تطابق جداگانه در سلول‌های مجزا بازگردانده می‌شود.

برای استخراج شماره تلفن‌ها، فرمول زیر در سلول D2 وارد می‌شود:

=REGEXEXTRACT(A2,”[0-9()]+ [0-9-]+”)

چگونه از REGEXREPLACE استفاده کنیم؟

این تابع متنی را طبق الگوی مشخص به نسخه جدید تغییر می‌دهد، اما متن اصلی در مکان اولیه خود باقی می‌ماند.

نحو (Syntax):

REGEXREPLACE(h,i,j,k,l)

1. h (الزامی): متن اصلی.
2. i (الزامی): الگویی که باید جایگزین شود.
3. j (الزامی): محتوای جایگزین.
4. k (اختیاری): شماره تطابقی که باید جایگزین شود.
5. l (اختیاری): حساسیت به حروف.

مثال:

برای تغییر قالب اسامی به “نام خانوادگی، نام” از فرمول زیر استفاده می‌کنیم:

=REGEXREPLACE([@Client name],”([A-Z][a-z]+) ([A-Z][a-z]+)”,”$2, $1″)

توضیح:

1. ([A-Z][a-z]+) ([A-Z][a-z]+): دو کلمه با حروف بزرگ و کوچک.
2. $2, $1: ترتیب دو کلمه را جابجا می‌کند.

کاربردهای دیگر REGEX

REGEX فقط برای اکسل نیست و می‌توان از آن برای وظایف دیگر مانند:

1. تصحیح متن‌های کپی شده از PDF،
2. تغییر نام گروهی فایل‌ها،
3. قالب‌ بندی ارزها،
4. حذف تگ‌های HTML، و موارد دیگر استفاده کرد.