এক্সেলে মাস্টার রেজেক্স:একটি পাওয়ার ব্যবহারকারীর ক্র্যাশ কোর্স

এক্সেল পাওয়ার ব্যবহারকারীদের জন্য এটি আমাদের রেজেক্স ক্র্যাশ কোর্স।

Regex কি?

Regex (নিয়মিত অভিব্যক্তি) পাঠ্যের জন্য একটি প্যাটার্ন ভাষা। একটি সঠিক শব্দ অনুসন্ধান বা মেলানোর পরিবর্তে, regex আপনাকে পাঠ্যের আকার বর্ণনা করতে দেয়। Excel-এ, regex হল একটি সুপার-পাওয়ারড FIND + MID + SUBSTITUTE একত্রিত করার মত, যেখানে একটি প্যাটার্ন টেক্সট যাচাই, এক্সট্রাক্ট বা পরিষ্কার করতে পারে।

আপনি রেজেক্স ব্যবহার করে এক্সেলের সাধারণ কাঠামোর সাথে মিল করতে পারেন:ইমেল ঠিকানা, ফোন নম্বর, অনেক ফর্ম্যাটে তারিখ, বা নিয়ম অনুসরণ করে এমন যেকোনো আইডি।

আধুনিক এক্সেলে, আপনি এটি প্রধানত এর সাথে ব্যবহার করেন:

• REGEXTEST (একটি প্যাটার্ন বিদ্যমান কিনা তা পরীক্ষা করুন)
• REGEXEXTRACT (মিলিত অংশ টানুন)
• REGEXREPLACE (প্রতিস্থাপন/পরিষ্কার ম্যাচ)

রেজেক্স বেসিকস:বিল্ডিং প্যাটার্নস

Regex প্যাটার্ন হল স্ট্রিং যা আপনি যা খুঁজছেন তা নির্ধারণ করে। তারা আক্ষরিক অক্ষরগুলিকে (যেমন, "abc") বিশেষ মেটাক্যারেক্টারের সাথে একত্রিত করে। আপনি একটি দ্রুত রেফারেন্স হিসাবে এই টেবিল ব্যবহার করতে পারেন.

কী মেটাক্যারেক্টার এবং সিনট্যাক্স:

ব্যবহার করুন
• কোয়ান্টিফায়ার (*, +,?, {}) তাদের আগে এলিমেন্টে প্রয়োগ করুন।
• পতাকা: এক্সেলের রেজেক্স ফাংশন প্যাটার্নে (?i) মত ইনলাইন ফ্ল্যাগ সমর্থন করে না; কেস সংবেদনশীলতার জন্য ফাংশন আর্গুমেন্ট ব্যবহার করুন (যেমন, REGEXTEST-এর তৃতীয় আর্গুমেন্ট), অথবা ক্যারেক্টার ক্লাস ব্যবহার করুন (যেমন, [Aa])।
• লোভী বনাম অলস: ডিফল্টরূপে, কোয়ান্টিফায়াররা লোভী (যতটা সম্ভব মেলে)। যোগ করুন? একটি কোয়ান্টিফায়ারের পরে এটিকে অলস করতে (যেমন, .*?)।

Excel এর Regex ফাংশন

এক্সেল স্থানীয়ভাবে তিনটি রেজেক্স ফাংশন অফার করে। এই ফাংশনগুলি একটি টেক্সট স্ট্রিং এবং একটি রেগুলার এক্সপ্রেশন প্যাটার্ন ইনপুট হিসাবে নেয় এবং প্যাটার্নের উপর ভিত্তি করে টেক্সটকে যাচাই বা রূপান্তর করে। কোর্সে ডুব দেওয়ার আগে আসুন প্রতিটি ফাংশন জেনে নেই।

সিনট্যাক্স:

=REGEXTEST(text, pattern, [case_sensitivity])

এই ফাংশনটি পরীক্ষা করে যে প্যাটার্নটি প্রদত্ত পাঠ্যের কোনো অংশের সাথে মেলে কিনা। যদি টেক্সটের যেকোনো জায়গায় প্যাটার্নটি মেলে তবে TRUE প্রদান করে; অন্যথায় মিথ্যা।

• পাঠ্য: আপনি যে পাঠ্য বা সেল রেফারেন্স পরীক্ষা করতে চান।
• প্যাটার্ন: রেগুলার এক্সপ্রেশন (regex) প্যাটার্ন যা আপনি যে টেক্সটটি মেলাতে চান তা বর্ণনা করে।
• [কেস_সংবেদনশীলতা]: ম্যাচটি কেস-সংবেদনশীল কিনা তা নির্ধারণ করে। ডিফল্টরূপে, ম্যাচটি কেস-সংবেদনশীল৷
  ৷
  • 0: কেস সংবেদনশীল
  • 1: কেস সংবেদনশীল

A1-এ পরপর তিনটি সংখ্যা থাকলে এই ফাংশনটি TRUE প্রদান করে।

সিনট্যাক্স:

=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

এই ফাংশন মিলে যাওয়া টেক্সট বের করে।

• [রিটার্ন_মোড]: একটি সংখ্যা যা নির্দিষ্ট করে যে আপনি কোন স্ট্রিংগুলি বের করতে চান৷ ডিফল্টরূপে, রিটার্ন মোড হল 0।
  • 0: প্যাটার্নের সাথে মেলে প্রথম স্ট্রিংটি ফেরত দিন
  • 1: একটি অ্যারে হিসাবে প্যাটার্নের সাথে মেলে এমন সমস্ত স্ট্রিং ফেরত দিন
  • 2: অ্যারে হিসাবে প্রথম ম্যাচ থেকে ক্যাপচারিং গ্রুপগুলি ফেরত দিন

=REGEXEXTRACT(A1, "\d{3}-\d{3}-\d{4}")

"123-456-7890" এর মতো একটি ফোন নম্বর বের করে।

সিনট্যাক্স:

=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

এই ফাংশনটি মিলগুলিকে নতুন পাঠ্যের সাথে প্রতিস্থাপন করে৷

• প্রতিস্থাপন: আপনি প্যাটার্নের জায়গায় যে পাঠ্যটি ব্যবহার করতে চান। আপনি $1, $2, ইত্যাদির সাথে ক্যাপচার গ্রুপ উল্লেখ করতে পারেন।
• ঘটনা: আপনি যে প্যাটার্নটি প্রতিস্থাপন করতে চান তার কোন উদাহরণ নির্দিষ্ট করে। ডিফল্টরূপে, ঘটনা 0, যা সমস্ত দৃষ্টান্ত প্রতিস্থাপন করে। একটি নেতিবাচক সংখ্যা সেই উদাহরণটিকে প্রতিস্থাপন করে, শেষ থেকে অনুসন্ধান করা হয়।

=REGEXREPLACE(A1, "\d{3}", "***")

প্রতি তিন-অঙ্কের ক্রম মাস্ক।

এই ফাংশনগুলি যদি অ্যারেগুলি ফেরত দেয় (যেমন, একাধিক নিষ্কাশন)।

এক্সেল পাওয়ার ব্যবহারকারীদের জন্য ব্যবহারিক উদাহরণ

ধরুন আপনার কাছে অগোছালো ডেটা আছে। সাধারণ পরিস্থিতিতে regex প্রয়োগ করা যাক।

উদাহরণ 1:ইমেল ঠিকানা যাচাই করা

আপনি বৈধ কোম্পানির ইমেলগুলিকে ফ্ল্যাগ করতে 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,}$")

অবৈধ ইমেল হাইলাইট করতে শর্তসাপেক্ষ বিন্যাস ব্যবহার করুন:

• হোম এ যান ট্যাব>> শর্তাধীন বিন্যাস নির্বাচন করুন>> নতুন নিয়ম নির্বাচন করুন
• চয়ন করুন কোন কক্ষ বিন্যাস করতে হবে তা নির্ধারণ করতে একটি সূত্র ব্যবহার করুন
• নিম্নলিখিত সূত্র ঢোকান
• ফর্ম্যাট নির্বাচন করুন>> একটি ফিল কালার বেছে নিন>> ঠিক আছে ক্লিক করুন

• অবৈধ ইমেল ঠিকানা লাল রঙে হাইলাইট করা হয়েছে

আপনি বৈধ কোম্পানির ইমেলগুলিকে পতাকাঙ্কিত করতে REGEXTEST ব্যবহার করতে পারেন৷

=REGEXTEST(B2,"@company\.com$",1)

• তৃতীয় যুক্তি (1 ) ম্যাচটিকে কেস-অসংবেদনশীল করে তোলে, তাই Company.com এখনও পাস করে
• \. বিন্দু এড়িয়ে যায়। একটি সমতল । মানে "যে কোনো অক্ষর"

উদাহরণ 2:অগোছালো নোট থেকে ডেটা বের করা

ধরুন আপনার নোটে অর্ডার আইডি বা ফোন নম্বরের মতো মিশ্র ডেটা আছে। আপনি REGEXEXTRACT ফাংশন ব্যবহার করে ফোন নম্বর বা অর্ডার আইডি বের করতে পারেন।

অর্ডার আইডি এক্সট্র্যাক্ট করুন:

=REGEXEXTRACT(D2,"TX-\d{4}")

• যদি কিছু সারিতে এটি না থাকে, তাহলে IFERROR দিয়ে মোড়ানো:

=IFERROR(REGEXEXTRACT(D2,"TX-\d{4}"),"")

ফোন নম্বর বের করুন:

• ডেটা: "আমাকে 123-456-7890 বা (987) 654-3210 নম্বরে কল করুন।"
• প্যাটার্ন: (\d{3}[-। )]+){2}\d{4}

=REGEXEXTRACT(A2, "(\d{3}[-. )]+){2}\d{4}", 0, 1)

এই সূত্রটি টেক্সট থেকে ফোন নম্বর বের করে।

উদাহরণ 3:নন-ডিজিটগুলি সরিয়ে ফোন নম্বরগুলিকে মানককরণ করা

• প্রথমে একটি শুধুমাত্র-সংখ্যা সংস্করণ তৈরি করুন।

=REGEXREPLACE(C2,"[^\d]","")

• এখন আপনি ফর্ম্যাট করতে পারেন (উদাহরণ:ইউএস নম্বর হিসাবে শেষ 10 সংখ্যা যখন উপলব্ধ থাকে):

=LET(x,REGEXREPLACE(C2,"[^\d]",""),
d,RIGHT(x,10),
IF(LEN(d)=10,"("&LEFT(d,3)&") "&MID(d,4,3)&"-"&RIGHT(d,4),""))

এই পদ্ধতিটি সমস্ত নন-ডিজিট অক্ষরগুলিকে সরিয়ে দেয় এবং ফলস্বরূপ ফোন নম্বর ফর্ম্যাট করে।

উদাহরণ 4:তারিখ পার্সিং এবং রিফরম্যাটিং

আপনি তারিখটি পার্স করতে পারেন এবং regex ব্যবহার করে এটিকে পুনরায় ফর্ম্যাট করতে পারেন।

• প্যাটার্ন: (\d{1,2})/(\d{1,2})/(\d{4})

=REGEXREPLACE(A2, "(\d{1,2})/(\d{1,2})/(\d{4})", "$3-$2-$1")

এই সূত্রটি তারিখটি পার্স করে এবং তারপর এটিকে একটি বৈধ ISO-এর মতো বিন্যাসে পুনরায় ফর্ম্যাট করে। আপনি প্রতিস্থাপনে অংশগুলি উল্লেখ করতে ক্যাপচার গ্রুপ () ব্যবহার করতে পারেন।

উদাহরণ 5:অগোছালো ডেটা পরিষ্কার করা (যেমন, অতিরিক্ত স্থান সরানো)

আপনি অগোছালো ডেটা পরিষ্কার করতে regex ব্যবহার করতে পারেন, যেমন অতিরিক্ত স্পেস সরানো এবং ফর্ম্যাটিং স্বাভাবিক করা৷

অতিরিক্ত স্পেস অপসারণ করতে, নিম্নলিখিত প্যাটার্ন ব্যবহার করুন।

• প্যাটার্ন: \s+

=REGEXREPLACE(A2, "\s+", " ")

এই সূত্রটি হোয়াইটস্পেসের রানকে একটি একক স্পেস দিয়ে প্রতিস্থাপন করে।

উদাহরণ 6:বন্ধনীর মধ্যে যেকোনো কিছু অপসারণ করা (বন্ধনী সহ)

• একটি ঘর নির্বাচন করুন এবং নিম্নলিখিত সূত্রটি সন্নিবেশ করুন:

=REGEXREPLACE(D2,"\s*\(.*?\)","")

• .*? একজন অ-লোভী ম্যাচ এটি শেষের পরিবর্তে প্রথম বন্ধনী বন্ধনীতে থামে।

এই সূত্রটি বন্ধনীর মধ্যে থাকা সমস্ত ডেটা সরিয়ে দেয়, বন্ধনীগুলি সহ।

উদাহরণ 7:ফিল্টারিং সারি যাতে একটি অর্ডার আইডি থাকে

=FILTER(A2:F16,REGEXTEST(D2:D16,"TX-\d{4}"))

এই সূত্রটি রেজেক্স প্যাটার্নের উপর ভিত্তি করে ডেটা ফিল্টার করে।

Excel এ উন্নত রেজেক্স কৌশল

• লুকহেডস/লুকবিহাইন্ডস: টেক্সট ব্যবহার না করে শর্ত জারি করুন।
  • ইতিবাচক দৃষ্টিভঙ্গি:(?=…) যেমন, % চিহ্নের আগে সংখ্যার জন্য \d+(?=%)$।
  • মূল্য বের করতে REGEXEXTRACT-এ ব্যবহার করুন:\d+\.\d{2}(?=\sUSD)
• নন-ক্যাপচারিং গ্রুপ: (?:…) ক্যাপচার ছাড়াই গ্রুপ করার জন্য।
• বিকল্প: বিকল্পগুলির জন্য, যেমন, (http|https)://\S+ URL-এর জন্য৷
৷
• হ্যান্ডলিং অ্যারে: যদি REGEXEXTRACT একাধিক গ্রুপ প্রদান করে, তাহলে INDEX বা স্পিল রেঞ্জ ব্যবহার করুন।
• অন্যান্য ফাংশনের সাথে সমন্বয়: শক্তিশালী কর্মপ্রবাহের জন্য IF, FILTER বা LAMBDA-এর সাথে নেস্ট করুন।

টিপস এবং সর্বোত্তম অভ্যাস

• পরীক্ষার ধরণ: regex101.com এর মতো অনলাইন টুলস ব্যবহার করুন "ECMAScript" ফ্লেভারের সাথে (Excel এর regex এর সবচেয়ে কাছাকাছি)।
• পারফরম্যান্স: বড় ডেটাসেটে Regex ধীর হতে পারে; প্রথমে নমুনা পরীক্ষা করুন।
• ত্রুটি: যদি কোন মিল না থাকে, REGEXEXTRACT #N/A প্রদান করে; IFNA বা IFERROR দিয়ে পরিচালনা করুন।
• সীমাবদ্ধতা: Excel এর regex একটি উপসেটের উপর ভিত্তি করে, সম্পূর্ণ PCRE সমর্থন ছাড়াই, তাই পুনরাবৃত্তির মত উন্নত বৈশিষ্ট্যগুলি এড়িয়ে চলুন।
• আরো শেখা: বাস্তব ডেটাসেটের সাথে অনুশীলন করুন। Regex ব্যবহার করা সহজ হয়!

অভ্যাস ওয়ার্কবুক ডাউনলোড করুন

র্যাপিং আপ

এক্সেল পাওয়ার ব্যবহারকারীরা ডেটা পরিষ্কার, যাচাইকরণ এবং অটোমেশনকে ত্বরান্বিত করতে আমাদের রেজেক্স ক্র্যাশ কোর্স অনুসরণ করতে পারেন। একবার আপনি বেসিক বিল্ডিং ব্লকগুলির সাথে স্বাচ্ছন্দ্য বোধ করলে, আরও পরিশীলিত প্যাটার্ন ম্যাচিংয়ের জন্য সামনের দিকে এবং পিছনের দিকে তাকান। এই ক্র্যাশ কোর্সটি আপনাকে শুরু করে:প্রথমে অভিব্যক্তিটি বুঝুন, তারপর একটি ফাঁকা ওয়ার্কবুকে প্যাটার্ন নিয়ে পরীক্ষা করুন৷ Regex রূপান্তর করতে পারে কিভাবে আপনি Excel এ পাঠ্য পরিচালনা করেন!