एक्सेल में रेगुलर एक्सप्रेशन (RegExp) के साथ पार्स टेक्स्ट

एक्सेल में टेक्स्ट के साथ काम करते समय सबसे अधिक समय लेने वाला और निराशाजनक कार्यों में से एक है पदच्छेद - अल्फ़ान्यूमेरिक "दलिया" को घटकों में पार्स करना और उसमें से आवश्यक टुकड़े निकालना। उदाहरण के लिए:

• पते से ज़िप कोड निकालना (यह अच्छा है यदि ज़िप कोड हमेशा शुरुआत में है, लेकिन क्या होगा यदि यह नहीं है?)
• बैंक स्टेटमेंट में भुगतान के विवरण से चालान की संख्या और तारीख का पता लगाना
• प्रतिपक्षों की सूची में कंपनियों के प्रेरक विवरण से टिन की निकासी
• विवरण आदि में कार नंबर या लेख संख्या खोजें।
  

आमतौर पर ऐसे मामलों में, मैन्युअल रूप से पाठ में आधे घंटे के नीरस चयन के बाद, इस प्रक्रिया को स्वचालित करने के लिए किसी तरह विचार आने लगते हैं (विशेषकर यदि बहुत अधिक डेटा है)। कई समाधान हैं और जटिलता-दक्षता की अलग-अलग डिग्री के साथ:

• उपयोग बिल्ट-इन एक्सेल टेक्स्ट फंक्शन खोज-कट-गोंद पाठ के लिए: लेवसिमव (बाएं), RIGHT (सही), पीएसटीआर (मध्य), STsEPIT (जुड़ाव) और इसके अनुरूप, जोड़ना (संयुक्त पाठ), सटीक (सटीक) आदि। पाठ में स्पष्ट तर्क होने पर यह विधि अच्छी है (उदाहरण के लिए, सूचकांक हमेशा पते की शुरुआत में होता है)। अन्यथा, सूत्र बहुत अधिक जटिल हो जाते हैं और, कभी-कभी, यह सरणी सूत्रों तक भी आ जाता है, जो बड़ी तालिकाओं पर बहुत धीमा हो जाता है।
• का प्रयोग पाठ समानता ऑपरेटर की तरह कस्टम मैक्रो फ़ंक्शन में लिपटे Visual Basic से। यह आपको वाइल्डकार्ड वर्णों (*, #,?, आदि) का उपयोग करके अधिक लचीली खोज को लागू करने की अनुमति देता है। दुर्भाग्य से, यह उपकरण टेक्स्ट से वांछित सबस्ट्रिंग को नहीं निकाल सकता है - केवल यह जांचें कि क्या यह इसमें निहित है।

उपरोक्त के अलावा, एक और दृष्टिकोण है जो पेशेवर प्रोग्रामर, वेब डेवलपर्स और अन्य तकनीकी विशेषज्ञों के संकीर्ण दायरे में बहुत प्रसिद्ध है - यह है नियमित अभिव्यक्ति (नियमित अभिव्यक्ति = RegExp = "regexps" = "नियमित")। सीधे शब्दों में कहें, RegExp एक ऐसी भाषा है जहां टेक्स्ट में आवश्यक सबस्ट्रिंग की खोज करने, उन्हें निकालने या उन्हें अन्य टेक्स्ट के साथ बदलने के लिए विशेष वर्णों और नियमों का उपयोग किया जाता है।. रेगुलर एक्सप्रेशन एक बहुत शक्तिशाली और सुंदर टूल है जो परिमाण के क्रम में टेक्स्ट के साथ काम करने के अन्य सभी तरीकों से आगे निकल जाता है। कई प्रोग्रामिंग भाषाएं (सी #, पीएचपी, पर्ल, जावास्क्रिप्ट…) और टेक्स्ट एडिटर्स (वर्ड, नोटपैड ++…) रेगुलर एक्सप्रेशन का समर्थन करते हैं।

दुर्भाग्य से Microsoft Excel में RegExp समर्थन बॉक्स से बाहर नहीं है, लेकिन इसे VBA के साथ आसानी से ठीक किया जा सकता है। टैब से विजुअल बेसिक एडिटर खोलें विकासक (डेवलपर) या कीबोर्ड शॉर्टकट ऑल्ट+F11. फिर मेनू के माध्यम से नया मॉड्यूल डालें सम्मिलित करें - मॉड्यूल और वहां निम्न मैक्रो फ़ंक्शन के टेक्स्ट को कॉपी करें:

पब्लिक फंक्शन RegExpExtract (स्ट्रिंग के रूप में पाठ, स्ट्रिंग के रूप में पैटर्न, पूर्णांक के रूप में वैकल्पिक आइटम = 1) त्रुटि पर स्ट्रिंग के रूप में GoTo ErrHandl सेट रेगेक्स = CreateObject ("VBScript.RegExp") regex.Pattern = पैटर्न regex.Global = True यदि regex.Test (पाठ) फिर मैच सेट करें = regex.Execute (पाठ) RegExpExtract = मैचों।  

अब हम विजुअल बेसिक एडिटर को बंद कर सकते हैं और अपनी नई सुविधा को आजमाने के लिए एक्सेल में वापस आ सकते हैं। इसका सिंटैक्स निम्नलिखित है:

=RegExpExtract (Txt; पैटर्न; आइटम)

जहां

• TXT - टेक्स्ट वाला एक सेल जिसे हम चेक कर रहे हैं और जिससे हम अपनी जरूरत के सबस्ट्रिंग को निकालना चाहते हैं
• पैटर्न - खोज के विकल्प के लिए मुखौटा (पैटर्न)
• मद - निकाले जाने वाले सबस्ट्रिंग की अनुक्रम संख्या, यदि उनमें से कई हैं (यदि निर्दिष्ट नहीं है, तो पहली घटना प्रदर्शित होती है)

यहां सबसे दिलचस्प बात, निश्चित रूप से, पैटर्न है - RegExp की "भाषा में" विशेष वर्णों की एक टेम्पलेट स्ट्रिंग, जो निर्दिष्ट करती है कि हम वास्तव में क्या और कहां खोजना चाहते हैं। आरंभ करने के लिए यहां सबसे बुनियादी हैं:

यदि हम एक निश्चित संख्या में वर्णों की तलाश कर रहे हैं, उदाहरण के लिए, छह अंकों का डाक कोड या सभी तीन-अक्षर वाले उत्पाद कोड, तो हम बचाव में आते हैं परिमाणकों or परिमाणकों विशेष भाव हैं जो खोजे जाने वाले वर्णों की संख्या निर्दिष्ट करते हैं। इससे पहले आने वाले चरित्र पर क्वांटिफायर लागू होते हैं:

अब आइए सबसे दिलचस्प भाग पर चलते हैं - निर्मित फ़ंक्शन के अनुप्रयोग का विश्लेषण और हमने जीवन से व्यावहारिक उदाहरणों पर पैटर्न के बारे में क्या सीखा।

टेक्स्ट से नंबर निकालना

शुरू करने के लिए, आइए एक साधारण मामले का विश्लेषण करें - आपको अल्फ़ान्यूमेरिक दलिया से पहला नंबर निकालने की आवश्यकता है, उदाहरण के लिए, मूल्य सूची से निर्बाध बिजली आपूर्ति की शक्ति:

रेगुलर एक्सप्रेशन के पीछे तर्क सरल है: d मतलब कोई अंक, और क्वांटिफायर + कहते हैं कि उनकी संख्या एक या अधिक होनी चाहिए। फ़ंक्शन के सामने डबल माइनस की आवश्यकता होती है "ऑन द फ्लाई" निकाले गए वर्णों को संख्या-के-पाठ से पूर्ण संख्या में परिवर्तित करने के लिए।

पोस्टकोड

पहली नज़र में, यहाँ सब कुछ सरल है - हम एक पंक्ति में ठीक छह अंकों की तलाश कर रहे हैं। हम एक विशेष चरित्र का उपयोग करते हैं d अंक और परिमाणक के लिए 6 {} वर्णों की संख्या के लिए:

हालांकि, एक स्थिति संभव है, जब पंक्ति में सूचकांक के बाईं ओर, एक पंक्ति में संख्याओं का एक और बड़ा सेट (फोन नंबर, टिन, बैंक खाता, आदि) तब हमारा नियमित सीजन पहले 6 को हटा देगा। इससे अंक, यानी सही ढंग से काम नहीं करेंगे:

ऐसा होने से रोकने के लिए, हमें अपने रेगुलर एक्सप्रेशन के किनारों के आसपास एक संशोधक जोड़ने की आवश्यकता है b एक शब्द के अंत का संकेत। इससे एक्सेल को यह स्पष्ट हो जाएगा कि जिस खंड (सूचकांक) की हमें आवश्यकता है वह एक अलग शब्द होना चाहिए, न कि किसी अन्य खंड (फोन नंबर) का हिस्सा:

फ़ोन

पाठ में फ़ोन नंबर खोजने में समस्या यह है कि संख्या लिखने के लिए बहुत सारे विकल्प हैं - हाइफ़न के साथ और बिना, रिक्त स्थान के माध्यम से, कोष्ठक में क्षेत्र कोड के साथ या बिना, आदि। इसलिए, मेरी राय में, यह आसान है पहले कई नेस्टेड फ़ंक्शंस का उपयोग करके इन सभी वर्णों को स्रोत टेक्स्ट से साफ़ करें विकल्प (स्थानापन्न)ताकि यह एक साथ एक पूरे में चिपक जाए, और फिर एक आदिम नियमित के साथ घ{11} एक पंक्ति में 11 अंक निकालें:

आईटीएन

यह यहाँ थोड़ा अधिक जटिल है, क्योंकि TIN (हमारे देश में) 10-अंकों (कानूनी संस्थाओं के लिए) या 12-अंकों (व्यक्तियों के लिए) हो सकता है। यदि आप विशेष रूप से दोष नहीं पाते हैं, तो नियमित से संतुष्ट होना काफी संभव है घ{10,12}, लेकिन, कड़ाई से बोलते हुए, यह सभी संख्याओं को 10 से 12 वर्णों तक खींच लेगा, यानी और गलती से 11 अंक दर्ज कर दिया जाएगा। तार्किक या ऑपरेटर द्वारा जुड़े दो पैटर्न का उपयोग करना अधिक सही होगा | (सीधी खड़ी रेखा):

कृपया ध्यान दें कि क्वेरी में हम पहले 12-बिट संख्याओं की तलाश करते हैं, और उसके बाद ही 10-बिट संख्याओं के लिए। यदि हम अपने रेगुलर एक्सप्रेशन को दूसरी तरह से लिखते हैं, तो यह सभी के लिए, यहां तक ​​कि लंबे 12-बिट TIN, केवल पहले 10 वर्णों के लिए खींचेगा। अर्थात्, पहली शर्त शुरू होने के बाद, आगे सत्यापन नहीं किया जाता है:

यह ऑपरेटर के बीच मूलभूत अंतर है | एक मानक एक्सेल तर्क समारोह से OR (या), जहां तर्कों को पुनर्व्यवस्थित करने से परिणाम नहीं बदलता है।

उत्पाद एसकेयू

कई कंपनियों में, वस्तुओं और सेवाओं को विशिष्ट पहचानकर्ता सौंपे जाते हैं - लेख, एसएपी कोड, एसकेयू, आदि। यदि उनके संकेतन में तर्क है, तो उन्हें नियमित अभिव्यक्तियों का उपयोग करके किसी भी पाठ से आसानी से निकाला जा सकता है। उदाहरण के लिए, यदि हम जानते हैं कि हमारे लेखों में हमेशा तीन बड़े अंग्रेजी अक्षर, एक हाइफ़न और बाद में तीन अंकों की संख्या होती है, तो:

टेम्पलेट के पीछे तर्क सरल है। [एजेड] - लैटिन वर्णमाला के किसी भी बड़े अक्षर का मतलब है। अगला क्वांटिफायर 3 {} कहते हैं कि हमारे लिए यह महत्वपूर्ण है कि ठीक ऐसे तीन अक्षर हों। हाइफ़न के बाद, हम तीन अंकों की प्रतीक्षा कर रहे हैं, इसलिए हम अंत में जोड़ते हैं घ{3}

नकद राशि

पिछले पैराग्राफ के समान ही, आप माल के विवरण से कीमतों (लागत, वैट ...) को भी निकाल सकते हैं। यदि मौद्रिक राशियाँ, उदाहरण के लिए, एक हाइफ़न के साथ इंगित की जाती हैं, तो:

पैटर्न d परिमाणक के साथ + हाइफ़न तक किसी भी संख्या की खोज करता है, और घ{2} के बाद पेनीज़ (दो अंक) की तलाश करेंगे।

यदि आपको मूल्य नहीं, बल्कि वैट निकालने की आवश्यकता है, तो आप हमारे RegExpExtract फ़ंक्शन के तीसरे वैकल्पिक तर्क का उपयोग कर सकते हैं, जो निकाले जाने वाले तत्व की क्रमिक संख्या निर्दिष्ट करता है। और, ज़ाहिर है, आप फ़ंक्शन को बदल सकते हैं विकल्प (स्थानापन्न) परिणामों में, मानक दशमलव विभाजक को हाइफ़न करें और शुरुआत में एक डबल माइनस जोड़ें ताकि एक्सेल सामान्य संख्या के रूप में पाए गए वैट की व्याख्या करे:

कार प्लेट नंबर

यदि आप विशेष वाहन, ट्रेलर और अन्य मोटरसाइकिल नहीं लेते हैं, तो मानक कार नंबर को "अक्षर - तीन नंबर - दो अक्षर - क्षेत्र कोड" सिद्धांत के अनुसार पार्स किया जाता है। इसके अलावा, क्षेत्र कोड 2- या 3-अंकीय हो सकता है, और केवल वे जो लैटिन वर्णमाला के समान दिखते हैं, उन्हें अक्षरों के रूप में उपयोग किया जाता है। इस प्रकार, निम्नलिखित नियमित अभिव्यक्ति हमें पाठ से संख्याएँ निकालने में मदद करेगी:

पहर

एचएच: एमएम प्रारूप में समय निकालने के लिए, निम्नलिखित नियमित अभिव्यक्ति उपयुक्त है:

बृहदान्त्र के टुकड़े के बाद [0-5]डी, जैसा कि यह पता लगाना आसान है, किसी भी संख्या को 00-59 की सीमा में सेट करता है। कोष्ठक में बृहदान्त्र से पहले, दो पैटर्न काम करते हैं, एक तार्किक OR (पाइप) द्वारा अलग किया जाता है:

• [0-1]डी - 00-19 . की सीमा में कोई भी संख्या
• 2 [0-3] - 20-23 . की सीमा में कोई भी संख्या

प्राप्त परिणाम के लिए, आप अतिरिक्त रूप से मानक एक्सेल फ़ंक्शन लागू कर सकते हैं TIME (टीम)इसे एक समय प्रारूप में परिवर्तित करने के लिए जो कार्यक्रम के लिए समझ में आता है और आगे की गणना के लिए उपयुक्त है।

पासवर्ड चेक

मान लीजिए कि हमें उपयोगकर्ताओं द्वारा आविष्कृत पासवर्डों की सूची की शुद्धता के लिए जाँच करने की आवश्यकता है। हमारे नियमों के अनुसार, पासवर्ड में केवल अंग्रेजी अक्षर (लोअरकेस या अपरकेस) और संख्याएँ हो सकती हैं। रिक्त स्थान, अंडरस्कोर और अन्य विराम चिह्नों की अनुमति नहीं है।

निम्नलिखित सरल नियमित अभिव्यक्ति का उपयोग करके जाँच का आयोजन किया जा सकता है:

वास्तव में, इस तरह के पैटर्न के साथ हमें आवश्यकता होती है कि शुरुआत के बीच (^) और अंत ($) हमारे पाठ में वर्गाकार कोष्ठकों में दिए गए सेट से केवल वर्ण थे। यदि आपको पासवर्ड की लंबाई (उदाहरण के लिए, कम से कम 6 वर्ण) की जांच करने की भी आवश्यकता है, तो क्वांटिफायर + फॉर्म में अंतराल "छह या अधिक" द्वारा प्रतिस्थापित किया जा सकता है {6,}:

पते से शहर

मान लीजिए कि हमें पता बार से शहर को खींचने की जरूरत है। नियमित कार्यक्रम "जी" से पाठ निकालने में मदद करेगा। अगले अल्पविराम के लिए:

आइए इस पैटर्न पर करीब से नज़र डालें।

यदि आपने उपरोक्त पाठ पढ़ा है, तो आप पहले ही समझ चुके हैं कि नियमित अभिव्यक्तियों में कुछ वर्ण (अवधि, तारांकन, डॉलर के संकेत, आदि) का एक विशेष अर्थ होता है। यदि आपको इन पात्रों को स्वयं देखने की आवश्यकता है, तो वे एक बैकस्लैश (कभी-कभी कहा जाता है) से पहले होते हैं परिरक्षण) इसलिए, खंड "जी" की खोज करते समय। हमें नियमित अभिव्यक्ति में लिखना है श्री अगर हम एक प्लस की तलाश में हैं, तो + इत्यादि

हमारे टेम्पलेट में अगले दो अक्षर, डॉट और क्वांटिफायर तारांकन, किसी भी वर्ण की किसी भी संख्या, यानी किसी भी शहर के नाम के लिए खड़े हैं।

टेम्पलेट के अंत में एक अल्पविराम है, क्योंकि हम "g" से टेक्स्ट ढूंढ रहे हैं। एक अल्पविराम को। लेकिन पाठ में कई अल्पविराम हो सकते हैं, है ना? न केवल शहर के बाद, बल्कि गली, मकान आदि के बाद भी। इनमें से किस पर हमारा अनुरोध रुकेगा? इसी के लिए प्रश्नवाचक चिन्ह है। इसके बिना, हमारा रेगुलर एक्सप्रेशन सबसे लंबे समय तक संभव स्ट्रिंग को बाहर निकाल देगा:

नियमित अभिव्यक्तियों के संदर्भ में, ऐसा पैटर्न "लालची" है। स्थिति को ठीक करने के लिए, एक प्रश्न चिह्न की आवश्यकता होती है - यह क्वांटिफायर बनाता है जिसके बाद यह "कठोर" हो जाता है - और हमारी क्वेरी टेक्स्ट को केवल "g" के बाद पहले काउंटर कॉमा तक ले जाती है:

पूरे पथ से फ़ाइल नाम

फ़ाइल नाम को पूर्ण पथ से निकालने के लिए एक और बहुत ही सामान्य स्थिति है। फ़ॉर्म की एक साधारण नियमित अभिव्यक्ति यहाँ मदद करेगी:

यहां चाल यह है कि खोज, वास्तव में, विपरीत दिशा में होती है - अंत से शुरुआत तक, क्योंकि हमारे टेम्पलेट के अंत में है $, और हम इससे पहले दाईं ओर से पहले बैकस्लैश तक सब कुछ ढूंढ रहे हैं। बैकस्लैश बच निकला है, पिछले उदाहरण में डॉट की तरह।

PS

"अंत में" मैं स्पष्ट करना चाहता हूं कि उपरोक्त सभी उन सभी संभावनाओं का एक छोटा सा हिस्सा है जो नियमित अभिव्यक्ति प्रदान करते हैं। उनके उपयोग के लिए बहुत सारे विशेष पात्र और नियम हैं, और इस विषय पर पूरी किताबें लिखी गई हैं (मैं शुरुआत के लिए कम से कम इसे अनुशंसा करता हूं)। एक तरह से रेगुलर एक्सप्रेशन लिखना लगभग एक कला है। लगभग हमेशा, एक आविष्कृत नियमित अभिव्यक्ति में सुधार या पूरक किया जा सकता है, जिससे यह अधिक सुरुचिपूर्ण या इनपुट डेटा की एक विस्तृत श्रृंखला के साथ काम करने में सक्षम हो जाता है।

अन्य लोगों के रेगुलर एक्सप्रेशन का विश्लेषण और विश्लेषण करने या अपने स्वयं के डीबग करने के लिए, कई सुविधाजनक ऑनलाइन सेवाएं हैं: रेगेक्स101, RegExr और अधिक

दुर्भाग्य से, क्लासिक रेगुलर एक्सप्रेशन की सभी सुविधाएँ VBA (उदाहरण के लिए, रिवर्स सर्च या POSIX क्लासेस) में समर्थित नहीं हैं और सिरिलिक के साथ काम कर सकती हैं, लेकिन मुझे लगता है कि जो कुछ है वह पहली बार आपको खुश करने के लिए पर्याप्त है।

यदि आप विषय के लिए नए नहीं हैं, और आपके पास साझा करने के लिए कुछ है, तो नीचे दी गई टिप्पणियों में एक्सेल में काम करते समय नियमित अभिव्यक्तियों को उपयोगी छोड़ दें। एक दिमाग अच्छा है, लेकिन दो जूते एक जोड़ी हैं!

• SUBSTITUTE फ़ंक्शन के साथ टेक्स्ट को बदलना और साफ़ करना
• पाठ में लैटिन वर्णों की खोज और हाइलाइटिंग
• निकटतम समान पाठ खोजें (इवानोव = इवोनोव = इवानोफ़, आदि)