साधारण डाटा सफाई को लागी एक्सेल सूत्र

एक्सेल डाटा क्लीनअप सूत्रहरू

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

गैर-संख्यात्मक वर्णहरू हटाउनुहोस्

प्रणालीहरूले प्राय: फोन नम्बरहरू देशको कोडको साथ एक ११-अंकको सूत्रमा सम्मिलित गर्न आवश्यक गर्दछ र विराम चिह्नहरू। जहाँसम्म, मान्छेहरू प्राय: यसको सट्टामा ड्यासहरू र अवधिहरूको साथ यो डाटा प्रविष्ट गर्छन्। यहाँ को लागी एक महान सूत्र छ सबै गैर-संख्यात्मक क्यारेक्टरहरू हटाउँदै एक्सेलमा सूत्र सेल 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 जाँच गर्न चाहान्छु। यदि कुनै डाटा हराइरहेको छ भने, म ० फिर्ताको लागि जाँदैछु, अन्यथा १। यसले मलाई डाटा क्रमबद्ध गर्न र अपूर्ण रिकर्डहरू मेटाउन अनुमति दिनेछ।

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

ट्रिम र कन्सटेनेट क्षेत्रहरू

यदि तपाईंको डाटाको पहिलो र अन्तिम नाम फिल्डहरू छन्, तर तपाईंको आयातको पूरा नाम फिल्ड छ, तपाईं एक्ज्यामेन्ट कन्टेन्टमा निर्मित प्रयोग गरेर फाँटहरू सँगै सँगै गर्न सक्नुहुनेछ, तर पहिले वा पछाडि कुनै पनि खाली ठाउँहरू हटाउन ट्रिम प्रयोग गर्न नबिर्सनुहोस्। पाठ हामी घटनालाई TRIM को साथ पूरै क्षेत्र लपेट्छौं जहाँ एक फिल्डमा डाटा छैन:

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

वैध ईमेल ठेगानाको लागि जाँच गर्नुहोस्

एक सुन्दर सरल सूत्र जुन दुबैलाई @ र। ईमेल ठेगानामा:

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

पहिलो र अन्तिम नामहरू निकाल्नुहोस्

कहिलेकाँही समस्या यसको विपरित हुन्छ। तपाईंको डाटाको पूरा नाम फिल्ड छ तर तपाईं पहिलो र अन्तिम नाम पार्स गर्न आवश्यक छ। यी सूत्रहरूले पहिलो र अन्तिम नामको बीचको खाली ठाउँ खोज्दछन् र आवश्यक पर्ने पाठ लिनुहोस्। आईटी ले पनि ह्यान्डल गर्छ यदि कुनै अन्तिम नाम छैन वा A2 मा खाली प्रविष्टि छ।

=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० देखि १ characters० वर्णहरू) मा प्रयोगको लागि सामग्री ट्रिम गर्न चाहानुहुन्छ भने, तपाईं यो सूत्र प्रयोग गरेर त्यो गर्न सक्नुहुनेछ मेरो स्पट। यसले सफासँग वर्णन खाली ठाउँमा तोड्दछ र त्यसपछि थप गर्दछ ...:

=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)

अवश्य पनि, यी व्यापक हुनुको मतलब होईन… जम्प सुरू गर्न मद्दत गर्न केहि द्रुत सूत्रहरू! अरू कुन सुत्रहरू तपाईले आफुलाई प्रयोग गरिरहेको पाउनुभयो? तिनीहरूलाई टिप्पणीमा थप्नुहोस् र म तपाईंलाई यो लेख अपडेट गर्ने बित्तिकै क्रेडिट दिनेछु।

तिम्रो के बिचार छ?

यो साइट स्प्याम कम गर्न Akismet को उपयोग गर्दछ। जान्नुहोस् कि तपाईंको डेटा कसरी संसाधित छ.