متن په Excel کې د منظم څرګندونو (RegExp) سره پارس کړئ

په Excel کې د متن سره کار کولو په وخت کې یو له خورا ډیر وخت اخیستونکي او مایوسونکي کارونو څخه دی جلا کول - د الفانومیریک "دلیا" په اجزاوو کې تجزیه کول او هغه ټوټې را ایستل چې موږ ورته اړتیا لرو. د مثال په توګه:

• له پتې څخه د زپ کوډ استخراج (دا ښه ده که چیرې زپ کوډ تل په پیل کې وي، مګر که دا نه وي څه شی دی؟)
• د بانک په بیان کې د تادیې توضیحاتو څخه د رسید شمیره او نیټه موندل
• د مخالفو ګوندونو په لیست کې د شرکتونو له ډول ډول توضیحاتو څخه د TIN استخراج
• په تفصیل کې د موټر شمیره یا د مقالې شمیره وپلټئ.
  

معمولا په داسې قضیو کې ، په لاسي ډول متن کې د نیم ساعت غوره کولو وروسته ، فکرونه ذهن ته راځي چې یو څه دا پروسه اتومات کړي (په ځانګړي توګه که چیرې ډیری ډیټا شتون ولري). ډیری حلونه شتون لري او د پیچلتیا - موثریت مختلف درجې سره:

• کارول جوړ شوي Excel متن افعال د لټولو-کټ-ګلو متن: LEVSIMV (لیفټ), حق (حق), پي ار ټي (منځنی), STsEPIT (CONCATENATE) او د هغې انلاګونه, ګډ کړئ (JOINTEXT), بالکل ( دقیق ) دا طریقه ښه ده که چیرې په متن کې روښانه منطق شتون ولري (د مثال په توګه، شاخص تل د پتې په پیل کې وي). که نه نو، فورمولونه خورا پیچلي کیږي او ځینې وختونه، دا حتی د صف فارمولونو ته راځي، کوم چې په لوی میزونو کې خورا ورو کوي.
• د کارولو لکه د متن ورته والی آپریټر له Visual Basic څخه په دودیز میکرو فنکشن کې پوښل شوی. دا تاسو ته اجازه درکوي د وائلډ کارډ حروفونو (*، #،؟، او داسې نور) په کارولو سره ډیر انعطاف وړ لټون پلي کړئ، له بده مرغه، دا وسیله نشي کولی د متن څخه مطلوب سبسټرینګ راوباسي - یوازې وګورئ چې دا په کې شتون لري.

د پورتني برسیره، یو بل طریقه شتون لري چې د مسلکي پروګرامرانو، ویب پراختیا کونکو او نورو تخنیکونو په محدودو حلقو کې خورا ښه پیژندل کیږي - دا دی منظم څرګندونې (منظم څرګندونې = RegExp = "regexps" = "منظم"). په ساده ډول، RegExp یوه ژبه ده چیرې چې ځانګړي حروف او قواعد په متن کې د اړین فرعي سټینګونو لټون کولو لپاره کارول کیږي، دوی یې استخراجوي یا یې د بل متن سره بدلوي.. منظم څرګندونې یو خورا پیاوړی او ښکلی وسیله ده چې د متن سره د کار کولو نورو ټولو لارو څخه د اندازې په ترتیب سره تیریږي. ډیری پروګرامینګ ژبې (C#, PHP, Perl, JavaScript…) او د متن مدیران (Word, Notepad++…) د منظم بیان ملاتړ کوي.

د مایکروسافټ ایکسل له بده مرغه د بکس څخه بهر د RegExp ملاتړ نلري، مګر دا د VBA سره په اسانۍ سره تنظیم کیدی شي. له ټب څخه د لید اساسی مدیر خلاص کړئ د پراختیا (پراختیا) یا د کیبورډ شارټ کټ alt+F11. بیا د مینو له لارې نوی ماډل داخل کړئ داخل کړئ - ماډل او هلته د لاندې میکرو فنکشن متن کاپي کړئ:

د عامه فعالیت RegExpExtract(متن د سټرینګ په توګه، نمونه د سټرینګ په توګه، اختیاري توکي د بشپړتیا په توګه = 1) د خط په توګه د خط په توګه GoTo ErrHandl Set regex = CreateObject("VBScript. RegExp") regex.Pattern = Pattern regex.Global = True. (متن) بیا میچونه ترتیب کړئ = regex.Execute(متن) RegExpExtract = matches.Item(Item - 1) Exit Function End که ErrHandl: RegExpExtract = CVErr(xlErrValue) پای فنکشن  

موږ اوس کولی شو د بصری اساسی مدیر وتړو او خپل نوی خصوصیت هڅه کولو لپاره Excel ته راستون شو. ترکیب یې په لاندې ډول دی:

=RegExpExtract( txt ; نمونه ؛ توکي )

هلته

• په ډایلولو سره - د متن سره یوه حجره چې موږ یې چک کوو او له هغه څخه موږ غواړو هغه فرعي سټینګ استخراج کړو چې موږ ورته اړتیا لرو
• بڼه - د سبسټرینګ لټون لپاره ماسک (پټنه).
• د قالب - د استخراج لپاره د فرعي سټینګ ترتیب شمیره، که چیرې یو څو شتون ولري (که مشخص نه وي، نو لومړی پیښه ښودل کیږي)

دلته ترټولو په زړه پورې شی، البته، نمونه ده - د RegExp "ژبه کې" د ځانګړو حروفونو د ټیمپلیټ تار، کوم چې مشخص کوي چې څه شی او چیرته یې موندل غواړو. دلته ستاسو د پیل کولو لپاره خورا لومړني دي:

که موږ د یو ټاکلي شمیر حروفونو په لټه کې یو، د بیلګې په توګه، شپږ عددي پوستی کوډ یا ټول درې لیکي محصول کوډونه، نو بیا موږ د ژغورنې لپاره راځي. اندازه کول or اندازه کول ځانګړي څرګندونې دي چې د لټون کولو لپاره د حروفونو شمیر مشخص کوي. مقدار کونکي په هغه کرکټر باندې پلي کیږي چې مخکې راځي:

اوس راځئ چې خورا په زړه پورې برخې ته لاړ شو - د رامینځته شوي فنکشن غوښتنلیک تحلیل او هغه څه چې موږ د ژوند څخه د عملي مثالونو نمونو په اړه زده کړل.

د متن څخه د شمیرو استخراج

د پیل کولو لپاره ، راځئ چې یو ساده قضیه تحلیل کړو - تاسو اړتیا لرئ لومړی نمبر د الفانومیریک دلیا څخه راوباسئ ، د مثال په توګه ، د نرخ لیست څخه د نه وقفېدونکي بریښنا رسولو ځواک:

د منظم بیان تر شا منطق ساده دی: d د هرې شمیرې معنی، او اندازه کونکی + وايي چې د دوی شمیر باید یو یا ډیر وي. د فنکشن په مخ کې دوه ګونی منفي ته اړتیا ده چې "په الوتنه کې" استخراج شوي حروف د شمیرې په څیر متن څخه په بشپړ شمیره بدل کړي.

پوسټکو

په لومړي نظر کې، دلته هرڅه ساده دي - موږ په قطار کې دقیقا شپږ عددونه په لټه کې یو. موږ یو ځانګړی کرکټر کاروو d د عدد او اندازه کولو لپاره ۶ {} د کرکټرونو شمیر لپاره:

په هرصورت، یو حالت ممکن وي کله چې، په لیکه کې د شاخص کیڼ اړخ ته، په قطار کې د شمیرو بله لویه سیټ وي (د تلیفون شمیره، TIN، بانکي حساب، او نور) بیا زموږ منظم فصل به لومړی 6 وباسي. له دې څخه شمیرې، یعنې په سمه توګه کار نه کوي:

د دې څخه د مخنیوي لپاره، موږ اړتیا لرو چې د خپل منظم بیان د څنډو په شاوخوا کې یو ترمیم کونکی اضافه کړو b د یوې کلمې پای ته اشاره کوي. دا به اکسل ته دا روښانه کړي چې هغه ټوټه (انډیکس) چې موږ ورته اړتیا لرو باید جلا کلمه وي، او د بلې برخې (د تلیفون شمیره) برخه نه وي:

تيليفون

په متن کې د تلیفون شمیرې موندلو کې ستونزه دا ده چې د شمیرو لیکلو لپاره ډیری اختیارونه شتون لري - د هایفنز سره او پرته ، د ځای له لارې ، په قوسونو کې د سیمې کوډ سره یا پرته ، او داسې نور. نو زما په نظر ، دا اسانه ده. لومړی دا ټول حروف د سرچینې متن څخه د څو نیسټ شوي افعالونو په کارولو سره پاک کړئ ضمیمه (موضوع)تر څو دا په یو واحد ټول کې سره یوځای شي، او بیا د ابتدايي منظم سره د{11} په یوه قطار کې 11 عددونه وباسئ:

ITN

دا دلته یو څه ډیر پیچلی دی، ځکه چې TIN (زموږ په هیواد کې) کیدای شي 10 عددي وي (د قانوني ادارو لپاره) یا 12 عددي (د افرادو لپاره). که تاسو په ځانګړې توګه نیمګړتیا ونه مومئ، نو دا خورا ممکنه ده چې د منظم څخه راضي وي د{10,12}، مګر، په کلکه سره، دا به د 10 څخه تر 12 حروف پورې ټولې شمیرې راوباسي، د بیلګې په توګه او په غلطۍ سره د 11 عددونو ته ننوتل. دا به ډیر سم وي چې د منطقي یا آپریټر لخوا وصل شوي دوه نمونې وکاروئ | (عمودی بار):

مهرباني وکړئ په یاد ولرئ چې په پوښتنه کې موږ لومړی د 12-bit شمیرو لپاره ګورو، او بیا یوازې د 10-bit شمیرو لپاره. که موږ خپل منظم بیان په بل ډول ولیکو، نو دا به د هرچا لپاره راوباسي، حتی د 12-بټ TIN اوږد، یوازې لومړی 10 حروف. دا، وروسته له دې چې لومړی حالت پیل شو، نور تایید نور نه ترسره کیږي:

دا د آپریټر تر مینځ بنسټیز توپیر دی | د معیاري ایکسل منطق فنکشن څخه OR (OR)، چیرې چې د دلیلونو بیا تنظیم کول پایله نه بدلوي.

د محصول SKUs

په ډیری شرکتونو کې، ځانګړي پیژندونکي د توکو او خدماتو لپاره ګمارل شوي - مقالې، SAP کوډونه، SKUs، او نور. که چیرې د دوی په یادښت کې منطق شتون ولري، نو دوی د منظم بیانونو په کارولو سره په اسانۍ سره له هر متن څخه ایستل کیدی شي. د مثال په توګه، که موږ پوهیږو چې زموږ مقالې تل د دریو لوی انګلیسي تورو، یو هایفن او ورپسې درې عددي شمیرې لري، نو بیا:

د کينډۍ تر شا منطق ساده دی. [AZ] - د لاتیني الفبا هر ډول لوی حروف معنی لري. راتلونکی اندازه کونکی ۶ {} وايي چې دا زموږ لپاره مهمه ده چې واقعیا درې داسې لیکونه شتون ولري. د هایفین وروسته، موږ د دریو عددونو په تمه یو، نو موږ په پای کې اضافه کوو د{3}

د نغدو پیسو اندازه

د تیرې پراګراف په څیر، تاسو کولی شئ د توکو له توضیحاتو څخه قیمتونه (لګښتونه، VAT ...) هم وباسئ. که د پیسو مقدار، د بیلګې په توګه، د هایفین سره اشاره کیږي، بیا:

بيلګه d د اندازه کولو سره + تر هایفن پورې د هرې شمیرې لټون کوي، او د{2} وروسته به د پیسو (دوه عددونو) لټون وکړي.

که تاسو اړتیا لرئ نرخونه نه ، مګر VAT استخراج کړئ ، نو تاسو کولی شئ زموږ د RegExpExtract فعالیت دریم اختیاري دلیل وکاروئ ، کوم چې د استخراج لپاره د عنصر منظم شمیر مشخص کوي. او، البته، تاسو کولی شئ فعالیت بدل کړئ ضمیمه (موضوع) په پایلو کې، د معیاري لسیزې جلا کونکي ته هایفن او په پیل کې دوه ګونی منفي اضافه کړئ ترڅو Excel وموندل VAT د نورمال شمیرې په توګه تشریح کړي:

د موټر پلیټ نمبر

که تاسو ځانګړي وسایط، ټریلرونه او نور موټرسایکلونه نه اخلئ، نو د معیاري موټر شمیره د اصولو سره سم تجزیه کیږي "خط - درې شمیرې - دوه لیکونه - د سیمې کوډ". سربیره پردې، د سیمې کوډ کیدای شي 2- یا 3 عددي وي، او یوازې هغه چې د لاتیني الفبا سره ورته وي د لیکونو په توګه کارول کیږي. په دې توګه، لاندې منظم بیان به موږ سره مرسته وکړي چې د متن څخه شمیرې راوباسي:

وخت

د HH:MM بڼه کې د وخت د استخراج لپاره، لاندې منظم بیان مناسب دی:

د کولمو ټوټې وروسته [0-5]دلکه څنګه چې دا معلومه کول اسانه دي، د 00-59 په حد کې هره شمیره ټاکي. په قوسونو کې د کولون څخه مخکې، دوه نمونې کار کوي، د منطقي یا پایپ لخوا جلا شوي:

• [0-1]د - هره شمیره د 00-19 حد کې
• 2[0-3] - هره شمیره د 20-23 حد کې

د ترلاسه شوي پایلې لپاره، تاسو کولی شئ د معیاري اکسل فنکشن هم پلي کړئ وخت (ټیم)دا د وخت په بڼه کې بدل کړئ چې پروګرام ته د پوهیدو وړ وي او د نورو حسابونو لپاره مناسب وي.

د پټنوم چک

فرض کړئ چې موږ اړتیا لرو د سموالي لپاره د کاروونکو لخوا اختراع شوي پاسورډونو لیست چیک کړو. زموږ د مقرراتو له مخې، پاسورډونه یوازې د انګلیسي توري (کوچني یا لوی توري) او شمیرې درلودلی شي. ځایونه، انډر سکورونه او نور ټکي نښې اجازه نلري.

چک کول د لاندې ساده منظم بیان په کارولو سره تنظیم کیدی شي:

په حقیقت کې، د دې ډول نمونې سره موږ اړتیا لرو چې د پیل ترمنځ (^) او پای ($) زموږ په متن کې یوازې د سیټ څخه حروف وو چې په مربع بریکٹ کې ورکړل شوي. که تاسو اړتیا لرئ د پاسورډ اوږدوالی وګورئ (د مثال په توګه لږترلږه 6 حروف)، نو بیا اندازه کونکی + په فورمه کې د وقفې "شپږ یا ډیر" لخوا بدل کیدی شي {6 ،:

له ادرس څخه ښار

راځئ چې ووایو موږ اړتیا لرو ښار د ادرس بار څخه وباسو. منظم برنامه به د "g" څخه متن استخراج کې مرسته وکړي. بلې کوما ته:

راځئ چې دې نمونې ته نږدې کتنه وکړو.

که تاسو پورته متن لوستلی وي، نو تاسو دمخه پوه شوي چې په منظم بیان کې ځینې حروف (دوره، ستوري، د ډالر نښې، او نور) ځانګړې معنی لري. که تاسو اړتیا لرئ چې دا کرکټرونه پخپله وپلټئ، نو د دوی څخه مخکې د بیک سلیش (کله ناکله ویل کیږي) خوندي کول). له همدې امله، کله چې د ټوټې "g" لټون کول. موږ باید په منظم بیان کې ولیکو ښاغلی. که موږ د پلس په لټه کې یو، بیا + او داسې نور

زموږ په ټیمپلیټ کې راتلونکي دوه حروف، نقطه او د مقدار وړ ستوري، د هر ډول حروفونو لپاره ولاړ دي، د بیلګې په توګه د کوم ښار نوم.

د کينډۍ په پای کې کوما شتون لري، ځکه چې موږ د "g" متن په لټه کې یو. کوما ته. مګر په متن کې ډیری کوما شتون لري، سمه ده؟ له ښاره وروسته نه، بلکې د کوڅې، کورونو او داسې نورو وروسته به زموږ غوښتنه په کوم لوري ودرېږي؟ دا هغه څه دي چې د پوښتنې نښه ده. پرته له دې، زموږ منظم بیان به د امکان تر ټولو اوږد تار وباسي:

د منظم بیانونو له مخې، دا ډول بڼه "لالچ" ده. د وضعیت د سمولو لپاره، د پوښتنې نښه ته اړتیا ده - دا اندازه کونکی رامینځته کوي چې وروسته دا "کنج" ودریږي - او زموږ پوښتنه متن یوازې د "g" څخه وروسته لومړۍ کاونټر کوما ته رسوي:

د دوتنې نوم د بشپړې لارې څخه

بل خورا عام حالت د بشپړې لارې څخه د فایل نوم ایستل دي. د فورمې یو ساده منظم بیان به دلته مرسته وکړي:

دلته چال دا دی چې لټون، په حقیقت کې، په مخالف لوري کې واقع کیږي - له پای څخه پیل ته، ځکه چې زموږ د ټیمپلیټ په پای کې دی. $، او موږ د ښي اړخ څخه د لومړي بیک سلیش څخه دمخه د هرڅه په لټه کې یو. بیک سلیش تښتیدلی دی، لکه په تیرو مثال کې نقطه.

PS

"د پای په لور" زه غواړم روښانه کړم چې پورته ټول د ټولو امکاناتو یوه کوچنۍ برخه ده چې منظم بیانات چمتو کوي. د دوی کارولو لپاره ډیری ځانګړي حروف او قواعد شتون لري، او په دې موضوع ټول کتابونه لیکل شوي دي (زه لږترلږه د پیل لپاره دا وړاندیز کوم). په یو ډول، د منظم بیان لیکل تقریبا یو هنر دی. تقریبا تل، یو اختراع شوی منظم بیان ښه یا ضمیمه کیدی شي، دا ډیر په زړه پورې یا د پراخو معلوماتو ډیټا سره کار کولو توان لري.

د نورو خلکو منظم بیانونه تحلیل او تجزیه کولو یا خپل ځان ډیبګ کولو لپاره ، ډیری اسانه آنلاین خدمات شتون لري: RegEx101, RegExr او نور

له بده مرغه، د کلاسیک منظم بیان ټولې ب featuresې په VBA کې نه ملاتړ کیږي (د مثال په توګه ، د لټون لټون یا POSIX ټولګي) او د سیریلیک سره کار کولی شي ، مګر زه فکر کوم چې د لومړي ځل لپاره ستاسو د خوښولو لپاره کافي دي.

که تاسو موضوع ته نوي نه یاست، او تاسو د شریکولو لپاره یو څه لرئ، په لاندې نظرونو کې په Excel کې کار کولو په وخت کې منظم څرګندونې ګټورې پریږدئ. یو ذهن ښه دی، مګر دوه بوټان یو جوړه ده!

• د SUBSTITUTE فنکشن سره د متن بدلول او پاکول
• په متن کې د لاتیني حروفونو لټون او روښانه کول
• د نږدې ورته متن لپاره لټون وکړئ (Ivanov = Ivonov = Ivanof، etc.)