ايڪسل ۾ باقاعده اظهار (RegExp) سان متن پارس ڪريو

Excel ۾ ٽيڪسٽ سان ڪم ڪرڻ وقت سڀ کان وڌيڪ وقت سازي ۽ مايوس ڪندڙ ڪمن مان هڪ آهي تجزيو ڪرڻ - الفانومريڪ ”دليءَ“ کي حصن ۾ پارس ڪرڻ ۽ ان مان گهربل ٽڪرا ڪڍڻ. مثال طور:

• ايڊريس مان زپ ڪوڊ ڪڍڻ (اهو سٺو آهي ته زپ ڪوڊ هميشه شروع ۾ آهي، پر ڇا اهو نه آهي؟)
• بينڪ جي بيان ۾ ادائيگي جي وضاحت مان انوائس جو نمبر ۽ تاريخ ڳولڻ
• مخالف ڌر جي فهرست ۾ ڪمپنين جي مختلف وضاحتن مان TIN ڪڍڻ
• تفصيل ۾ ڪار نمبر يا آرٽيڪل نمبر ڳولھيو، وغيره.
  

عام طور تي اهڙين حالتن ۾، اڌ ڪلاڪ جي خوفناڪ چونڊ کان پوءِ دستي طور تي، ذهن ۾ خيال اچڻ شروع ٿين ٿا ته ڪنهن نه ڪنهن طريقي سان هن عمل کي خودڪار ڪيو وڃي (خاص طور تي جيڪڏهن ڊيٽا تمام گهڻي هجي). اتي ڪيترائي حل آھن ۽ مختلف درجي جي پيچيدگي جي ڪارڪردگي سان:

• استعمال بلٽ ان ايڪسل ٽيڪسٽ افعال ڳولها-ڪٽ-گلو متن: LEVSIMV (کاٻو), ساڄو (ساڄو), پي ايس ٽي آر (وچ), STsEPIT (CONCATENATE) ۽ ان جي analogues, گڏ ڪرڻ (JOINTEXT), ٺيڪ (صحيح) وغيره. اهو طريقو سٺو آهي جيڪڏهن متن ۾ واضح منطق آهي (مثال طور، انڊيڪس هميشه پتي جي شروعات ۾ آهي). ٻي صورت ۾، فارمولا تمام گهڻو پيچيده ٿي ويندا آهن ۽، ڪڏهن ڪڏهن، اهو پڻ اچي ٿو صف فارمولن تي، جيڪو وڏي جدولن تي تمام گهڻو سست ٿئي ٿو.
• استعمال متن جي هڪجهڙائي آپريٽر وانگر Visual Basic کان ڪسٽم ميڪرو فنڪشن ۾ لپي ويو. هي توهان کي وائلڊ ڪارڊ اکر استعمال ڪندي وڌيڪ لچڪدار ڳولها لاڳو ڪرڻ جي اجازت ڏئي ٿو (*, #,?, etc.) بدقسمتي سان، هي اوزار متن مان گهربل ذيلي اسٽرينگ نه ڪڍي سگهي ٿو - صرف چيڪ ڪريو ته اهو ان ۾ موجود آهي.

مٿين کان علاوه، هڪ ٻيو طريقو آهي جيڪو تمام گهڻو مشهور آهي پروفيشنل پروگرامرز، ويب ڊولپرز ۽ ٻين ٽيڪنالاجي جي تنگ حلقن ۾ - هي آهي باقاعده اشارو (باقاعده اظهار = RegExp = "regexps" = "باقاعده"). آسان لفظ ۾، RegExp هڪ ٻولي آهي جتي خاص اکر ۽ ضابطا استعمال ڪيا ويندا آهن متن ۾ ضروري سبسٽرنگ ڳولڻ لاءِ، انهن کي ڪڍڻ يا انهن کي ٻئي متن سان مٽائڻ.. باقاعده اظهار هڪ تمام طاقتور ۽ خوبصورت اوزار آهي جيڪو متن سان ڪم ڪرڻ جي ٻين سڀني طريقن کي وڌائي ٿو. ڪيتريون ئي پروگرامنگ ٻوليون (C#, PHP, Perl, JavaScript…) ۽ ٽيڪسٽ ايڊيٽر (Word, Notepad++…) باقاعده اظهار جي حمايت ڪن ٿيون.

بدقسمتي سان Microsoft Excel وٽ RegExp سپورٽ نه آهي باڪس مان، پر اهو آساني سان VBA سان مقرر ٿي سگهي ٿو. ٽيب مان Visual Basic Editor کوليو ڊولپر (ڊولپر) يا ڪي بورڊ شارٽ ڪٽ ھن+F11. پوء مينيو ذريعي نئون ماڊل داخل ڪريو داخل ڪريو - ماڊل ۽ ھيٺ ڏنل ميڪرو فنڪشن جي متن کي نقل ڪريو.

پبلڪ فنڪشن RegExpExtract(ٽيڪسٽ اسٽرنگ جي طور تي، پيٽرن جي طور تي اسٽرنگ، اختياري شيءِ انٽيجر جي طور تي = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = پيٽرن regex.Global = IfestTrue. (ٽيڪسٽ) پوءِ سيٽ ڪريو matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

اسان ھاڻي Visual Basic Editor کي بند ڪري سگھون ٿا ۽ پنھنجي نئين خصوصيت کي آزمائڻ لاءِ Excel ڏانھن موٽي سگھون ٿا. ان جي نحو هن ريت آهي:

= RegExpExtract (txt ؛ نمونو ؛ شيون )

جتي

• txt - هڪ سيل جنهن ۾ ٽيڪسٽ آهي جنهن کي اسين چيڪ ڪري رهيا آهيون ۽ جنهن مان اسان کي گهربل سبسٽرنگ ڪڍڻ چاهيون ٿا
• نموني - ماسڪ (نمونہ) substring ڳولا لاء
• جي غيرموجودگي سبب - ڪڍڻ لاءِ ذيلي اسٽرنگ جو تسلسل نمبر، جيڪڏھن انھن مان گھڻا آھن (جيڪڏھن بيان نه ڪيو ويو آھي، ته پھريون واقعو ڏيکاريو ويندو)

هتي سڀ کان وڌيڪ دلچسپ شيء، يقينا، نمونو آهي - RegExp جي "ٻولي ۾" خاص ڪردارن جو هڪ ٽيمپليٽ اسٽرنگ، جيڪو بيان ڪري ٿو ته اسان ڇا ۽ ڪٿي ڳولڻ چاهيون ٿا. هتي توهان کي شروع ڪرڻ لاء سڀ کان وڌيڪ بنيادي آهن:

جيڪڏهن اسان اکرن جي هڪ خاص تعداد کي ڳولي رهيا آهيون، مثال طور، ڇهه عدد پوسٽل ڪوڊ يا سڀ ٽي-اکر پراڊڪٽ ڪوڊ، ته پوءِ اسان بچاءُ لاءِ اچون ٿا. ماپيندڙ or ماپيندڙ خاص اظهار آهن جيڪي ڳولهڻ لاءِ اکرن جو تعداد بيان ڪن ٿا. مقدار کي ان ڪردار تي لاڳو ڪيو ويو آهي جيڪو ان کان اڳ اچي ٿو:

ھاڻي اچون ٿا سڀ کان دلچسپ حصي ڏانھن - ٺاھيل فنڪشن جي ايپليڪيشن جو تجزيو ۽ زندگي جي عملي مثالن جي نمونن بابت اسان ڇا سکيو.

متن مان انگ ڪڍڻ

شروع ڪرڻ سان، اچو ته هڪ سادي ڪيس جو تجزيو ڪريون - توهان کي الفانومريڪ پورج مان پهريون نمبر ڪڍڻو پوندو، مثال طور، قيمت جي فهرست مان بي ترتيب بجلي جي فراهمي جي طاقت:

باقاعده اظهار جي پويان منطق سادو آهي: d مطلب ته ڪو به انگ، ۽ مقدار جو + چون ٿا ته انهن جو تعداد هڪ يا وڌيڪ هجڻ گهرجي. فنڪشن جي سامهون ڊبل مائنس جي ضرورت آهي ”پر اڏامڻ“ لاءِ ڪڍيل اکرن کي نمبر-اي-ٽيڪسٽ مان مڪمل نمبر ۾ تبديل ڪرڻ.

پوسٽ ڪوڊ

پهرين نظر ۾، هتي هر شيء سادو آهي - اسان هڪ قطار ۾ ڇهن انگن اکرن کي ڳولي رهيا آهيون. اسان هڪ خاص ڪردار استعمال ڪندا آهيون d عدد ۽ مقدار لاءِ 6 {} اکرن جي تعداد لاء:

بهرحال، هڪ صورتحال ممڪن آهي جڏهن، قطار ۾ انڊيڪس جي کاٻي پاسي، قطار ۾ نمبرن جو هڪ ٻيو وڏو سيٽ آهي (فون نمبر، TIN، بينڪ اڪائونٽ، وغيره) پوء اسان جي باقاعده موسم پهرين 6 کي ڪڍي ڇڏيندو. ان مان انگ اکر، يعني صحيح ڪم نه ڪندو:

ائين ٿيڻ کان روڪڻ لاءِ، اسان کي اسان جي باقاعده اظهار جي ڪنارن جي چوڌاري هڪ ترميمي شامل ڪرڻ جي ضرورت آهي b لفظ جي پڇاڙيءَ جي معنيٰ. اهو ايڪسل تي واضح ڪندو ته ٽڪرا (انڊيڪس) اسان کي هڪ الڳ لفظ هجڻ گهرجي، ۽ ڪنهن ٻئي ٽڪرا (فون نمبر) جو حصو نه هجڻ گهرجي:

فون

ٽيڪسٽ ۾ فون نمبر ڳولڻ ۾ مسئلو اهو آهي ته نمبر لکڻ لاءِ تمام گھڻا آپشن آهن - هائيفن سان ۽ بغير، اسپيس ذريعي، خطي جي ڪوڊ سان يا بغير بريڪٽس ۾ وغيره. ان ڪري، منهنجي خيال ۾، اهو آسان آهي. پهريان انهن سڀني اکرن کي ماخذ متن مان صاف ڪريو ڪيترن ئي nested افعال استعمال ڪندي سبسٽيٽيوٽ (متبادل)انهي ڪري ته اهو هڪ واحد مجموعي ۾ گڏ ٿئي ٿو، ۽ پوء هڪ ابتدائي باقاعده سان ڊي{11} قطار ۾ 11 انگ ڪڍو:

آئي ٽي اين

اهو هتي ٿورو وڌيڪ پيچيده آهي، ڇاڪاڻ ته TIN (اسان جي ملڪ ۾) 10-عددن (قانوني ادارن لاءِ) يا 12-عددن (ماڻهن لاءِ) ٿي سگهي ٿو. جيڪڏهن توهان خاص طور تي غلطي نه ڳوليندا آهيو، پوء اهو ممڪن آهي ته باقاعده سان مطمئن ٿي وڃي ڊي{10,12}، پر، سختي سان ڳالهائڻ، اهو 10 کان 12 اکرن تائين سڀني نمبرن کي ڪڍي ڇڏيندو، يعني ۽ غلطي سان 11 انگن ۾ داخل ڪيو ويو. منطقي يا آپريٽر سان ڳنڍيل ٻن نمونن کي استعمال ڪرڻ وڌيڪ صحيح ٿيندو | (عمودي بار):

مهرباني ڪري نوٽ ڪريو ته سوال ۾ اسان پهريون ڀيرو 12-bit نمبر ڳوليندا آهيون، ۽ صرف پوءِ 10-bit نمبرن لاءِ. جيڪڏهن اسان پنهنجو باقاعده اظهار ٻئي طريقي سان لکون ٿا، ته اهو هر ڪنهن لاءِ ڪڍيو ويندو، ايستائين جو ڊگهو 12-بٽ TIN، صرف پهريان 10 اکر. اهو آهي، پهرين شرط شروع ٿيڻ کان پوء، وڌيڪ تصديق نه ڪئي وئي آهي:

هي آپريٽر جي وچ ۾ بنيادي فرق آهي | هڪ معياري ايڪسل منطق فنڪشن مان OR (يا)، جتي دليلن کي ٻيهر ترتيب ڏيڻ سان نتيجو تبديل نٿو ٿئي.

پيداوار SKUs

ڪيترين ئي ڪمپنين ۾، منفرد سڃاڻپ ڪندڙ سامان ۽ خدمتن لاءِ مقرر ڪيا ويا آهن - آرٽيڪل، SAP ڪوڊ، SKUs، وغيره. جيڪڏهن انهن جي نوٽيشن ۾ منطق آهي، ته پوءِ اهي آساني سان ڪنهن به متن مان ڪڍي سگهجن ٿا باقاعده اظهار استعمال ڪندي. مثال طور، جيڪڏهن اسان ڄاڻون ٿا ته اسان جا مضمون هميشه ٽن وڏن انگريزي اکرن تي مشتمل هوندا آهن، هڪ هائيفن ۽ بعد ۾ ٽي عددي نمبر، پوءِ:

ٽيمپليٽ جي پويان منطق سادي آهي. [AZ] - مطلب ته لاطيني الفابيٽ جا ڪي به وڏا اکر. ايندڙ quantifier 3 {} چوي ٿو ته اسان لاءِ ضروري آهي ته اهڙا ٽي اکر آهن. هائيفن کان پوء، اسان ٽن انگن جي انتظار ۾ آهيون، تنهنڪري اسان آخر ۾ شامل ڪندا آهيون ڊي{3}

نقد رقم

ساڳئي طريقي سان پوئين پيراگراف ۾، توهان پڻ ڪڍي سگهو ٿا قيمتون (قيمت، VAT ...) سامان جي وضاحت مان. جيڪڏهن مالياتي رقم، مثال طور، هڪ هائفن سان اشارو ڪيو ويو آهي، پوء:

طرز d quantifier سان + ڪنهن به نمبر لاءِ ڳولهي ٿو هڪ هائيفن تائين، ۽ ڊي{2} بعد ۾ پئسن (ٻه انگن اکرن) جي ڳولا ڪندو.

جيڪڏهن توهان کي قيمت نه، پر VAT ڪڍڻ جي ضرورت آهي، ته پوء توهان اسان جي RegExpExtract فنڪشن جو ٽيون اختياري دليل استعمال ڪري سگهو ٿا، جيڪو عنصر جو آرڊينل نمبر بيان ڪري ٿو. ۽، يقينا، توهان فنڪشن کي تبديل ڪري سگهو ٿا سبسٽيٽيوٽ (متبادل) نتيجن ۾، هائيفن کي معياري اعشاري ڌار ڪندڙ ڏانهن ۽ شروع ۾ ڊبل مائنس شامل ڪريو ته جيئن Excel مليل VAT کي عام نمبر طور بيان ڪري:

ڪار پليٽ نمبر

جيڪڏهن توهان خاص گاڏيون، ٽريلر ۽ ٻيون موٽرسائيڪلون نه کڻندا آهيو، ته پوءِ معياري ڪار نمبر کي اصول مطابق پارس ڪيو ويندو آهي ”خط – ٽي نمبر – ٻه اکر – علائقي جو ڪوڊ“. ان کان علاوه، علائقي جو ڪوڊ 2- يا 3-عددو ٿي سگھي ٿو، ۽ صرف اھي جيڪي لاطيني الفابيٽ سان ملندڙ جلندڙ آھن اکر طور استعمال ڪيا ويا آھن. اهڙيء طرح، هيٺ ڏنل باقاعده اظهار اسان کي متن مان انگ ڪڍڻ ۾ مدد ڪندي:

وقت

HH:MM فارميٽ ۾ وقت ڪڍڻ لاءِ، ھيٺ ڏنل باقاعده اظهار مناسب آھي:

ڪولن جي ٽڪر کان پوء [0-5] ڊي, جيئن اهو سمجهڻ آسان آهي، ڪنهن به نمبر کي 00-59 جي حد ۾ سيٽ ڪري ٿو. قوس ۾ ڪالون کان اڳ، ٻه نمونا ڪم ڪن ٿا، هڪ منطقي يا (پائپ) سان الڳ ٿيل:

• [0-1] ڊي - رينج ۾ ڪو به نمبر 00-19
• 2 [0-3] - رينج ۾ ڪو به نمبر 20-23

حاصل ڪيل نتيجن لاء، توھان اضافي طور تي لاڳو ڪري سگھو ٿا معياري Excel فنڪشن TIME (ٽيم)ان کي وقت جي فارميٽ ۾ تبديل ڪرڻ لاءِ جيڪو پروگرام لاءِ سمجھي سگھجي ٿو ۽ وڌيڪ حسابن لاءِ موزون آھي.

پاسورڊ چيڪ

فرض ڪريو ته اسان کي درست ڪرڻ لاءِ صارفين پاران ٺاهيل پاسورڊ جي لسٽ کي جانچڻ جي ضرورت آهي. اسان جي ضابطن جي مطابق، پاسورڊ صرف انگريزي اکر (لوئر ڪيز يا اپر ڪيز) ۽ انگن تي مشتمل هوندا. خلا، انڊر اسڪور ۽ ٻيا اوقاف جي نشانين جي اجازت ناهي.

هيٺ ڏنل سادي باقاعده اظهار کي استعمال ڪندي چيڪنگ کي منظم ڪري سگهجي ٿو:

حقيقت ۾، اهڙي نموني سان اسان کي ضرورت آهي ته شروعات جي وچ ۾ (^) ۽ آخر ($) اسان جي متن ۾ صرف اسڪوائر بریکٹ ۾ ڏنل سيٽ مان اکر هئا. جيڪڏهن توهان کي پڻ پاسورڊ جي ڊيگهه کي جانچڻ جي ضرورت آهي (مثال طور، گهٽ ۾ گهٽ 6 اکر)، پوء quantifier + بدلائي سگھجي ٿو وقفي سان ”ڇهه يا وڌيڪ“ فارم ۾ {6 ،}:

ايڊريس کان شهر

اچو ته چون ٿا ته اسان کي ايڊريس بار مان شهر کي ڪڍڻو پوندو. باقاعده پروگرام مدد ڪندو، متن کي "g" مان ڪڍڻ. ايندڙ ڪاما ڏانهن:

اچو ته هن نموني تي هڪ ويجهي نظر رکون.

جيڪڏهن توهان مٿي ڏنل متن پڙهيو آهي، ته توهان اڳ ۾ ئي سمجهي ورتو آهي ته باقاعده اظهار ۾ ڪجهه ڪردارن (مدت، ستارن، ڊالر جي نشانين، وغيره) جي هڪ خاص معني آهي. جيڪڏهن توهان انهن ڪردارن کي پاڻ کي ڳولڻ جي ضرورت آهي، ته پوء اهي هڪ پٺتي پيل کان اڳ آهن (ڪڏهن ڪڏهن سڏيو ويندو آهي ڍڪيل). تنهن ڪري، جڏهن ٽڪرا ڳولي رهيا آهيو "g." اسان کي باقاعده اظهار ۾ لکڻو پوندو مسٽر جيڪڏهن اسان هڪ پلس ڳولي رهيا آهيون، پوء + وغيره.

اسان جي ٽيمپليٽ ۾ ايندڙ ٻه اکر، ڊٽ ۽ quantifier asterisk، ڪنهن به اکرن جي ڪنهن به نمبر لاءِ بيٺا آهن، يعني ڪنهن به شهر جو نالو.

ٽيمپليٽ جي آخر ۾ ڪاما آهي، ڇاڪاڻ ته اسان "g" مان متن ڳولي رهيا آهيون. ڪاما ڏانهن. پر متن ۾ ڪيترائي ڪاما ٿي سگھن ٿا، صحيح؟ نه رڳو شهر، پر گهٽي، گهرن وغيره کان پوءِ به انهن مان اسان جي درخواست ڪنهن تي بيهندي؟ ان لاءِ سواليه نشان آهي. ان کان سواءِ، اسان جو باقاعده اظهار سڀ کان ڊگھو تار ڪڍي ڇڏيندو.

باقاعده اظهار جي لحاظ کان، اهڙي نموني "لالچ" آهي. صورتحال کي درست ڪرڻ لاءِ، هڪ سواليه نشان جي ضرورت آهي - اهو مقدار کي ٺاهيندو آهي جنهن کان پوءِ اهو بيٺو آهي "بخل" - ۽ اسان جو سوال متن کي صرف "g" کان پوءِ پهرين ڪائونٽر ڪاما تائين وٺي ٿو:

مڪمل رستو مان فائل جو نالو

ٻي تمام عام صورتحال فائل جو نالو مڪمل رستو مان ڪڍڻ آهي. فارم جو هڪ سادي باقاعده اظهار هتي مدد ڪندو:

هتي چال اها آهي ته ڳولا، حقيقت ۾، مخالف سمت ۾ ٿيندي آهي - آخر کان شروع تائين، ڇاڪاڻ ته اسان جي ٽيمپليٽ جي آخر ۾ آهي. $، ۽ اسان سڀ ڪجھ ڳولي رھيا آھيون ان کان اڳ ساڄي طرف کان پھرين پٺتي پيل تائين. پوئين مثال ۾ ڊٽ وانگر، پوئتي سليش ڀڄي ويو آهي.

PS

”آخر تائين“ مان واضع ڪرڻ چاهيان ٿو ته مٿيون سڀ شيون انهن سڀني امڪانن جو هڪ ننڍڙو حصو آهي جيڪي باقاعده اظهار مهيا ڪن ٿا. اتي ڪيترائي خاص اکر ۽ ضابطا آھن انھن جي استعمال لاءِ، ۽ ھن موضوع تي سمورا ڪتاب لکيا ويا آھن (آءٌ گھٽ ۾ گھٽ ھن کي شروع ڪرڻ جي صلاح ڏيان ٿو). هڪ طريقي سان، باقاعده اظهار لکڻ لڳ ڀڳ هڪ فن آهي. تقريبن هميشه، هڪ ايجاد ٿيل باقاعده اظهار کي بهتر يا اضافي ڪري سگهجي ٿو، ان کي وڌيڪ خوبصورت يا ان پٽ ڊيٽا جي وسيع رينج سان ڪم ڪرڻ جي قابل بڻائي.

ٻين ماڻهن جي باقاعده اظهار جو تجزيو ۽ تجزيو ڪرڻ يا پنهنجو پاڻ کي ڊيبگ ڪرڻ لاءِ، ڪيتريون ئي آسان آن لائن خدمتون آهن: RegEx101, ريگڪسر ۽ وڌيڪ

بدقسمتي سان، کلاسک باقاعده اظهار جون سڀئي خاصيتون VBA ۾ سپورٽ نه آهن (مثال طور، ريورس سرچ يا POSIX ڪلاس) ۽ سيريلڪ سان ڪم ڪري سگهن ٿيون، پر مان سمجهان ٿو ته توهان کي خوش ڪرڻ لاء پهريون ڀيرو ڪافي آهي.

جيڪڏهن توهان موضوع تي نوان نه آهيو، ۽ توهان وٽ شيئر ڪرڻ لاءِ ڪجهه آهي، ڇڏي ڏيو باقاعده اظهار ڪارآمد جڏهن Excel ۾ ڪم ڪري رهيا آهيو هيٺ ڏنل تبصرن ۾. هڪ دماغ سٺو، پر ٻه بوٽ هڪ جوڙو!

• SUBSTITUTE فنڪشن سان متن کي تبديل ڪرڻ ۽ صاف ڪرڻ
• متن ۾ لاطيني اکرن جي ڳولا ۽ نمايان ڪرڻ
• ويجھي ملندڙ متن جي ڳولا ڪريو (Ivanov = Ivonov = Ivanof، وغيره)