রেগুলার এক্সপ্রেশন (regex) প্রথম নজরে কমপ্লেক্স দেখাতে পারে, কিন্তু এগুলো প্যাটার্ন ম্যাচিং এবং টেক্সট ম্যানিপুলেশনের জন্য পাওয়ারফুল টুল। এক্সেলে, এগুলো আপনাকে কমপ্লেক্স প্যাটার্নের ওপর ভিত্তি করে ডেটা সার্চ, এক্সট্র্যাক্ট, এবং রিপ্লেস করতে দেয়, যা ডেটা ক্লিনিং, ভ্যালিডেশন, এবং পার্সিং-जैसे টাস্ক অনেক বেশি দক্ষ করে তোলে। একবার বেসিক শিখলে এটা ঘন্টার পর ঘন্টা ক্লিনিং, ভ্যালিডেটিং, এবং ইনফরমেশন এক্সট্র্যাক্ট করার সময় বাঁচাতে পারে। Regex সাপোর্ট করার জন্য, এক্সেল REGEXTEST, REGEXEXTRACT, এবং REGEXREPLACE ফাংশন অফার করে।
এই হলো এক্সেল পাওয়ার ইউজারদের জন্য আমাদের Regex ক্র্যাশ কোর্স।
Regex কী?
Regex (রেগুলার এক্সপ্রেশন) হলো টেক্সটের জন্য একটি প্যাটার্ন ল্যাঙ্গুয়েজ। এক্সাক্ট ওয়ার্ড সার্চ বা ম্যাচ করার বদলে, regex আপনাকে টেক্সটের আকৃতি বর্ণনা করতে দেয়। এক্সেলে, regex 마치 একটি সুপার-পাওয়ার FIND + MID + SUBSTITUTE কম্বাইনড, যেখানে একটি প্যাটার্ন ভ্যালিডেট, এক্সট্র্যাক্ট, বা ক্লিন করতে পারে টেক্সট।
আপনি regex ব্যবহার করে এক্সেলে সাধারণ কাঠামো ম্যাচ করতে পারেন: ইমেইল ঠিকানা, ফোন নম্বর, বিভিন্ন ফরম্যাটের তারিখ, বা যেকোনো আইডি যেটি একটি রুল ফলো করে।
আধুনিক এক্সেলে, আপনি প্রধানত এটি ব্যবহার করেন:
- REGEXTEST (প্যাটার্ন এক্সিস্ট করে কিনা চেক করতে)
- REGEXEXTRACT (ম্যাচিং অংশ বের করতে)
- REGEXREPLACE (ম্যাচ রিপ্লেস/ক্লিন করতে)
Regex বেসিক: প্যাটার্ন বানানো
Regex প্যাটার্ন হলো স্ট্রিং যা ডিফাইন করে আপনি কী খুঁজছেন। সেগুলো লিটারেল ক্যারেক্টার (যেমন, "abc") স্পেশাল মেটাক্যারেক্টারসহ কম্বাইন করে। আপনি এই টেবিলটি কুইক রেফারেন্স হিসেবে ব্যবহার করতে পারেন।
কী মেটাক্যারেক্টার এবং সিনট্যাক্স:
| বিল্ডিং ব্লক | অর্থ | কুইক উদাহরণ প্যাটার্ন | ম্যাচেস |
|---|---|---|---|
| abc | লিটারেল টেক্সট | TX- | TX- |
| . | যেকোনো একক ক্যারেক্টার | a.c | abc, a-c |
| \d | ডিজিট (০–৯) | \d{4} | 2026 |
| \D | নন-ডিজিট | \D+ | TX- |
| \w | ওয়ার্ড ক্যারেক্টার (A–Z, a–z, ০–৯, _) | \w+ | amina_01 |
| \W | নন-ওয়ার্ড ক্যারেক্টার | \W+ | – ( ) . |
| \s | হোয়াইটস্পেস | \s+ | স্পেস/ট্যাব |
| \S | নন-হোয়াইটস্পেস | \S+ | [email protected] |
| [ABC] | এগুলোতে যেকোনো একটি | [AEIOU] | A |
| [A-Z] | রেঞ্জ (আপারকেস) | [A-Z]{2} | TX |
| [a-z] | রেঞ্জ (লোয়ারকেস) | [a-z]+ | rahman |
| [0-9] | ডিজিট রেঞ্জ | [0-9]+ | 1054 |
| [^0-9] | এগুলো NOT (নেগেটেড ক্লাস) | [^0-9]+ | TX- |
| * | ০ বা অধিক | \d* | "", 7, 1054 |
| + | ১ বা অধিক | \d+ | 7, 1054 |
| ? | ঐচ্ছিক (০ বা ১) | -? | – বা কিছু নেই |
| {n} | ঠিক n বার | \d{2} | 12 |
| {n,} | কমপক্ষে n বার | \d{4,} | 202601 |
| {n,m} | n এবং m এর মধ্যে | \d{2,4} | 12, 2026 |
| ^ | টেক্সটের শুরু | ^TX- | TX-1054 (শুরুতে) |
| $ | টেক্সটের শেষ | \.com$ | .com দিয়ে শেষ |
| \b | ওয়ার্ড বাউন্ডারি | \bTX-\d{4}\b | TX-1054 পুরো টোকেন হিসেবে |
| ( … ) | ক্যাপচারিং গ্রুপ | (\d{4}) | 1054 ক্যাপচার করে |
| (?: … ) | নন-ক্যাপচারিং গ্রুপ | (?:TX|CA)-\d{4} | TX-1054 বা CA-1054 |
| \ | স্পেশাল ক্যারেক্টার এস্কেপ | company\.com | লিটারেল company.com |
| .* | যেকোনো ক্যারেক্টার (লোভী) | \(.*\) | প্রথম ( থেকে শেষ ) পর্যন্ত ম্যাচ |
| .*? | যেকোনো ক্যারেক্টার (নন-লোভী) | \(.*?\) | সबसे ছোট ( … ) ম্যাচ |
| (?i) | কেস-ইনসেনসিটিভ মোড (এক্সেল regex প্যাটার্নে সাপোর্টেড নয়; ফাংশনের case_sensitivity আর্গুমেন্ট ব্যবহার করুন) | — | REGEXTEST(text, pattern, 1) ব্যবহার করুন |
- কোয়ান্টিফায়ার (*, +, ?, {}) সেসবকে প্রয়োগ হয় যেগুলো তাদের 바로 আগে থাকে।
- ফ্ল্যাগস: এক্সেলের regex ফাংশনগুলো ইনলাইন ফ্ল্যাগ সাপোর্ট করে না যেমন প্যাটার্নে (?i); কেস সেনসিটিভিটির জন্য ফাংশন আর্গুমেন্ট ব্যবহার করুন (যেমন REGEXTEST-এর থার্ড আর্গুমেন্ট), বা ক্যারেক্টার ক্লাস ব্যবহার করুন (যেমন, [Aa])।
- লোভী vs লেজি: ডিফল্টভাবে কোয়ান্টিফায়ার লোভী (যতটুকু সম্ভব ম্যাচ করে)। কোয়ান্টিফায়ারের পর ? যোগ করলে সেটি লেজি হয় (যেমন, .*?)।
এক্সেলের Regex ফাংশন
এক্সেল নেটিভলি তিনটি regex ফাংশন অফার করে। এই ফাংশনগুলো একটি টেক্সট স্ট্রিং এবং একটি রেগুলার এক্সপ্রেশন প্যাটার্ন ইনপুট হিসেবে নেয় এবং প্যাটার্নের ওপর ভিত্তি করে টেক্সট ভ্যালিডেট বা ট্রান্সফর্ম করে। ফাংশনগুলো জানার আগে প্রতিটি ফাংশনকে জানি।
সিনট্যাক্স:
=REGEXTEST(text, pattern, [case_sensitivity])
এই ফাংশন চেক করে প্যাটার্ন প্রদত্ত টেক্সটের যেকোনো অংশে ম্যাচ করে কিনা। প্যাটার্ন টেক্সটের যেকোনো জায়গায় ম্যাচ করলে TRUE রিটার্ন করে; অন্যথায় FALSE।
- text: যে টেক্সট বা সেল রেফারেন্স টেস্ট করতে চান।
- pattern: যে রেগুলার এক্সপ্রেশন (regex) প্যাটার্ন যা আপনি যে টেক্সট ম্যাচ করতে চান সেটা বর্ণনা করে।
- [case_sensitivity]: ম্যাচ কেস-সেনসিটিভ কিনা সেটা নির্ধারণ করে। ডিফল্টভাবে ম্যাচ কেস-সেনসিটিভ।
- ০: কেস সেনসিটিভ
- ১: কেস ইনসেনসিটিভ
এই ফাংশন TRUE রিটার্ন করে যদি A1-এ তিনটি последователь ডিজিট থাকে।
সিনট্যাক্স:
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
এই ফাংশন ম্যাচিং টেক্সট এক্সট্র্যাক্ট করে।
- [return_mode]: একটি নম্বর যা স্পেসিফাই করে আপনি কী স্ট্রিংস এক্সট্র্যাক্ট করতে চান। ডিফল্ট রিটার্ন মোড ০।
- ০: প্যাটার্ন ম্যাচ করা প্রথম স্ট্রিং রিটার্ন করুন
- ১: প্যাটার্ন ম্যাচ করা সব স্ট্রিং অ্যারে হিসেবে রিটার্ন করুন
- ২: প্রথম ম্যাচের ক্যাপচারিং গ্রুপগুলো অ্যারে হিসেবে রিটার্ন করুন
=REGEXEXTRACT(A1, "\d{3}-\d{3}-\d{4}")
"১২৩-৪৫৬-৭৮৯০" এর মত একটি ফোন নম্বর এক্সট্র্যাক্ট করে।
সিনট্যাক্স:
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
এই ফাংশন ম্যাচগুলোকে নতুন টেক্সট দিয়ে রিপ্লেস করে।
- replacement: যে টেক্সট আপনি প্যাটার্নের বদলে ব্যবহার করতে চান। আপনি ক্যাপচার গ্রুপ $1, $2, ইত্যাদি দিয়ে রেফারেন্স করতে পারেন।
- occurrence: প্যাটার্নের কোন ইনস্ট্যান্স রিপ্লেস করতে চান সেটা স্পেসিফাই করে। ডিফল্ট occurrence ০, যা সব ইনস্ট্যান্স রিপ্লেস করে। নেগেটিভ নম্বর শেষ থেকে সার্চ করে সেই ইনস্ট্যান্স রিপ্লেস করে।
=REGEXREPLACE(A1, "\d{3}", "***")
প্রতিটি তিন-ডিজিট সিকোয়েন্স মাস্ক করে।
এই ফাংশনগুলো স্পিল করে যদি অ্যারে রিটার্ন করে (যেমন, একাধিক এক্সট্র্যাকশন)।
এক্সেল পাওয়ার ইউজারদের জন্য প্র্যাক্টিক্যাল উদাহরণ
মান লিন আপনার কাছে মেসি ডেটা আছে। চলুন regex সাধারণ সিনেরিওতে প্রয়োগ করি।
উদাহরণ ১: ইমেইল ঠিকানা ভ্যালিডেট করা
আপনি REGEXTEST ব্যবহার করে বৈধ কোম্পানির ইমেইল ফ্ল্যাগ করতে পারেন (কেস-ইনসেনসিটিভ ম্যাচ প্রায়শই সেফার)।
ইমেইল ভ্যালিডেট করার জন্য নিচের প্যাটার্ন ব্যবহার করুন।
- প্যাটার্ন: ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$
=REGEXTEST(A2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")
কন্ডিশনাল ফরম্যাটিং ব্যবহার করে অবৈধ ইমেইল হাইলাইট করুন:
- Home ট্যাবে যান >> Conditional Formatting >> New Rule নির্বাচন করুন
- Use a formula to determine which cells to format বেছে নিন
- নিচের ফর্মুলা ইনসার্ট করুন
- Format >> একটি Fill Color বেছে নিন >> OK ক্লিক করুন
- অবৈধ ইমেইল ঠিকানা লাল রঙে হাইলাইট হবে
আপনি কোম্পানির বৈধ ইমেইল ফ্ল্যাগ করার জন্যও REGEXTEST ব্যবহার করতে পারেন।
=REGEXTEST(B2,"@company\.com$",1)
- থার্ড আর্গুমেন্ট (১) ম্যাচ কেস-ইনসেনসিটিভ করে, তাই Company.com ও পাস করবে
- \. ডটকে এস্কেপ করে। একটি সাদা . মানে "যেকোনো ক্যারেক্টার"
উদাহরণ ২: মেসি নোট থেকে ডেটা এক্সট্র্যাক্ট করা
মান লিন আপনার কাছে মিক্সড ডেটা আছে, যেমন একটি অর্ডার আইডি বা ফোন নম্বর, নোটে। আপনি REGEXEXTRACT ফাংশন ব্যবহার করে ফোন নম্বর বা অর্ডার আইডি বের করতে পারেন।
অর্ডার আইডি এক্সট্র্যাক্ট করুন:
=REGEXEXTRACT(D2,"TX-\d{4}")
- কিছু সারিতে এটা না থাকলে, IFERROR দিয়ে র্যাপ করুন:
=IFERROR(REGEXEXTRACT(D2,"TX-\d{4}"),"")
ফোন নম্বর এক্সট্র্যাক্ট করুন:
- ডেটা: "Call me at 123-456-7890 or (987) 654-3210."
- প্যাটার্ন: (\d{3}[-. )]+){2}\d{4}
=REGEXEXTRACT(A2, "(\d{3}[-. )]+){2}\d{4}", 0, 1)
এই ফর্মুলা টেক্সট থেকে ফোন নম্বর এক্সট্র্যাক্ট করে।
উদাহরণ ৩: ফোন নম্বর স্ট্যান্ডার্ডাইজ করা নন-ডিজিট সরিয়ে
- প্রথমে একটি ডিজিট-অনলি ভার্সন তৈরি করুন।
=REGEXREPLACE(C2,"[^\d]","")
- এখন আপনি ফরম্যাট করতে পারেন (উদাহরণ: উপলব্ধ হলে শেষ ১০ ডিজিট ইউএস নম্বর হিসেবে):
=LET(x,REGEXREPLACE(C2,"[^\d]",""), d,RIGHT(x,10), IF(LEN(d)=10,"("&LEFT(d,3)&") "&MID(d,4,3)&"-"&RIGHT(d,4),""))
এই পদ্ধতিটি সব নন-ডিজিট ক্যারেক্টার সরিয়ে ফলাফল ফোন নম্বর ফরম্যাট করে।
উদাহরণ ৪: তারিখ পার্স এবং রিফরম্যাট করা
আপনি regex ব্যবহার করে তারিখ পার্স এবং রিফরম্যাট করতে পারেন।
- প্যাটার্ন: (\d{1,2})/(\d{1,2})/(\d{4})
=REGEXREPLACE(A2, "(\d{1,2})/(\d{1,2})/(\d{4})", "$3-$2-$1")
এই ফর্মুলা তারিখ পার্স করে এবং তারপর সেটিকে একটি বৈধ ISO-লাইক ফরম্যাটে রিফরম্যাট করে। আপনি ক্যাপচার গ্রুপ () ব্যবহার করে রিপ্লেসমেন্টে অংশগুলো রেফারেন্স করতে পারেন।
উদাহরণ ৫: মেসি ডেটা ক্লিন করা (যেমন, অতিরিক্ত স্পেস সরানো)
আপনি regex ব্যবহার করে মেসি ডেটা ক্লিন করতে পারেন, যেমন অতিরিক্ত স্পেস সরানো এবং ফরম্যাটিং নর্মালাইজ করা।
অতিরিক্ত স্পেস সরানোর জন্য নিচের প্যাটার্ন ব্যবহার করুন।
- প্যাটার্ন: \s+
=REGEXREPLACE(A2, "\s+", " ")
এই ফর্মুলা হোয়াইটস্পেসের রানকে একটি একক স্পেস দিয়ে রিপ্লেস করে।
উদাহরণ ৬: প্যারেনথেসিসের ভিতরের সবকিছু সরানো (প্যারেনথেসিসসহ)
- একটি সেল সিলেক্ট করুন এবং নিচের ফর্মুলা ইনসার্ট করুন:
=REGEXREPLACE(D2,"\s*\(.*?\)","")
- .*? একটি নন-লোভী ম্যাচ। এটি প্রথম ক্লোজিং প্যারেনথেসিসে থামে, শেষের নয়।
এই ফর্মুলা প্যারেনথেসিসের ভিতরের সকল ডেটা প্যারেনথেসিসসহ সরিয়ে দেয়।
উদাহরণ ৭: অর্ডার আইডি ধারণ করা সারি ফিল্টার করা
=FILTER(A2:F16,REGEXTEST(D2:D16,"TX-\d{4}"))
এই ফর্মুলা regex প্যাটার্নের ওপর ভিত্তি করে ডেটা ফিল্টার করে।
এক্সেলে অ্যাডভান্সড Regex টেকনিক
- লুকঅহেড/লুকবিহাইন্ড: টেক্সট কনসিউম ছাড়া কন্ডিশন অ্যাসার্ট করে।
- পজিটিভ লুকঅহেড: (?=…) উদাহরণস্বরূপ, % সাইন-এর আগের নম্বর এর জন্য \d+(?=%)$।
- REGEXEXTRACT-এ প্রাইস এক্সট্র্যাক্ট করতে ব্যবহার করুন: \d+\.\d{2}(?=\sUSD)
- নন-ক্যাপচারিং গ্রুপস: (?:…) ক্যাপচার ছাড়া গ্রুপিং-এর জন্য।
- অল্টারনেশন: অপশনের জন্য, উদাহরণস্বরূপ, (http|https)://\S+ URL-এর জন্য।
- অ্যারে হ্যান্ডলিং: যদি REGEXEXTRACT একাধিক গ্রুপ রিটার্ন করে, INDEX বা স্পিল রেঞ্জ ব্যবহার করুন।
- অন্য ফাংশনlarla কম্বাইন: IF, FILTER, বা LAMBDA দিয়ে नेস্ট করুন পাওয়ারফুল ওয়ার্কফ্লোর জন্য।
টিপস এবং সেরা অভ্যাস
- প্যাটার্ন টেস্ট করুন: regex101.com-এর মতো অনলাইন টুলস ব্যবহার করুন "ECMAScript" ফ্লেভারের সাথে (এক্সেলের regex-এর সবচেয়ে ক্লোজেস্ট)।
- পারফরম্যান্স: Regex বড় ডেটাসেটে ধীর হতে পারে; প্রথমে স্যাম্পলে টেস্ট করুন।
- এরর: যদি কোনো ম্যাচ না থাকে, REGEXEXTRACT #N/A রিটার্ন করে; IFNA বা IFERROR দিয়ে হ্যান্ডেল করুন।
- সীমাবদ্ধতা: এক্সেলের regex একটি সাবসেটের ওপর ভিত্তি, ফুল PCRE সাপোর্ট ছাড়া, তাই রিকার্সন মতো অ্যাডভান্সড ফিচার এড়িয়ে চলুন।
- আরও শিখুন: রিয়েল ডেটাসেটে প্র্যাকটিস করুন। Regex ব্যবহার করার সাথে সাথে সহজ হয়ে যায়!
প্র্যাকটিস ওয়ার্কবুক ডাউনলোড করুন
উপসংহার
এক্সেল পাওয়ার ইউজাররা আমাদের regex ক্র্যাশ কোর্স ফলো করে ডেটা ক্লিনিং, ভ্যালিডেশন, এবং অটোমেশন ত্বরান্বিত করতে পারেন। একবার আপনি বেসিক বিল্ডিং ব্লকসের সাথে কমফর্টেবল হলে, আরও সফিস্টিকেটেড প্যাটার্ন ম্যাচিং-এর জন্য লুকঅহেড এবং লুকবিহাইন্ড এক্সপ্লোর করুন। এই ক্র্যাশ কোর্স আপনাকে শুরু করاয়: প্রথমে এক্সপ্রেশন বুঝুন, তারপর একটি খালি ওয়ার্কবুকে প্যাটার্নের সাথে এক্সপেরিমেন্ট করুন। Regex এক্সেলে টেক্সট হ্যান্ডল করার আপনার পদ্ধতি রূপান্তরিত করতে পারে!
