एक्सेल सूत्र: उन कोशिकाओं को हाइलाइट करें जिनमें कई में से एक है

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

=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0

सारांश

उन कक्षों को हाइलाइट करने के लिए जिनमें कई टेक्स्ट स्ट्रिंग्स हैं, आप SUMPRODUCT फ़ंक्शन के साथ ISNUMBER और SEARCH फ़ंक्शन के आधार पर एक सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, B4: B11 पर लागू सशर्त स्वरूपण इस सूत्र पर आधारित है:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0

स्पष्टीकरण

अंदर से बाहर की ओर कार्य करते हुए, सूत्र का यह भाग प्रत्येक सेल को B4: B11 में नामित श्रेणी "चीजों" के सभी मानों के लिए खोजता है:

--ISNUMBER(SEARCH(things,B4)

SEARCH फ़ंक्शन मान मिलने पर और यदि नहीं मिला तो #VALUE त्रुटि की स्थिति देता है। B4 के लिए, परिणाम इस तरह से एक सरणी में वापस आते हैं:

(8;#VALUE!;#VALUE!)

ISNUMBER फ़ंक्शन सभी परिणामों को TRUE या FALSE में बदल देता है:

(TRUE;FALSE;FALSE)

ISNUMBER के सामने डबल नकारात्मक TRUE / FALSE को 1/0 के लिए मजबूर करता है:

(1;0;0)

SUMPRODUCT फ़ंक्शन तब परिणाम जोड़ता है, जिसे शून्य के विरुद्ध परीक्षण किया जाता है:

=SUMPRODUCT((1;0;0))>0

किसी भी गैर-शून्य परिणाम का मतलब कम से कम एक मूल्य पाया गया, इसलिए सूत्र नियम को ट्रिगर करते हुए TRUE लौटाता है।

खाली चीजों को नजरअंदाज करें

नामित श्रेणी "चीजों" में खाली कोशिकाओं को अनदेखा करने के लिए, आप इस तरह से संशोधित फॉर्मूला आजमा सकते हैं:

=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0

यह तब तक काम करता है जब तक आप जिन पाठ मानों का परीक्षण कर रहे हैं उनमें स्ट्रिंग "FALSE" नहीं है। यदि वे करते हैं, तो आप IF फ़ंक्शन को एक मान शामिल करने के लिए विस्तारित कर सकते हैं यदि टेक्स्ट में गलत पता नहीं चलता है (अर्थात "zzzz", "####", आदि)

केस-संवेदी विकल्प

SEARCH केस-संवेदी नहीं है। साथ ही मामले की जांच करने के लिए, खोज को इस तरह से बदलें:

=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0

झूठे मेल को रोकना

इस दृष्टिकोण के साथ एक समस्या यह है कि आप सब्सट्रिंग्स की वजह से झूठे मैच देख सकते हैं जो लंबे शब्दों के अंदर दिखाई देते हैं। उदाहरण के लिए, यदि आप "ड्र" से मेल खाने की कोशिश करते हैं, तो आप "एंड्रिया", "ड्रिंक", "ड्राई" आदि को भी ढूंढ सकते हैं, क्योंकि "ड्र" इन शब्दों के अंदर दिखाई देता है। ऐसा इसलिए होता है क्योंकि खोज स्वचालित रूप से "समाहित" मैच करता है।

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

=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0

हालाँकि, यह विराम चिह्नों के कारण होने वाली समस्याओं को ठीक नहीं करेगा।

यदि आपको अधिक संपूर्ण समाधान की आवश्यकता है, तो एक विकल्प यह है कि आप पहले सहायक कॉलम में पाठ को सामान्य करें, साथ ही एक अग्रणी और अनुगामी स्थान भी जोड़ें। फिर आप रिक्त स्थान से घिरे पूरे शब्दों को खोज सकते हैं।