19 एक्सेल में व्यावहारिक डेटा सफाई तकनीक

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

आप नीचे दिए गए लिंक से प्रदर्शन के लिए उपयोग की गई कार्यपुस्तिका डाउनलोड कर सकते हैं।

19 एक्सेल में डेटा क्लीनिंग तकनीक जो काम आएगी

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

आपके डेटा में डुप्लिकेट पंक्तियाँ हो सकती हैं, जो भी कारण हों। अधिकांश समय, आपको डुप्लिकेट पंक्तियों को समाप्त करने की आवश्यकता होती है। पुराने दिनों में, डुप्लिकेट डेटा को हटाना एक मैन्युअल कार्य था- हालाँकि हटाने का कार्य उन्नत तकनीकों के साथ किया जा सकता था। लेकिन डुप्लिकेट निकालें . के साथ आदेश, दोहराव को हटाना अब एक आसान काम है। डुप्लिकेट निकालें कमांड एक्सेल 2007 में पेश किया गया था।

आइए कॉलम को बगल में दोहराएं और बाद में उनकी तुलना करने के लिए डुप्लिकेट को हटा दें।

डुप्लिकेट निकालने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, उस कॉलम को चुनें जिससे आप डुप्लीकेट हटाना चाहते हैं।
• अब डेटा पर जाएं अपने रिबन पर टैब करें।
• फिर डुप्लिकेट निकालें . चुनें डेटा टूल . से वहाँ समूह।

• उसके बाद, वर्तमान चयन के साथ जारी रखें . चुनें पॉप-अप बॉक्स में विकल्प चुनें और डुप्लिकेट निकालें . पर क्लिक करें ।

• अगला, ठीक पर क्लिक करें ।

यह चयन से सभी डुप्लीकेट हटा देगा।

कभी-कभी डुप्लिकेट मानों को पूरी तरह से हटाने के बजाय, उन्हें हाइलाइट करना सहायक होता है। यहां हम आपको अलग-अलग परिदृश्य और डुप्लिकेट मानों को हाइलाइट करने के तरीके दिखाएंगे।

सशर्त स्वरूपण का उपयोग करना

पहले भाग में, हम सशर्त स्वरूपण . का उपयोग करने जा रहे हैं डुप्लिकेट मानों को हाइलाइट करने की सुविधा। हम इसे निम्नलिखित डेटासेट पर करेंगे।

हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, उन सेल का चयन करें जिन्हें हम डुप्लिकेट करना चाहते हैं। इस मामले में, यह D5:D18 है।
• दूसरा, होम पर जाएं> सशर्त स्वरूपण चुनें> हाइलाइट सेल नियम पर जाएं> डुप्लिकेट मान चुनें ।

• आखिरकार, एक डुप्लिकेट मान विंडो दिखाई देगी।
• तीसरा, डुप्लिकेट choose चुनें ।
• चौथा, मानों . में से कोई भी रंग विकल्प चुनें इस मामले में, हमने गहरे लाल पाठ के साथ हल्का लाल भरण . चुना है ।

• परिणामस्वरूप, हम देखेंगे कि D . में डुप्लिकेट मान कॉलम हाइलाइट किया गया है।

पहली घटना सहित डुप्लिकेट को हाइलाइट करना

अब, उसी डेटासेट के लिए, हम पहली घटना सहित डुप्लिकेट मानों को हाइलाइट करने जा रहे हैं। यह वही है जैसा कि पिछले उल्लेख किया गया है। लेकिन हम यहां COUNITF फ़ंक्शन . के साथ एक अलग मार्ग का अनुसरण करेंगे . यह तरीका कैसे काम करता है यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, नए स्वरूपण नियम पर जाएं सशर्त स्वरूपण . पर जाकर विंडो

• दूसरा, यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें चुनें ।
• तीसरा, सूत्र बॉक्स में निम्न सूत्र लिखें।

=COUNTIF($D$5:$E$16,$D5)>1

• परिणामस्वरूप, हम देखेंगे कि D पहली घटनाओं सहित, डुप्लीकेट के लिए कॉलम हाइलाइट किया गया है।

पहली घटना को छोड़कर डुप्लिकेट को हाइलाइट करना

अब हम डुप्लिकेट मानों की पहचान करने के लिए सबसे उपयोगी तकनीकों में से एक पर चर्चा करने जा रहे हैं। यह पहली घटनाओं को छोड़कर उन्हें उजागर नहीं करता है। यह उपयोगी है यदि हम सभी डुप्लिकेट मानों के बजाय केवल आवर्ती मानों का निरीक्षण करना चाहते हैं। पिछले वाले की तरह ही, हम COUNTIF फ़ंक्शन का उपयोग करेंगे यहाँ।

हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• ऐसा करने के लिए, सबसे पहले, नए स्वरूपण नियम पर जाएं सशर्त स्वरूपण . के माध्यम से विकल्प जैसा कि हमने पहले चर्चा की है।

• दूसरा, यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें select चुनें पहले की तरह ही।
• तीसरा, सूत्र बॉक्स में निम्न सूत्र लिखें।

=COUNTIF($D$5:$D5,$D5)>1

• ठीक . पर क्लिक करने के बाद , यह पहली घटनाओं को छोड़कर सभी डुप्लिकेट को हाइलाइट करेगा।

जब आप किसी अन्य स्रोत से डेटा आयात करते हैं, तो ऐसा हो सकता है कि एक ही कॉलम में कई मान आयात किए गए हों। निम्न छवि इस प्रकार की आयात घटना का एक उदाहरण दिखाती है।

एक उपयोगी टेक्स्ट टू कॉलम . है सुविधा जिसका हम उपयोग कर सकते हैं जो हमें इन ग्रंथों को विभाजित करने में मदद करेगी।

चरण:

• सबसे पहले, उस श्रेणी का चयन करें जिससे आप टेक्स्ट को विभाजित करना चाहते हैं।
• फिर डेटा . पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, डेटा उपकरण . से समूह में, स्तंभों का पाठ select चुनें ।

• अगला, सीमांकित select चुनें अगले बॉक्स में।

• अगला . पर क्लिक करने के बाद , एक और बॉक्स दिखाई देगा।
• स्पेस चुनें यहां सीमांकक . के अंतर्गत विकल्प के रूप में पाठ रिक्त स्थान से विभाजित है।
• फिर अगला . पर क्लिक करें ।

• उसके बाद, समाप्त करें . पर क्लिक करें ।

• आखिरकार, टेक्स्ट विभाजित हो जाएगा।

• अब इसे और अधिक प्रस्तुत करने योग्य बनाने के लिए कुछ संशोधन करते हैं।

इस प्रकार हम एक्सेल में अन्य डेटा सफाई तकनीकों के साथ टेक्स्ट को अलग-अलग सेल में विभाजित कर सकते हैं।

कभी-कभी, आप चाहते हैं कि आपके टेक्स्ट का मामला वही हो। एक्सेल टेक्स्ट के केस को बदलने का कोई सीधा तरीका प्रदान नहीं करता है, लेकिन आप इसे कुछ फ़ार्मुलों का उपयोग करके आसानी से कर सकते हैं। टेक्स्ट बदलने के तीन सूत्र हैं:

ऊपरी :यह सूत्र टेक्स्ट को सभी अपरकेस में बदल देता है।

निचला :यह सूत्र टेक्स्ट को सभी लोअरकेस में बदल देता है।

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

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

अपर केस में बदलना

जैसा कि पहले उल्लेख किया गया है, हम UPPER फ़ंक्शन का उपयोग करेंगे इस काम के लिए। हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, सेल चुनें C5 ।
• फिर उसमें निम्न सूत्र लिखिए।

=UPPER(B5)

• उसके बाद, Enter press दबाएं ।

• अब फिर से सेल का चयन करें और फिल हैंडल आइकन पर क्लिक करें और बाकी सेल्स को फॉर्मूला से भरने के लिए खींचें।

लोअर केस में बदलना

इसके बाद, हम यह प्रदर्शित करेंगे कि सभी टेक्स्ट को लोअरकेस में कैसे बदला जाए।

इसके लिए हम निम्नलिखित चरणों का पालन करेंगे।

चरण:

• सबसे पहले, सेल चुनें D5 ।
• फिर उसमें निम्न सूत्र लिखिए।

=LOWER(B5)

• उसके बाद, Enter press दबाएं ।

• अब फिर से सेल का चयन करें और फिल हैंडल आइकन पर क्लिक करें और बाकी सेल्स को फॉर्मूला से भरने के लिए खींचें।

उचित मामले में बदलना

उचित मामला बड़े अक्षरों को उचित स्थानों पर और अन्य स्थानों में लोअरकेस को इंगित करता है। इस डेटासेट के लिए, हम इसके लिए निम्न विधि का उपयोग कर रहे हैं।

चरण:

• सबसे पहले, सेल चुनें E5 ।
• फिर उसमें निम्न सूत्र लिखिए।

=PROPER(B5)

• उसके बाद, Enter press दबाएं ।

• आखिरकार, फिर से सेल का चयन करें और बाकी सेल्स को भरने के लिए फिल हैंडल आइकन पर क्लिक करें और खींचें।

5. अतिरिक्त स्थान निकालें

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

हम अतिरिक्त रिक्त स्थान को हटाने के लिए एक्सेल में इन दो डेटा सफाई तकनीकों का पालन कर सकते हैं।

हम इन दोनों का उपयोग निम्नलिखित डेटासेट में करेंगे।

ट्रिम फ़ंक्शन का उपयोग करना

हम ट्रिम फ़ंक्शन का उपयोग करते हैं किसी पाठ से सभी प्रमुख और अनुगामी रिक्त स्थान को निकालने के लिए। ट्रिम फ़ंक्शन एकाधिक रिक्त स्थान को एक ही स्थान से भी बदल देता है।

चरण:

• सबसे पहले, अपडेट किया गया डेटा . नामक कॉलम जोड़ें परिणाम दिखाने के लिए।
• फिर सेल D5 . पर क्लिक करें ।
• टाइप करें =TRIM और सेल चुनें C5 पहले तर्क में। कोष्ठक बंद करें।
• तो, सूत्र बन जाता है:

=TRIM(C5)

• अब, Enter दबाएं ।

• अब फिल हैंडल आइकन को अंतिम सेल में खींचें।

हम देख सकते हैं कि अनावश्यक स्थान हटा दिए गए हैं; प्रत्येक शब्द के बाद केवल एक ही स्थान होता है।

ढूंढें और बदलें सुविधा का उपयोग करना:

इस खंड में, हम चर्चा करेंगे कि सभी रिक्त स्थान को हटाने के लिए ढूँढें और बदलें कमांड का उपयोग कैसे करें।

चरण:

• सबसे पहले, उस डेटा का चयन करें जहां से हम अतिरिक्त स्थान हटाना चाहते हैं।
• फिर होम  पर जाएं टैब।
• संपादन . से आदेश, ढूंढें और चुनें  . पर जाएं सुविधा।
• बदलें का चयन करें ड्रॉप-डाउन सूची से।

• जब हम बदलें select चुनते हैं , हमें एक डायलॉग बॉक्स मिलेगा।
• क्या खोजें  . में रिक्त स्थान लिखें फ़ील्ड.
• इससे बदलें रखें बॉक्स खाली.
• फिर, सभी को बदलें click क्लिक करें ।

• सभी बदलें clicking क्लिक करने के बाद , हमें प्रतिस्थापनों की संख्या दर्शाने वाला एक पॉप-अप मिलेगा।
• अब ठीक क्लिक करें पॉप-अप पर।
• फिर बंद करें . क्लिक करें डायलॉग बॉक्स का।
• आखिरकार, हमें परिणाम मिलता है।

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

=CLEAN(A2)

स्वच्छ फ़ंक्शन कभी-कभी कुछ गैर-मुद्रण यूनिकोड वर्णों को याद कर सकता है। इसे 7-बिट ASCII कोड में पहले 32 गैर-मुद्रण वर्णों को हटाने के लिए प्रोग्राम किया गया है। गैर-मुद्रण यूनिकोड वर्णों को निकालने के तरीके के बारे में जानकारी के लिए एक्सेल हेल्प सिस्टम ब्राउज़ करें।

डेटा सफाई तकनीकों में एक और महत्वपूर्ण बात यह है कि डेटासेट में एक मान को दूसरे में बदलना है। कभी-कभी आपको मानों को एक मीट्रिक सिस्टम से दूसरे मीट्रिक सिस्टम में बदलने की आवश्यकता हो सकती है। उदाहरण के लिए, आप एक ऐसी फ़ाइल आयात कर सकते हैं जिसका मान द्रव औंस (fl oz) में है, और उन्हें मिलीलीटर में व्यक्त करने की आवश्यकता है। एक्सेल का कन्वर्ट फ़ंक्शन इस प्रकार और कई अन्य प्रकार के रूपांतरण कर सकते हैं।

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

एक प्रदर्शन के लिए, आइए एक डेटासेट देखें और इसे उस पर लागू करें। यह डेटासेट है।

तापमान को फ़ारेनहाइट में बदलने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, आउटपुट सेल चुनें D5
• फिर निम्न सूत्र टाइप करें

=CONVERT(C5,"C","F")

• उसके बाद, Enter press दबाएं ।

• अब क्लिक करें और सभी मानों को बदलने के लिए भरण हैंडल आइकन को कॉलम के अंत तक खींचें।

इस प्रकार हम मूल्यों को अन्य इकाइयों में परिवर्तित कर सकते हैं। अन्य इकाई रूपांतरणों के लिए, यह लेख देखें ।

8. हाइलाइट त्रुटियां

उचित सूत्र या स्ट्रिंग या मानों की कमी के कारण, स्प्रेडशीट त्रुटियाँ दिखाएगी। यदि आप उन्हें एक-एक करके खोजना शुरू करेंगे तो इसमें काफी समय लग जाएगा। नीचे मैंने त्रुटियों को खोजने और उन्हें हाइलाइट करने की एक आसान तकनीक के बारे में बताया है ताकि आप उन्हें आसानी से ढूंढ सकें।

मान लीजिए कि हमारे पास कुछ कोशिकाओं में कुछ त्रुटियों वाला डेटासेट है। अब हम उन्हें सशर्त स्वरूपण के साथ हाइलाइट करेंगे और विशेष पर जाएं एक्सेल की विशेषता।

आइए निम्नलिखित स्प्रैडशीट पर एक नज़र डालें।

यहां त्रुटियों को उजागर करने के लिए इन चरणों का पालन करें।

चरण:

• सबसे बढ़कर, संपूर्ण डेटासेट चुनें और नया नियम . पर क्लिक करें सशर्त स्वरूपण  . से विकल्प।

• फिर, नियम प्रकार में केवल उन कक्षों को प्रारूपित करें जिनमें शामिल हैं . चुनें और फिर त्रुटियों . का चयन करें नीचे दी गई ड्रॉप-डाउन सूची से।

• फिर ठीक . पर क्लिक करें ।

यह डेटासेट का अंतिम परिणाम है। जैसा कि आप देख सकते हैं, एक्सेल ने त्रुटि मानों को हाइलाइट किया है।

इस प्रकार आप एक्सेल में डेटा सफाई तकनीकों में से एक के रूप में त्रुटियों को उजागर कर सकते हैं।

9. कॉलम में शामिल हों

कॉलम में शामिल होना उन तकनीकों में से एक है जो डेटा को साफ या पुनर्व्यवस्थित करते समय हमें कुछ समय की आवश्यकता हो सकती है। इस प्रदर्शन के लिए, आइए निम्नलिखित डेटासेट लें।

यह देखने के लिए इन चरणों का पालन करें कि हम एक्सेल में अलग-अलग सेल से इन टेक्स्ट को कैसे जोड़ सकते हैं।

चरण:

• सबसे पहले, सेल चुनें E5 ।
• फिर निम्न सूत्र लिखिए।

=B5&" "&D5&" "&C5

• उसके बाद, Enter press दबाएं ।

• अब क्लिक करें और फिल हैंडल आइकॉन को कॉलम के अंत तक खींचें और बाकी सेल्स के लिए फॉर्मूला दोहराने के लिए।

इस प्रकार हम एक्सेल में दो या दो से अधिक कॉलमों को जोड़ सकते हैं।

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

यहां एक आसान तरीका है जिसका उपयोग करके आप कॉलम को आसानी से पुनर्व्यवस्थित कर सकते हैं जो हम निम्नलिखित डेटासेट पर कर रहे हैं।

चरण:

• सबसे पहले, कॉलम हेडर पर क्लिक करके उस कॉलम को चुनें जिसे आप स्थानांतरित करना चाहते हैं।
• फिर या तो संदर्भ मेनू का उपयोग करके या Ctrl+X . दबाकर कॉलम को काटें . आप इस बिंदु पर चयन की सीमा पर बिंदीदार रेखा देखेंगे।

• उसके बाद, उस कॉलम हेडर पर राइट-क्लिक करें जिसके पहले आप पिछला कॉलम रखना चाहते हैं।
• अब कट सेल डालें select चुनें संदर्भ मेनू से।

• यह पिछले कॉलम को यहां सम्मिलित करेगा।

इस प्रकार हम Excel में डेटा सफाई तकनीकों के भाग के रूप में स्तंभों को पुनर्व्यवस्थित कर सकते हैं।

11. पंक्तियों को यादृच्छिक करें

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

हम निम्नलिखित डेटासेट के लिए ऐसा कार्य करने जा रहे हैं।

पंक्तियों को यादृच्छिक बनाने और एक्सेल में क्रम बदलने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, हमें B column कॉलम के बाद एक नया कॉलम बनाना होगा . इसके लिए C column कॉलम के हेडर लेटर पर क्लिक करें , और संपूर्ण C कॉलम का चयन किया जाएगा।
• फिर, राइट-क्लिक करें और इन्सर्ट कमांड चुनें, और एक नया कॉलम बन जाएगा।

• अब, नए बनाए गए कॉलम (यानी रैंडम नंबर) को एक नाम दें। पहले सेल (यानी C5) कॉलम को चुनें और सेल में निम्न फॉर्मूला टाइप करें।

=RAND()

• उसके बाद, ENTER दबाएं , और एक यादृच्छिक संख्या C5 . कक्ष में प्रदर्शित होगी . संख्या 1 से कम है।

• अगला, फिर से सेल का चयन करें और बाकी सेल के लिए फॉर्मूला दोहराने के लिए कॉलम के अंत में फिल हैंडल आइकन पर क्लिक करें और खींचें।

• जबकि सेल C5 चयनित है होम  . पर जाएं टैब।
• फिर संपादन . पर जाएं समूह बनाएं और क्रमबद्ध करें और फ़िल्टर करें . क्लिक करें ।
• अब सबसे छोटे से सबसे बड़े को क्रमबद्ध करें . चुनें ड्रॉप-डाउन मेनू से।

• जैसे ही आप कमांड पर क्लिक करेंगे, आपकी पंक्तियाँ अपने आप यादृच्छिक हो जाएँगी। यह ध्यान देने योग्य है कि न केवल कॉलम C , लेकिन पंक्तियों के साथ सभी कॉलम भी बेतरतीब ढंग से बदल दिए गए हैं।

तो ये वे चरण हैं जिनका पालन आप तब कर सकते हैं जब आपके पास एकाधिक कॉलम वाला डेटासेट हो और आप अपनी पंक्तियों को इस तरह से यादृच्छिक बनाना चाहते हैं कि एक पंक्ति के सभी कॉलम उनके मानों को यादृच्छिक बनाते हैं।

कभी-कभी आपके पास URL की सूची हो सकती है और आपको केवल फ़ाइल नाम निकालने की आवश्यकता होती है। निम्न तरीके से, आप URL से फ़ाइल का नाम निकाल सकते हैं। सेल मान लें B5 इसमें यह यूआरएल है:https://example.com/assets/images/horse.jpg ।

हमें यहां कार्यों की एक पूरी बहुतायत से युक्त एक सूत्र की आवश्यकता होगी। चिंता न करें, यह कैसे काम करता है यह समझने के लिए बाद में एक स्पष्टीकरण जोड़ा गया है। इस सूत्र में दाएं शामिल है , एलईएन , FIND and SUBSTITUTE functions in it.

Now follow these steps to extract the file name (horse.jpg) from the link.

Steps:

• First, select the cell where you want to put the file name. In this case, it is cell C5 ।
• Then write down the following formula in it.

=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))))

• After that, press Enter ।

🔎 Breakdown of the Formula

👉 SUBSTITUTE(B5,”/”,””) removes all the “/” from the URL. It returns http:www.example.comassetsimageshorse.jpg.

👉 LEN(SUBSTITUTE(B5,”/”,””)) returns the length of the previous string.

👉 LEN(B5) returns the length of the string in cell B4 which is 46 here.

👉 LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))) indicates the difference between the two length values. This in turn indicates the number of “/” were removed.

👉 SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))) substitutes all the slashes (/) is cell B5 with star sign (*) with the instance number from the result of the previous function.

👉 FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””)))) finds the position “*” is in the string up until now.

👉 LEN(B5)-FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””)))) is the difference between the original string length and the previous position.

👉 Finally, RIGHT(B5,LEN(B5)-FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))))) extracts that number of characters from the right side of the cell.

13. Match Text in List

Let’s take a look at the following figure. We are going to find out the persons who have resigned from the left side of our example. There is a list on the right side of the resigned numbers.

The above one shows a simple example. The data is in the range B5:D133 . The goal is to identify the rows in the data zone which are appearing in the Resigned Members list, in column G . You can delete these unnecessary rows later. We are going to need a combination of the IF and COUNTIF functions for the purpose.

Follow these steps to see how we can achieve the resigned status in our dataset.

Steps:

• First, select cell E5 ।
• Then write down the following formula.

=IF(COUNTIF($G$5:$G$25,C5),"Resigned","")

• After that, press Enter ।

• Now double-click the fill handle icon on the bottom-right corner of the cell border. You can also click and drag. But as the dataset is large, we are opting for the previous method.
• Once you have done that, the spreadsheet will look like this.

This is how we can match text as part of the data analysis techniques in Excel.

Transposing or changing vertical to horizontal and vice versa in a dataset is another common one in data analysis. So we will be discussing changing vertical data to horizontal now in the series of data cleaning techniques in Excel.

For that, we will be using the following dataset.

Using Paste Special Option

The easiest way to change a vertical column to a horizontal row is to use the Paste Special option of Excel. It also keeps the exact formatting while changing the vertical column. So, you don’t need to apply any formatting later. Let’s follow the steps below to see how we can use the Paste Special option to transpose the columns.

• First of all, select the range that you want to change to horizontal rows. Here, we have selected the range B4:C10 ।
• Secondly, press Ctrl+C to copy the range.
• Thirdly, select a cell where you want to paste the range horizontally. In our case, we have selected Cell B12 ।

This will change vertical columns into horizontal rows in the Excel spreadsheet.

Using TRANSPOSE Function:

We can use some Excel Functions to convert a vertical column to a horizontal row. Here, we will use the TRANSPOSE function for that purpose. The main advantage of using functions is that you will get dynamic updates in the horizontal rows if you change anything in the main dataset. But, the horizontal rows will not have the same formatting as the vertical columns. You need to add formatting after applying the method.

Let’s follow the steps below to learn more.

Steps:

• First, select the cell where you want to place the formula.
• Then write down the following formula in it.

=TRANSPOSE(B4:C10)

• Now press Enter ।

This is how we can change vertical data to horizontal using the TRANSPOSE function.

One of the most important data cleaning techniques in any form of data analysis is filling blank values, either with average in numeric cases or with values with the previous or later rows.

Sometimes we keep some blank cells to understand that they will be the first filled ones, have a look at our dataset to understand it. But sometimes it creates problems like while we are filtering or sorting data. So it’s better to fill them and there are easy ways to o it.

This is our dataset.

We can fill them up using these two simple methods.

Using Fill Command

If your data is small, you can enter the missing cell values from above manually or by using this command. Follow these steps to see how you can do that.

• Select Cell B6 ।
• Then go to the Home
• From the Editing group, click on Fill and then Down ।

• The dataset will look like this now.

• Now repeat the process for all the blank cells and fill out all the cells.

This is one way to fill up blank cells in a dataset.

Using Keyboard Shortcut:

We can achieve the same result using a keyboard shortcut. The shortcut is Ctrl+D . You just have to select the blank cell and press the shortcut. It will fill the cell with the value from the previous cell in the same column. Follow these steps for a more detailed guide.

• First, select cell B6 ।
• Then press Ctrl+D on your keyboard.

• Now selecting cell B9 and pressing Ctrl+D will yield the following result.

• Now fill out all the blank values by selecting them and pressing the shortcut.

16. Check Spelling

When you use Microsoft Word Document or another word processor, you use its spell checker feature most of the time. Spelling mistakes in a text document are embarrassing. In Excel, spelling mistakes can also occur serious problems. Say, for example, if you tabulate data by month, a misspelled month name will make that a year has 13 months.

Here we have made a dataset for the demonstration.

We are going to correct the range C5:C7 . Follow these steps to see how we can do that.

Steps:

• First, select the range C5:C7 ।
• Then go to the Review tab and select Spelling from the Proofing You can also skip this step and press F7 on your keyboard.

• Now select the correct suggestion and click on Change to change the value of a particular cell. The errors come in the selection order here.

• This will change the first error in the dataset.

• Now repeat this process for all the errors and you will have correct spellings this way.

17. Replace or Remove Text

Removing and replacing data, especially outliers is another important task in any data cleaning process. So as a part of our data cleaning techniques compilation, we are going to demonstrate replacing and removing text in Excel. We can do that using three methods.

For all of the methods, we are using the following dataset.

Using Find &Replace Feature:

Excel has a direct feature that can help find, replace and remove certain values from another. Follow these steps to see how we can use this feature to remove or replace in Excel.

• First, select the range which you want to modify. In this case, it is C5:C13 ।
• Then go to the Home
• After that, select Find &Replace from the Editing group section.
• Next, select Replace from the drop-down.

• Now, go to the Replace tab in the pop-up box.
• Then insert the value you want to replace in the Find what field and the value you want to replace it with in the Replace with

• Finally, click Replace All to replace all of them in the dataset.

This will be the final product after removing all of the hyphens from the dataset.

Utilizing Flash Fill:

Another feature we can use to replace or remove values while using data cleaning techniques is to utilize the flash fill feature. For the same dataset, follow these steps to see how that works.

• First, write down the final product after removing or replacing the desired value in the first cell. We are replacing the second hyphen in the string with a colon.

• Now start writing the second one manually. As soon as you start writing Excel will recognize the pattern and start suggesting output for the rest.

Note

If the suggestion doesn’t occur in the second entry, try it out manually. It sometimes suggests the output at the third or fourth entry if the pattern isn’t that obvious to Excel.

• Now press Enter ।

Thus, you can replace or remove values using this handy feature of Excel while data cleaning.

Using SUBSTITUTE Function:

Another way we can replace a value, or remove them in Excel is to use the SUBSTITUTE function . This function takes three values- a string, the value to replace, and the value to replace it with. Follow these steps to see how we can use this function.

• First, select cell C5 ।
• Then write down the following formula in it.

=SUBSTITUTE(B5,"-",":",2)

• After that, press Enter ।

• Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

18. Add Text to Cells

Adding text is not only common as one of the data cleaning techniques but also in general Excel operations. We can add text to cells in various ways. For demonstrations of those, we will use the following dataset.

Now you can use one of the following methods to add text to cells.

Using Ampersand (&) Symbol:

The ampersand (&) symbol in a formula works as the string connector. It connects the previous and the following string it is in between. Follow these steps to see how we can easily add text to cells using this.

• First of all, select cell C5 ।
• Secondly, put down the formula below.

=B5&".mp3"

• Next, press Enter ।

• Finally, click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

Applying Flash Fill Feature:

Follow these steps to add text using the flash fill feature easily in Excel.

• First, write down the first intended value manually in cell C5 ।

• Then start filling out the second one in the dataset. Once you start filling, Excel will suggest the pattern.

• As soon as the suggestion appears, press Enter ।

This is another way to add text in Excel.

Using CONCATENATE Function:

The CONCATENATE function in Excel, as the name suggests, concatenates two or more strings together. It takes those strings as its arguments. It works the same way as the ampersand sign method described earlier. Follow these steps to see how we can use this function.

• First, select cell C5 ।
• Then write down the following formula in it.

=CONCATENATE(B5,".mp3")

• After that, press Enter ।

• Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

19. Fix Trailing Minus Sign

Sometimes while taking data from other sources, the negative values do have a trailing minus sign. Take a look at the following figure.

Excel doesn’t consider the values with trailing negative signs as numbers. Instead, it treats them as texts. This may cause problems, not only in the data cleaning techniques but also in general operations like using formulas in Excel. So we need to address the problem and convert them into negative numbers.

Follow these simple steps to do that.

Steps:

• First, select the range you want to convert. We have copied it to compare it with the previous one.

• Then go to the Data tab on your ribbon.
• Then select Text to Columns from the Data Tools group section.

• Finally, directly click on Finish in the wizard.

This will automatically change all the negative values with trailing minus signs to Excel’s negative sign format.

This procedure works because there is a default setting in the Advanced Text Import Settings डायलॉग बॉक्स।

निष्कर्ष

So these were some of the most useful data cleaning techniques in Excel used for data analysis. Hopefully, you can use these techniques while using data cleaning in Excel with ease. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides like this, visit Exceldemy.com ।