एक्सेल सूत्र: सेल से स्ट्रिप न्यूमेरिक कैरेक्टर -

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

(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))

सारांश

किसी पाठ स्ट्रिंग से संख्यात्मक वर्णों को निकालने के लिए, आप TEXTJOIN फ़ंक्शन के आधार पर एक सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, C5 में सूत्र है:

=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))

नोट: यह एक सरणी सूत्र है और इसे Excel 365 को छोड़कर नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।

स्पष्टीकरण

Excel के पास पाठ स्ट्रिंग में अक्षरों को सीधे एक सूत्र में रखने का एक तरीका नहीं है। वर्कअराउंड के रूप में, यह सूत्र उसी परिणाम को प्राप्त करने के लिए ROW और INDIRECT फ़ंक्शंस की मदद से MID फ़ंक्शन का उपयोग करता है। C5 में सूत्र, नीचे कॉपी किया गया है:

=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))

यह बहुत जटिल लग रहा है, लेकिन यह है कि हम B5 में सभी वर्णों की एक सरणी बनाते हैं, और यह देखने के लिए प्रत्येक वर्ण का परीक्षण करते हैं कि क्या यह एक संख्या है। यदि ऐसा है, तो हम मान को छोड़ देते हैं और इसे एक खाली स्ट्रिंग ("") से बदल देते हैं। यदि नहीं, तो हम गैर-संख्यात्मक वर्ण को "संसाधित" सरणी में जोड़ते हैं। अंत में, हम खाली मूल्यों की अनदेखी करते हुए, सभी वर्णों को एक साथ जोड़ने के लिए TEXTJOIN फ़ंक्शन (एक्सेल 2019 में नया) का उपयोग करते हैं।

अंदर से बाहर तक काम करते हुए, MID फ़ंक्शन का उपयोग बी 5 में पाठ को निकालने के लिए किया जाता है, एक समय में एक वर्ण। यहां महत्वपूर्ण और महत्वपूर्ण स्निपेट है:

ROW(INDIRECT("1:100"))

जो इस तरह से 100 संख्याओं वाले एक सरणी को फैलाता है:

(1,2,3,4,5,6,7,8… .99,100)

नोट: 100 प्रक्रिया करने के लिए अधिकतम वर्णों का प्रतिनिधित्व करता है। अपने डेटा के अनुरूप बदलें, या नीचे बताए अनुसार LEN फ़ंक्शन का उपयोग करें।

यह सरणी start_num तर्क के रूप में MID फ़ंक्शन में जाती है। के लिए NUM_CHARS , हम 1 का उपयोग करें।

MID फ़ंक्शन इस तरह एक सरणी देता है:

("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )

नोट: पठनीयता के लिए हटाए गए सरणी में अतिरिक्त आइटम।

इस सरणी में, हम शून्य जोड़ते हैं। यह एक सरल ट्रिक है जो एक्सेल को टेक्स्ट को एक संख्या तक ले जाने के लिए मजबूर करती है। संख्यात्मक पाठ मान जैसे "1", "2", "3", "4" आदि को त्रुटियों के बिना रूपांतरित किया जाता है, लेकिन गैर-संख्यात्मक मान विफल हो जाएंगे और #VALUE त्रुटि फेंक देंगे। हम इन त्रुटियों को पकड़ने के लिए ISERR फ़ंक्शन के साथ IF फ़ंक्शन का उपयोग करते हैं। जब हमें कोई त्रुटि दिखाई देती है, तो हम जानते हैं कि हमारे पास एक गैर-संख्यात्मक चरित्र है, इसलिए हम उस चरित्र को एक अन्य MID फ़ंक्शन के साथ संसाधित सरणी में लाते हैं:

MID(B5,ROW(INDIRECT("1:100")),1)

यदि हमें कोई त्रुटि नहीं मिलती है, तो हमें पता है कि हमारे पास एक संख्या है, इसलिए हम संख्या के स्थान पर एक खाली स्ट्रिंग ("") को सरणी में डालें।

अंतिम सरणी परिणाम टेक्स्ट 1 तर्क के रूप में TEXTJOIN फ़ंक्शन में जाता है। सीमांकक के लिए, हम एक खाली स्ट्रिंग ("") का उपयोग करते हैं और ign_empty के लिए हम TRUE की आपूर्ति करते हैं। TEXTJOIN तब सरणी में सभी गैर-रिक्त मानों को सम्मिलित करता है और परिणाम देता है।

सटीक सरणी लंबाई

100 नंबर की तरह इंडिरेक्ट में हार्डकोड करने के बजाय, आप LEN फंक्शन का उपयोग करके सेल की वास्तविक संख्या इस तरह से बना सकते हैं:

MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)

LEN सेल में वर्णों की संख्या को एक संख्या के रूप में लौटाता है, जिसका उपयोग 100 के बजाय किया जाता है। यह फ़ॉर्मूला किसी भी वर्ण को स्वचालित रूप से स्केल करने की अनुमति देता है।

अतिरिक्त स्थान हटाना

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

=TRIM(formula)

साथ में

Excel 365 में, नया SEQUENCE फ़ंक्शन ऊपर दिए गए ROW + INDIRECT कोड को बदल सकता है:

=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))

यहां, हम एक चरण में सही लंबाई की एक सरणी बनाने के लिए SEQUENCE + LEN का उपयोग करते हैं।

एलईटी के साथ

हम आगे LET फ़ंक्शन के साथ इस सूत्र को कारगर बना सकते हैं। क्योंकि सरणी SEQUENCE और LEN के साथ दो बार ऊपर बनाई गई है, हम सरणी को एक चर के रूप में परिभाषित कर सकते हैं, और इसे केवल एक बार बना सकते हैं:

=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))

यहाँ सरणी का मान सिर्फ एक बार सेट किया जाता है, फिर MID फ़ंक्शन के अंदर दो बार उपयोग किया जाता है।