एक्सेल मधील सामान्य डेटा क्लीनअप फॉर्म्युले

एक्सेल सूत्रे

कित्येक वर्षांपासून मी प्रकाशने स्त्रोत म्हणून वापरली आहे की केवळ गोष्टी कशा करायच्या हे वर्णन करण्यासाठीच नाही तर स्वत: साठी नंतर शोधण्यासाठी रेकॉर्ड ठेवण्यासाठी देखील! आज, आमच्याकडे एक क्लायंट होता ज्याने आम्हाला ग्राहक डेटा फाईल दिली होती जी आपत्ती होती. अक्षरशः प्रत्येक क्षेत्रात चुकीचे स्वरूप होते आणि; परिणामी, आम्ही डेटा आयात करण्यात अक्षम होतो. एक्सेलसाठी व्हिज्युअल बेसिकचा वापर करून क्लीनअप करण्यासाठी काही उत्कृष्ट अ‍ॅड्स आहेत, आम्ही मॅकसाठी ऑफिस चालवितो जे मॅक्रोला समर्थन देणार नाही. त्याऐवजी आम्ही मदत करण्यासाठी सरळ सूत्रे शोधतो. मला वाटले की मी येथे असलेल्यांपैकी काही सामायिक कराल जेणेकरुन इतर त्यांचा वापर करु शकतील.

संख्यात्मक नसलेली अक्षरे काढा

सिस्टीममध्ये सहसा देश कोडसह विशिष्ट, 11-अंकी फॉर्म्युलामध्ये फोन नंबर समाविष्ट करणे आवश्यक असते आणि विरामचिन्हे नसतात. तथापि, लोक बर्‍याचदा त्याऐवजी डॅश आणि पूर्णविरामांसह हा डेटा प्रविष्ट करतात. यासाठी एक उत्तम सूत्र आहे सर्व संख्यात्मक वर्ण काढून टाकत आहे एक्सेल मध्ये सूत्र सेल 2 XNUMX मधील डेटाचे पुनरावलोकन करते:

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

आता आपण परिणामी स्तंभ कॉपी आणि वापरू शकता संपादित करा> मूल्ये पेस्ट करा योग्यरित्या स्वरूपित निकालासह डेटावर लिहिणे.

एक किंवा अनेक क्षेत्राचे मूल्यांकन करा

आम्ही बर्‍याचदा आयातातून अपूर्ण रेकॉर्ड साफ करतो. वापरकर्त्यांना नेहमीच हे समजत नाही की आपल्याला नेहमीच जटिल श्रेणीबद्ध सूत्र लिहावे लागत नाही आणि त्याऐवजी आपण ओआर विधान लिहू शकता. खाली दिलेल्या उदाहरणात, डेटा गहाळ झाल्याबद्दल मला ए 2, बी 2, सी 2, डी 2 किंवा ई 2 तपासायचे आहे. कोणताही डेटा गहाळ असल्यास, मी 0 परत करणार आहे, अन्यथा 1. यामुळे मला डेटा क्रमवारी लावण्याची आणि अपूर्ण रेकॉर्ड हटविण्याची परवानगी मिळेल.

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

ट्रिम आणि कंकॅटेनेट फील्ड

आपल्या डेटामध्ये प्रथम आणि आडनाव फील्ड असल्यास, परंतु आपल्या आयातीला पूर्ण नाव फील्ड असल्यास आपण एक्सेल फंक्शन कॉन्टेनेटमध्ये अंगभूत वापरून सुबकपणे फील्ड एकत्रित करू शकता, परंतु आधी किंवा नंतर रिक्त जागा काढण्यासाठी ट्राईम वापरण्याची खात्री करा मजकूर एका फील्डमध्ये डेटा नसल्यास आम्ही संपूर्ण क्षेत्र ट्रिमने लपेटतो:

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

वैध ईमेल पत्त्यासाठी तपासा

एक अतिशय सोपी सूत्र आहे जो @ आणि. ईमेल पत्त्यावर:

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

प्रथम आणि शेवटची नावे काढा

कधीकधी, समस्या उलट आहे. आपल्या डेटामध्ये पूर्ण नाव फील्ड आहे परंतु आपल्याला पहिली आणि शेवटची नावे विश्लेषित करण्याची आवश्यकता आहे. ही सूत्रे नाव आणि आडनाव दरम्यानची जागा शोधतात आणि आवश्यक असल्यास मजकूर हस्तगत करतात. आडनाव नसल्यास किंवा ए 2 मध्ये रिक्त प्रविष्टी नसल्यास आयटी देखील हाताळते.

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

नक्कीच, हे सर्वसमावेशक नसलेले आहेत… जंप स्टार्ट करण्यात मदत करण्यासाठी फक्त काही द्रुत सूत्रे! आपण कोणती इतर सूत्रे वापरत आहात? त्यांना टिप्पण्यांमध्ये जोडा आणि मी हा लेख अद्यतनित केल्यावर मी तुम्हाला क्रेडिट देईन.

तुला काय वाटत?

ही साइट स्पॅम कमी करण्यासाठी अकिस्मेट वापरते आपल्या टिप्पणी डेटावर प्रक्रिया कशी केली जाते ते जाणून घ्या.