एक्सेल में आम डेटा सफाई फॉर्मूला

एक्सेल फॉर्मूला

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

गैर-संख्यात्मक वर्ण निकालें

सिस्टम को अक्सर देश कोड के साथ एक विशिष्ट, 11-अंकीय सूत्र में फ़ोन नंबर डालने की आवश्यकता होती है और कोई विराम चिह्न नहीं होता है। हालांकि, लोग अक्सर इसके बजाय डैश और पीरियड के साथ इस डेटा को दर्ज करते हैं। यहाँ के लिए एक बढ़िया सूत्र है सभी गैर-संख्यात्मक वर्णों को हटाना एक्सेल में। सूत्र सेल A2 में डेटा की समीक्षा करता है:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

अब आप परिणामी कॉलम को कॉपी कर सकते हैं और उपयोग कर सकते हैं संपादित करें> चिपकाएँ मान ठीक से स्वरूपित परिणाम के साथ डेटा पर लिखने के लिए।

एक के साथ एकाधिक फ़ील्ड का मूल्यांकन करें

हम अक्सर आयात से अधूरे रिकॉर्ड को हटा देते हैं। उपयोगकर्ताओं को यह एहसास नहीं होता है कि आपको हमेशा जटिल पदानुक्रमित सूत्र लिखने की आवश्यकता नहीं होती है और आप इसके बजाय एक OR कथन लिख सकते हैं। नीचे दिए गए इस उदाहरण में, मैं लापता डेटा के लिए A2, B2, C2, D2, या E2 की जांच करना चाहता हूं। यदि कोई डेटा गुम है, तो मैं 0 वापस करने जा रहा हूं, अन्यथा 1। यह मुझे डेटा को क्रमबद्ध करने और अपूर्ण रिकॉर्ड्स को हटाने की अनुमति देगा।

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

ट्रिम और कॉनसैटनेट फील्ड्स

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

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

मान्य ईमेल पते की जाँच करें

एक बहुत ही सरल सूत्र, जो @ और दोनों के लिए दिखता है। एक ईमेल पते में:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

पहले और आखिरी नाम निकालें

कभी-कभी, समस्या इसके विपरीत होती है। आपके डेटा में एक पूरा नाम फ़ील्ड है, लेकिन आपको पहले और अंतिम नामों को पार्स करना होगा। ये सूत्र पहले और अंतिम नाम के बीच के स्थान की तलाश करते हैं और जहां आवश्यक हो वहां टेक्स्ट लेते हैं। यदि कोई अंतिम नाम नहीं है या A2 में कोई रिक्त प्रविष्टि है तो IT भी संभालता है।

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

और अंतिम नाम:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

वर्णों की संख्या को सीमित करें और जोड़ें ...

क्या आप कभी अपने मेटा विवरण को साफ करना चाहते हैं? यदि आप सामग्री को एक्सेल में खींचना चाहते हैं और फिर मेटा विवरण फ़ील्ड (150 से 160 वर्ण) में उपयोग के लिए सामग्री ट्रिम कर सकते हैं, तो आप इस सूत्र का उपयोग करके ऐसा कर सकते हैं मेरी जगह। यह एक स्थान पर विवरण को सफाई से तोड़ता है और फिर…

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

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

तुम्हें क्या लगता है?

यह साइट स्पैम को कम करने के लिए अकिस्मेट का उपयोग करती है। जानें कि आपका डेटा कैसे संसाधित किया जाता है.