एक्सेल सूत्र: कई मान खोजें और बदलें -

सामान्य सूत्र

=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))

सारांश

एक सूत्र के साथ कई मानों को खोजने और बदलने के लिए, आप एक साथ कई SUBSTITUTE फ़ंक्शंस को घोंसला कर सकते हैं, और INDEX फ़ंक्शन का उपयोग करके किसी अन्य तालिका से जोड़े को खोजने / बदलने में फ़ीड कर सकते हैं। दिखाए गए उदाहरण में, हम 4 अलग-अलग खोज कर रहे हैं और संचालन को प्रतिस्थापित कर रहे हैं। G5 में सूत्र है:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))

जहाँ "खोज" नामांकित श्रेणी E5: E8 है, और "प्रतिस्थापित" नामांकित श्रेणी F5: F8 है। इस सूत्र को पढ़ने में आसान बनाने के तरीके के बारे में जानकारी के लिए नीचे देखें।

प्रस्तावना

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

स्पष्टीकरण

मूल में, सूत्र इस मूल पैटर्न के साथ, प्रत्येक प्रतिस्थापन को करने के लिए SUBSTITUTE फ़ंक्शन का उपयोग करता है:

=SUBSTITUTE(text,find,replace)

"टेक्स्ट" इनकमिंग वैल्यू है, "फाइंड" टेक्स्ट है जिसे देखना है, और "रिप्लेस" टेक्स्ट को रिप्लेस करना है। पाठ देखने और बदलने के लिए तालिका में दाईं ओर तालिका में संग्रहीत है, E5: F8, प्रति पंक्ति एक जोड़ी। बाईं ओर के मान नामित श्रेणी "खोज" में हैं और दाईं ओर के मान नामित श्रेणी "प्रतिस्थापित" में हैं। INDEX फ़ंक्शन का उपयोग "खोज" पाठ और "बदलें" पाठ दोनों को प्राप्त करने के लिए किया जाता है:

INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value

तो, पहला प्रतिस्थापन ("लाल" के लिए देखो) चलाने के लिए, "गुलाबी" के साथ बदलें) हम उपयोग करते हैं:

=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))

कुल मिलाकर, हम चार अलग-अलग प्रतिस्थापन चलाते हैं, और प्रत्येक बाद का SUBSTITUTE पिछले SUBSTICUTE के परिणाम से शुरू होता है:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))

पठनीयता के लिए लाइन ब्रेक

आप देखेंगे कि इस तरह के नेस्टेड फॉर्मूला को पढ़ना काफी मुश्किल है। लाइन ब्रेक जोड़कर, हम सूत्र को पढ़ने और बनाए रखने में बहुत आसान बना सकते हैं:

= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( B5, INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)), INDEX(find,4),INDEX(replace,4))

एक्सेल में सूत्र पट्टी अतिरिक्त सफेद स्थान और रेखा विराम को अनदेखा करती है, इसलिए उपरोक्त सूत्र सीधे में चिपकाए जा सकते हैं:

वैसे, फॉर्मूला बार के विस्तार और पतन के लिए एक कीबोर्ड शॉर्टकट है।

अधिक प्रतिस्थापन

जोड़े को बदलने / बदलने के लिए अधिक पंक्तियों को तालिका में जोड़ा जा सकता है। जब भी कोई जोड़ी जोड़ी जाती है, तो नई जोड़ी को शामिल करने के लिए सूत्र को अद्यतन करने की आवश्यकता होती है। यह सुनिश्चित करना भी महत्वपूर्ण है कि नामित श्रेणियां (यदि आप उनका उपयोग कर रहे हैं) को नए मूल्यों को आवश्यकतानुसार शामिल करने के लिए अपडेट किया गया है। वैकल्पिक रूप से, आप नामित श्रेणियों के बजाय डायनेमिक श्रेणियों के लिए एक उचित एक्सेल टेबल का उपयोग कर सकते हैं।

अन्य उपयोग

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