Excel में स्ट्रिंग से नंबर कैसे निकालें: सही तरीका चुनें

Excel में डेटा साफ़ करते समय, आपको अल्फ़ान्यूमेरिक स्ट्रिंग्स से नंबर निकालने का काम मिल सकता है। यह समस्या तब आम होती है जब डेटा में नंबर स्ट्रिंग्स के भीतर एम्बेडेड हों, जैसे प्रोडक्ट कोड या सर्वे प्रतिक्रियाएँ।

इस लेख में, मैं आपको अलग-अलग उपयोग-स्थितियों के लिए RIGHT(), LEN(), और FIND() जैसी फ़ॉर्मूलों का उपयोग करके स्ट्रिंग्स से नंबर निकालने के विभिन्न तरीकों को दिखाऊँगा। यदि आप कुछ बुनियादी बातों से अपरिचित हैं, तो मैं सुझाव दूँगा कि आप हमारा Introduction to Excel कोर्स लें ताकि Excel में मजबूत नींव बने। आप हमारा Data Preparation in Excel भी देखें ताकि आप कच्चे डेटा को ट्रांसफॉर्म करना सीख सकें।

Excel फ़ॉर्मूले: स्ट्रिंग से नंबर निकालना

Excel स्ट्रिंग्स से नंबर निकालने के लिए विभिन्न फ़ॉर्मूला-आधारित तरीके देता है। हर तरीका इस पर निर्भर करता है कि स्ट्रिंग में नंबर कहाँ स्थित है। यदि आपके पास समय कम है, तो निःसंकोच उस सेक्शन पर जाएँ जो आपकी तुरंत आवश्यकता को हल करता है।

Excel में स्ट्रिंग की शुरुआत से नंबर निकालना

कभी-कभी ऐसे परिदृश्य होते हैं जहाँ नंबर स्ट्रिंग की शुरुआत में होते हैं। ऐसे मामलों में, आप LEFT() और FIND() का उपयोग करके स्ट्रिंग से नंबर निकालेंगे। जब आप इन फ़ंक्शनों को एक फ़ॉर्मूला में मिलाते हैं, तो नंबर निम्नलिखित लॉजिक से निकाले जाते हैं:

• FIND(): स्ट्रिंग में पहले अक्षर की पोज़ीशन ढूँढता है।

• LEFT(): स्ट्रिंग की शुरुआत से पहले अक्षर की पोज़ीशन से एक कम तक के कैरेक्टर्स निकालता है।

नीचे दिए फ़ॉर्मूला का पहला भाग स्ट्रिंग में पहला अक्षर ढूँढता है। फिर, दूसरा भाग उस अक्षर से पहले की सारी सामग्री निकाल देता है, जिससे शुरुआती नंबर रह जाते हैं। यदि कोई अक्षर नहीं है, तो यह पूरी स्ट्रिंग लौटा देता है।

=LEFT(A2, FIND(MID(A2, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)संचालित द्वारा

Excel में स्ट्रिंग की शुरुआत से नंबर निकालना। छवि: लेखक द्वारा।

यह तरीका उन डेटासेट्स के लिए उपयुक्त है जहाँ ऑर्डर नंबर, मेंबर आईडी, और बैच नंबर जैसे न्यूमेरिक प्रीफ़िक्स आम होते हैं।

Excel में स्ट्रिंग के अंत से नंबर निकालना

यदि आप Excel में स्ट्रिंग के अंत से नंबर निकालना चाहें, तो आपको RIGHT(), LEN(), और FIND() फ़ंक्शनों का उपयोग करना होगा। मान लें कि आपके Excel शीट में नीचे जैसा डेटा है। आप फ़ंक्शनों को इस प्रकार मिलाएँगे कि:

• FIND(): स्ट्रिंग में पहले अंक की पोज़ीशन ढूँढता है।

• LEN(): पहले अंक से स्ट्रिंग के अंत तक के कैरेक्टरों की संख्या गिनता है।

• RIGHT(): पहले अंक से स्ट्रिंग के अंत तक के कैरेक्टर निकालता है।

नीचे दिया फ़ॉर्मूला शुरुआती टेक्स्ट को हटा देता है और स्ट्रिंग में पहला नंबर तथा उसके बाद की सारी सामग्री लौटा देता है।

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)संचालित द्वारा

Excel में स्ट्रिंग के अंत से नंबर निकालना। छवि: लेखक द्वारा।

यह तरीका उन डेटासेट्स में सबसे उपयोगी है जहाँ अल्फ़ान्यूमेरिक कैरेक्टर लगातार होते हैं, जैसे प्रोडक्ट SKU, ऑर्डर आईडी, और सीरियल नंबर।

Excel में स्ट्रिंग के किसी भी स्थान से नंबर निकालना

यदि आपके डेटासेट में नंबर स्ट्रिंग के भीतर कहीं भी आ सकते हैं, तो आपको TEXTJOIN(), MID(), और ROW() जैसी एडवांस्ड फ़ॉर्मूलों की ज़रूरत होगी।

इन फ़ंक्शनों को एक फ़ॉर्मूला में मिलाने से निम्नलिखित लॉजिक के साथ स्ट्रिंग से नंबर निकलेगा: 

• ROW() और MID(): हर कैरेक्टर पर इटरेट करके जाँचते हैं कि वह संख्यात्मक है या नहीं।

• IFERROR(): पहचाने गए नंबरों को अन्य कैरेक्टर्स से अलग करने के लिए लॉजिकल टेस्ट।

• TEXTJOIN(): निकाले गए सभी संख्यात्मक कैरेक्टर्स को एक सिंगल नंबर स्ट्रिंग में जोड़ता है।

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))संचालित द्वारा

स्ट्रिंग के किसी भी स्थान से नंबर निकालें। छवि: लेखक द्वारा।

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

अनचाहे कैरेक्टर्स को फ़िल्टर करके नंबर निकालना

कभी-कभी आपका डेटा बिखरा हुआ होता है। सौभाग्य से, आप SUBSTITUTE() और TEXTJOIN() फ़ंक्शनों को मिला सकते हैं। बस ये चरण अपनाएँ:

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

• TEXTJOIN(): फिर TEXTJOIN() फ़ंक्शन का उपयोग शेष कैरेक्टर्स (जो अब केवल नंबर होने चाहिए) को एक सिंगल स्ट्रिंग में जोड़ने के लिए किया जा सकता है।

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))संचालित द्वारा

कैरेक्टर्स को फ़िल्टर करके स्ट्रिंग से नंबर निकालना। छवि: लेखक द्वारा।

Excel में कब आपको स्ट्रिंग से नंबर निकालने की ज़रूरत पड़ सकती है

अब अभ्यास के लिए दो छोटे केस स्टडी देखते हैं, जिनमें स्ट्रिंग्स से नंबर निकाले जाएँगे।

उदाहरण 1: प्रोडक्ट कोड से नंबर निकालना

मान लें कि आप एक प्रोडक्ट इन्वेंट्री मैनेज कर रहे हैं जहाँ हर आइटम एक यूनिक प्रोडक्ट कोड से पहचाना जाता है। ये प्रोडक्ट कोड अल्फ़ान्यूमेरिक स्ट्रिंग्स होते हैं, जैसे "PROD1234", "ITEM5678", या "SKU91011"।

तो, अल्फ़ान्यूमेरिक प्रोडक्ट कोड्स से न्यूमेरिक Product ID निकालने के लिए, हम नंबर की पोज़ीशन के आधार पर फ़ंक्शनों का उपयोग करते हैं। याद रखें, यदि नंबर अंत में हैं, तो RIGHT(), LEN(), और FIND() लगाएँ। यदि वे शुरुआत में आते हैं, तो LEFT() और FIND() का उपयोग करें।

नीचे दिए उदाहरण में प्रोडक्ट कोड्स के दाएँ हिस्से से संख्यात्मक भाग निकाला गया है।

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))संचालित द्वारा

Excel में प्रोडक्ट कोड से नंबर निकालना। छवि: लेखक द्वारा।

उदाहरण 2: एम्बेडेड नंबर वाला सर्वे डेटा

कभी-कभी सर्वे डेटा में टेक्स्ट और नंबर दोनों होते हैं, जैसे "Rated 5 out of 10", "Score: 8", या "3 stars"। औसत निकालने या रुझान पहचानने से पहले थोड़ी तैयारी करनी पड़ेगी।

क्योंकि नंबर टेक्स्ट में कहीं भी आ सकते हैं, इसलिए TEXTJOIN(), MID(), और ROW() का उपयोग करने वाला एक डायनेमिक फ़ॉर्मूला ज़रूरी है।

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))संचालित द्वारा

Excel में किसी भी पोज़ीशन से नंबर निकालना। छवि: लेखक द्वारा।

स्पेशल केसों के लिए कॉपी-पेस्ट कोड उदाहरण

अंतिम सेक्शन में, मैं आपको कुछ अतिरिक्त फ़ॉर्मूले देना चाहता था जो अलग-अलग और विशेष परिस्थितियों को संभालते हैं। बेझिझक कॉपी-पेस्ट करें, और आशा है कि यह आपकी किसी विशेष समस्या को हल कर देगा। 

पहला सतत संख्या-समूह निकालें

यदि आपकी स्ट्रिंग में कई न्यूमेरिक सीक्वेंस हों, लेकिन आपको केवल पहला समूह चाहिए, तो LET() और SEQUENCE() फ़ंक्शनों का उपयोग करते हुए यह समाधान आज़माएँ:

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)संचालित द्वारा

Excel में स्ट्रिंग से पहला सतत संख्या-समूह निकालना। छवि: लेखक द्वारा।

कोष्ठकों में बंद नंबर निकालना

जहाँ नंबर कोष्ठकों के अंदर आते हैं, वहाँ यह उपयोग करें:

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)संचालित द्वारा

Excel में कोष्ठकों में बंद नंबर निकालें। छवि: लेखक द्वारा।

किसी विशेष कैरेक्टर के बाद के नंबर निकालना

जब आपके डेटा में कॉलन डिलिमिटर के रूप में उपयोग होता है (जैसे, "Score: 8"), तो नंबर को इस तरह कैप्चर करें:

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))संचालित द्वारा

Excel में विशेष कैरेक्टर के बाद का नंबर निकालें। छवि: लेखक द्वारा।

यह कॉलन के बाद की हर चीज़ निकाल लेता है। यदि आपको संख्यात्मक मान चाहिए तो परिणाम को VALUE() से लपेटें। यदि आपका डिलिमिटर अलग है तो कॉलन बदलें।

सभी नंबर निकालें और उन्हें एक सिंगल वैल्यू में जोड़ें

सभी गैर-अंकीय कैरेक्टर्स हटाकर स्ट्रिंग के हर संख्यात्मक कैरेक्टर को जोड़ने के लिए यह उपयोग करें:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))संचालित द्वारा

Excel में सभी नंबर निकालकर उन्हें एक सिंगल वैल्यू में जोड़ना। छवि: लेखक द्वारा।

टिप: Excel के पुराने संस्करणों में इसे एरे फ़ॉर्मूला के रूप में दर्ज करें (Ctrl+Shift+Enter)।

दशमलव बिंदु सहित नंबर निकालना

ऐसे न्यूमेरिक स्ट्रिंग्स के लिए जिनमें दशमलव हो सकता है (जैसे, "Price: 45.99"), यह फ़ॉर्मूला अंकों और दशमलव बिंदु दोनों को बनाए रखता है:

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))संचालित द्वारा

Excel में दशमलव बिंदु सहित नंबर निकालना। छवि: लेखक द्वारा।

ध्यान दें: यह फ़ॉर्मूला मानता है कि केवल एक ही दशमलव बिंदु मौजूद है।

निष्कर्ष

स्ट्रिंग्स से नंबर निकालना डेटा साफ़ करने की एक महत्वपूर्ण तकनीक है। एक डेटा विश्लेषक के रूप में, मैं आपको प्रोत्साहित करता हूँ कि आप इस लेख में चर्चा किए गए विभिन्न फ़ॉर्मूलों को लागू करना सीखें।

यदि आप अपनी Excel स्किल्स आगे बढ़ाना चाहते हैं, तो मैं सुझाव दूँगा कि आप हमारे Data Analysis in Excel और Data Visualization in Excel कोर्स लें ताकि डेटा विश्लेषण और प्रस्तुति में महारत हासिल कर सकें। हमारा Financial Modeling in Excel कोर्स भी आज़माएँ, जो अनेक अवसर खोल सकता है।