Excel Regex ट्यूटोरियल: रेगुलर एक्सप्रेशंस के साथ पैटर्न मैचिंग में महारत

Excel में RegEx का उपयोग

हालाँकि Excel में RegEx के लिए नैटिव सपोर्ट नहीं है, आप Windows के लिए VBA स्क्रिप्ट के जरिए अपना कस्टम डिफिनिशन लिखकर या Mac के लिए ऐड-इन्स सक्षम करके इसे सक्रिय कर सकते हैं। आइए देखें कि Excel में किसी कॉलम से टेक्स्ट निकालने के लिए रेगेक्स फ़ंक्शन कैसे सक्षम करें।

Windows के लिए

शुरू करने के लिए, Alt+F11 दबाकर Visual Basic Editor खोलें। आप Tools > Macro > Visual Basic Editor के जरिए भी वहाँ जा सकते हैं। फिर Insert पर क्लिक करें > Module।

निम्न फ़ंक्शन को मॉड्यूल में पेस्ट करें:

Public Function CustomRegExpExtract(inputText As String, regexPattern As String, Optional instanceNumber As Integer = 0, Optional matchCase As Boolean = True) As Variant
    Dim textMatches() As String
    Dim matchesIndex As Integer
    Dim regex As Object
    Dim matches As Object
    
    On Error GoTo ErrHandler
    
    CustomRegExpExtract = ""
    
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = regexPattern
    regex.Global = True
    regex.MultiLine = True
    
    ' Set case sensitivity
    If matchCase Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
    
    ' Execute the regex pattern on the input text
    Set matches = regex.Execute(inputText)
    
    If matches.Count > 0 Then
        If instanceNumber = 0 Then
            ' If instanceNumber is 0, return all matches in an array
            ReDim textMatches(matches.Count - 1, 0)
            For matchesIndex = 0 To matches.Count - 1
                textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
            Next matchesIndex
            CustomRegExpExtract = textMatches
        Else
            ' If instanceNumber is specified, return the match at that instance
            CustomRegExpExtract = matches.Item(instanceNumber - 1)
        End If
    End If
    
    Exit Function

ErrHandler:
    ' Handle errors by returning an error value
    CustomRegExpExtract = CVErr(xlErrValue)
End Function

' Modified from an original script by Patrick Matthews

फ़ंक्शन सेव करें और स्प्रेडशीट पर वापस जाएँ। अब वर्कबुक को मैक्रो-सक्षम फ़ाइल के रूप में .xlsm फ़ाइल एक्सटेंशन के साथ सेव करें।

Excel वर्कशीट में फ़ंक्शन का उपयोग

Excel वर्कबुक में मैक्रोज़ सक्षम करके, अब आप CustomRegExpExtract फ़ंक्शन को Excel के किसी सामान्य फ़ंक्शन की तरह जटिल स्ट्रिंग पैटर्न्स को पार्स करने के लिए उपयोग कर सकते हैं, जहाँ:

• inputText वह मूल स्ट्रिंग है जिसे पार्स करना है;

• regexPattern वह रेगुलर एक्सप्रेशन पैटर्न है जो inputText को पार्स करने के लिए उपयोग होता है;

• instanceNumber डिफ़ॉल्ट रूप से पैटर्न की सभी घटनाएँ लौटाता है, लेकिन लौटाने के लिए घटनाओं की संख्या निर्दिष्ट करने हेतु एक पूर्णांक दर्ज किया जा सकता है; और

• matchCase यह निर्दिष्ट करता है कि मैचिंग के दौरान टेक्स्ट के केस पर विचार करना है (TRUE या छोड़ा गया) या उसे नज़रअंदाज़ करना है (FALSE)।

मान लें कि हम किसी फ़ील्ड से फोन नंबर निकालने में रुचि रखते हैं। हम रेगेक्स पैटर्न किसी भी सेल में टाइप कर सकते हैं (नीचे के मामले में, यह सेल A2 में है)। सेल A6 में आपका फ्री-फॉर्म स्ट्रिंग उदाहरण है, "Sophia, 1111111111"। सेल B6 में, हम टाइप करते हैं =CustomRegExpExtract(A6,$A$2)। यह फ़ंक्शन स्ट्रिंग को सेल A2 के रेगेक्स पैटर्न के साथ मैच कर फोन नंबर निकालता है और अपेक्षा के अनुरूप "1111111111" लौटाता है।

Mac OS के लिए

दुर्भाग्यवश, Mac OS MS VBScript Regular Expressions 5.5 लाइब्रेरी को नहीं पहचानता। Mac पर Excel में जटिल पैटर्न मैचिंग सक्षम करने के लिए दो विकल्प हैं: एडवांस्ड फ़िल्टर्स का उपयोग करें या पैकेज इंस्टॉल करें।

एडवांस्ड फ़िल्टर्स का उपयोग

Excel में एडवांस्ड फ़िल्टर्स कई मानदंडों के सेट के आधार पर जटिल डेटा फ़िल्टरिंग संचालन करने में मदद करते हैं। बड़े डेटा सेट्स से निपटने पर यह फ़ीचर विशेष रूप से उपयोगी हो सकता है। Excel में एडवांस्ड फ़िल्टर का उपयोग इस प्रकार करें:

• अपने डेटा को सेट करें: सुनिश्चित करें कि आपका डेटा स्पष्ट हैडर्स के साथ टेबल-जैसे प्रारूप में है।

• क्राइटेरिया रेंज बनाएं: अपनी वर्कशीट में फ़िल्टरिंग के मानदंड परिभाषित करने के लिए एक अलग क्षेत्र सेट करें। इस क्षेत्र में उन्हीं हैडर्स का उपयोग करें जिन्हें आप फ़िल्टर करना चाहते हैं। इन हैडर्स के नीचे फ़िल्टरिंग के मानदंड दर्ज करें। "OR" लॉजिक के लिए आप एक ही हैडर के नीचे कई मानदंड दर्ज कर सकते हैं, या "AND" लॉजिक के लिए अलग-अलग पंक्तियों में।

• डेटा चुनें: अपने डेटा सेट के भीतर किसी सेल पर क्लिक करें, या उस पूरे रेंज का चयन करें जिसे आप फ़िल्टर करना चाहते हैं।

• फ़िल्टर सेट करें: Excel रिबन में Data टैब पर क्लिक करें और Advanced Filter विकल्प पर जाएँ। Sort & Filter समूह में Advanced पर क्लिक करें। Advanced Filter डायलॉग बॉक्स में, चुनें कि आप सूची को उसी स्थान पर फ़िल्टर करना चाहते हैं या परिणामों को किसी अन्य स्थान पर कॉपी करना चाहते हैं। List range (आपका डेटा) निर्दिष्ट करें। Criteria range (जहाँ आपने फ़िल्टरिंग शर्तें सेट की हैं) निर्दिष्ट करें। फ़िल्टर लागू करने के लिए OK पर क्लिक करें।

मान लीजिए आपके पास Name, Age, City कॉलम वाला डेटा सेट है। आप उन लोगों को फ़िल्टर करना चाहते हैं जो New York में 30 वर्ष से ऊपर हैं या Los Angeles में कोई भी हो। आपकी क्राइटेरिया रेंज कुछ इस तरह दिख सकती है:

ऐड-इन्स इंस्टॉल करना

• Kutools for Excel: Kutools एक व्यापक ऐड-इन है जिसमें कई फ़ीचर्स शामिल हैं, जिनमें से एक रेगेक्स का उपयोग करके डेटा को ढूँढ़ना और बदलना है। यह Excel रिबन में एक नया टैब जोड़ता है जिसमें रेगेक्स फ़ंक्शंस सहित कई टूल्स होते हैं।

• RegEx Find/Replace: यह Excel के लिए एक ऐड-इन है जो आपको रेगुलर एक्सप्रेशंस का उपयोग करके टेक्स्ट ढूँढ़ने और बदलने देता है। यह Excel में इंटीग्रेट होता है और बिल्ट-इन विकल्प की तुलना में अधिक उन्नत ढूँढ़ें/बदलें कार्यक्षमता प्रदान करता है।

• PowerGREP: हालाँकि यह सीधे Excel ऐड-इन नहीं है, PowerGREP Excel फ़ाइलों के साथ काम कर सकता है। यह Windows के लिए एक शक्तिशाली grep टूल है जो टेक्स्ट और बाइनरी फ़ाइलों पर जटिल रेगेक्स ऑपरेशंस की अनुमति देता है। यह अधिकतर एक स्टैंडअलोन एप्लिकेशन है, लेकिन Excel के साथ मिलकर उन्नत रेगेक्स प्रोसेसिंग के लिए इस्तेमाल किया जा सकता है। हालाँकि, यह सस्ता नहीं है। एक सिंगल यूज़र लाइसेंस की कीमत $159 है और 100-यूज़र लाइसेंस के लिए कीमत $5,300 तक जाती है।

कुछ ऐड-इन्स सीधे Excel में Insert टैब > Get Add-ins के माध्यम से मिल सकते हैं।

अन्य को बाहरी स्रोत से डाउनलोड करना पड़ता है और उनकी इंस्टॉलेशन निर्देशों का पालन करना होता है।

कुछ विचार करने योग्य बातें:

• संगतता: सुनिश्चित करें कि ऐड-इन आपके Excel संस्करण और ऑपरेटिंग सिस्टम के साथ संगत है।
• सुरक्षा: केवल विश्वसनीय स्रोतों से ही ऐड-इन्स डाउनलोड और इंस्टॉल करें।
• कार्यात्मक आवश्यकताएँ: जिन विशिष्ट रेगेक्स कार्यों को आपको पूरा करना है, उन्हें ध्यान में रखें और उसी के अनुसार सबसे उपयुक्त ऐड-इन चुनें।

निष्कर्ष

अधिकांश मामलों में, FIND, SEARCH, और REPLACE जैसी नैटिव फ़ंक्शंस Excel में आपकी 90% टेक्स्ट मैनिपुलेशन आवश्यकताओं को पूरा कर देंगी। हालाँकि, शेष 10% के लिए, जहाँ अधिक जटिल स्ट्रिंग पैटर्न शामिल होते हैं, रेगेक्स बेहद मददगार साबित हो सकता है। लोकप्रिय प्रोग्रामिंग भाषाओं का उपयोग करके रेगुलर एक्सप्रेशंस लागू करने के बारे में अधिक जानने के लिए, हमारे Regular Expressions in Python और Intermediate Regular Expressions in R पाठ्यक्रम देखें।

Excel से जुड़ी हर चीज़ पर तेज़ी से पकड़ बनाने के लिए हमारा Excel Fundamentals स्किल ट्रैक देखें, जो आपको इस टूल में प्रवीण बनने के लिए आवश्यक कौशलों से रूबरू कराता है।