נתח טקסט עם ביטויים רגולריים (RegExp) באקסל

אחת המשימות הגוזלות זמן ומתסכלות ביותר בעבודה עם טקסט באקסל היא ניתוח - ניתוח ה"דייסה" האלפאנומרית לרכיבים והפקת השברים שאנו צריכים ממנה. לדוגמה:

• חילוץ המיקוד מהכתובת (זה טוב אם המיקוד תמיד בהתחלה, אבל מה אם לא?)
• מציאת מספר ותאריך החשבונית מתוך תיאור התשלום בדף חשבון הבנק
• מיצוי TIN מתיאורים ססגוניים של חברות ברשימת הצדדים הנגדיים
• חפש מספר רכב או מאמר בתיאור וכו'.
  

בדרך כלל במקרים כאלה, לאחר חצי שעה של איסוף עגום בטקסט באופן ידני, מחשבות מתחילות לעלות בראש איכשהו כדי להפוך את התהליך הזה לאוטומטי (במיוחד אם יש הרבה נתונים). ישנם מספר פתרונות ועם דרגות שונות של מורכבות-יעילות:

• השתמש פונקציות טקסט מובנות של Excel לחיפוש-חתוך-דבק טקסט: LEVSIMV (שמאלה), ימינה (ימין), PSTR (בֵּינוֹנִי), STsEPIT (לקשר) והאנלוגים שלו, לְשַׁלֵב (JOINTEXT), מְדוּיָק (מְדוּיָק) וכו' שיטה זו טובה אם יש הגיון ברור בטקסט (לדוגמה, האינדקס תמיד בתחילת הכתובת). אחרת, הנוסחאות הופכות להרבה יותר מסובכות ולפעמים זה מגיע אפילו לנוסחאות מערך, מה שמאט מאוד בטבלאות גדולות.
• שימוש כמו אופרטור דמיון טקסט מ-Visual Basic עטוף בפונקציית מאקרו מותאמת אישית. זה מאפשר לך ליישם חיפוש גמיש יותר באמצעות תווים כלליים (*, #,? וכו') למרבה הצער, הכלי הזה לא יכול לחלץ את המחרוזת המשנה הרצויה מהטקסט - רק בדוק אם הוא כלול בו.

בנוסף לאמור לעיל, ישנה גישה נוספת המוכרת מאוד במעגלים צרים של מתכנתים מקצועיים, מפתחי אתרים ואנשי טכנולוגיה נוספים – זוהי ביטויים רגילים (ביטויים רגולריים = RegExp = "regexps" = "רגילים"). פשוט שים, RegExp היא שפה שבה משתמשים בתווים וכללים מיוחדים כדי לחפש את מחרוזות המשנה הנחוצות בטקסט, לחלץ אותן או להחליף אותן בטקסט אחר. ביטויים רגולריים הם כלי מאוד חזק ויפה שעולה בסדר גודל על כל שאר דרכי העבודה עם טקסט. שפות תכנות רבות (C#, PHP, Perl, JavaScript...) ועורכי טקסט (Word, Notepad++...) תומכים בביטויים רגולריים.

ל-Microsoft Excel למרבה הצער אין תמיכה ב-RegExp מהקופסה, אך ניתן לתקן זאת בקלות עם VBA. פתח את עורך Visual Basic מהכרטיסייה מפתחים (מפתח) או קיצור מקלדת אחר+F11. לאחר מכן הכנס את המודול החדש דרך התפריט הכנס - מודול והעתק לשם את הטקסט של פונקציית המאקרו הבאה:

פונקציה ציבורית RegExpExtract(טקסט כמחרוזת, תבנית כמחרוזת, פריט אופציונלי כמספר שלם = 1) כמחרוזת בשגיאה GoTo ErrHandl הגדר regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Text) Then Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

כעת אנו יכולים לסגור את עורך Visual Basic ולחזור ל-Excel כדי לנסות את התכונה החדשה שלנו. התחביר שלו הוא הבא:

=RegExpExtract( Txt ; Pattern ; Item )

איפה

• txt – תא עם הטקסט שאנו בודקים וממנו אנו רוצים לחלץ את המחרוזת המשנה שאנו צריכים
• דפוס - מסכה (דפוס) לחיפוש מחרוזת משנה
• פריט – מספר הרצף של המחרוזת המשנה לחילוץ, אם יש כמה מהן (אם לא צוין, המופע הראשון מוצג)

הדבר המעניין ביותר כאן, כמובן, הוא Pattern – מחרוזת תבנית של תווים מיוחדים "בשפה" של RegExp, שמפרטת מה בדיוק ואיפה אנחנו רוצים למצוא. להלן הבסיסיים ביותר כדי להתחיל:

אם אנחנו מחפשים מספר מסוים של תווים, למשל, מיקוד בן שש ספרות או כל קודי המוצר בני שלוש אותיות, אז אנחנו נחלצים לעזרה כימות or כימות הם ביטויים מיוחדים המציינים את מספר התווים שיש לחפש. מכמתים מוחלים על הדמות שמופיעה לפניה:

כעת נעבור לחלק המעניין ביותר – ניתוח היישום של הפונקציה שנוצרה ומה למדנו על דפוסים על דוגמאות מעשיות מהחיים.

חילוץ מספרים מטקסט

ראשית, בואו ננתח מקרה פשוט - אתה צריך לחלץ את המספר הראשון מדייסה אלפאנומרית, למשל, כוחם של אל-פסק מהמחירון:

ההיגיון מאחורי הביטוי הרגולרי הוא פשוט: d פירושו כל ספרה, והכמת + אומר שמספרם צריך להיות אחד או יותר. המינוס הכפול שלפני הפונקציה נחוץ כדי "בתנועה" להמיר את התווים שחולצו למספר מלא מהמספר-כטקסט.

מיקוד

במבט ראשון הכל פשוט כאן - אנחנו מחפשים בדיוק שש ספרות ברצף. אנו משתמשים בדמות מיוחדת d עבור ספרה ומכמת 6 {} למספר התווים:

עם זאת, יתכן מצב שבו משמאל לאינדקס בשורה יש עוד קבוצה גדולה של מספרים ברצף (מספר טלפון, TIN, חשבון בנק וכו') ואז העונה הרגילה שלנו תוציא את 6 הראשונים ספרות ממנו, כלומר לא יפעל כראוי:

כדי למנוע את זה, אנחנו צריכים להוסיף שינוי מסביב לקצוות של הביטוי הרגולרי שלנו b מסמל סוף מילה. זה יבהיר לאקסל שהפרגמנט (אינדקס) שאנחנו צריכים צריך להיות מילה נפרדת, ולא חלק מפרגמנט אחר (מספר טלפון):

טלפון

הבעיה במציאת מספר טלפון בטקסט היא שיש כל כך הרבה אפשרויות לכתיבת מספרים - עם ובלי מקפים, דרך רווחים, עם או בלי קוד אזור בסוגריים וכו'. לכן, לדעתי, קל יותר תחילה נקה את כל התווים הללו מטקסט המקור באמצעות מספר פונקציות מקוננות תחליף (תחליף)כך שהוא נצמד לכדי שלם אחד, ולאחר מכן עם רגיל פרימיטיבי ד{11} שלוף 11 ספרות ברצף:

ITN

זה קצת יותר מסובך כאן, כי TIN (בארצנו) יכול להיות בן 10 ספרות (עבור ישויות משפטיות) או 12 ספרות (ליחידים). אם אתה לא מוצא פגם במיוחד, אז בהחלט אפשרי להיות מרוצה מהרגיל ד{10,12}, אבל, למהדרין, הוא יוציא את כל המספרים מ-10 עד 12 תווים, כלומר והוזנו בטעות 11 ספרות. זה יהיה נכון יותר להשתמש בשני תבניות המחוברות על ידי אופרטור OR לוגי | (פס אנכי):

שימו לב שבשאילתה אנו מחפשים קודם כל מספרים של 12 סיביות, ורק אחר כך מספרים של 10 סיביות. אם נכתוב את הביטוי הרגולרי שלנו הפוך, אז הוא יוציא עבור כולם, אפילו מספרי TIN ארוכים של 12 סיביות, רק את 10 התווים הראשונים. כלומר, לאחר הפעלת התנאי הראשון, אימות נוסף אינו מבוצע עוד:

זה ההבדל המהותי בין המפעיל | מפונקציית לוגיקה סטנדרטית של אקסל OR (OR), כאשר סידור מחדש של הטיעונים אינו משנה את התוצאה.

מק"ט למוצר

בחברות רבות, מזהים ייחודיים מוקצים לסחורות ולשירותים - מאמרים, קודי SAP, SKUs וכו'. אם יש היגיון בסימון שלהם, אז ניתן לשלוף אותם בקלות מכל טקסט באמצעות ביטויים רגולריים. לדוגמה, אם אנו יודעים שהכתבות שלנו מורכבות תמיד משלוש אותיות באנגלית גדולות, מקף ומספר תלת ספרתי לאחר מכן, אז:

ההיגיון מאחורי התבנית הוא פשוט. [AZ] - פירושו כל אותיות גדולות של האלפבית הלטיני. המכמת הבא 3 {} אומר שחשוב לנו שיהיו בדיוק שלוש אותיות כאלה. אחרי המקף, אנחנו מחכים לשלוש ספרות, אז מוסיפים בסוף ד{3}

סכומי מזומן

באופן דומה לפסקה הקודמת, ניתן גם לשלוף מחירים (עלויות, מע"מ...) מתיאור הסחורה. אם סכומים כספיים, למשל, מסומנים במקף, אז:

תבנית d עם מכמת + מחפש כל מספר עד מקף, ו ד{2} יחפש פרוטות (שתי ספרות) לאחר.

אם אתה צריך לחלץ לא מחירים, אלא מע"מ, אז אתה יכול להשתמש בארגומנט האופציונלי השלישי של הפונקציה RegExpExtract שלנו, המציינת את המספר הסידורי של האלמנט שיש לחלץ. וכמובן, אתה יכול להחליף את הפונקציה תחליף (תחליף) בתוצאות, מקף למפריד העשרוני הסטנדרטי והוסף מינוס כפול בהתחלה כך ש-Excel יפרש את המע"מ שנמצא כמספר רגיל:

מספרי לוחיות רכב

אם אינך לוקח כלי רכב מיוחדים, נגררים ואופנועים אחרים, מספר המכונית הסטנדרטי מנותח על פי העיקרון "אות - שלוש ספרות - שתי אותיות - קוד אזור". יתרה מכך, קוד האזור יכול להיות בן 2 או 3 ספרות, ורק אלו הדומים במראה לאלפבית הלטיני משמשים כאותיות. לפיכך, הביטוי הרגולרי הבא יעזור לנו לחלץ מספרים מהטקסט:

זְמַן

כדי לחלץ את הזמן בפורמט HH:MM, הביטוי הרגולרי הבא מתאים:

לאחר שבר המעי הגס [0-5]ד, כפי שקל להבין, מגדיר כל מספר בטווח 00-59. לפני המעי הגס בסוגריים, פועלות שתי תבניות, מופרדות על ידי OR לוגי (צינור):

• [0-1]ד – כל מספר בטווח 00-19
• 2[0-3] – כל מספר בטווח 20-23

לתוצאה המתקבלת, אתה יכול להחיל בנוסף את הפונקציה הסטנדרטית של Excel זמן (קְבוּצָה)להמיר אותו לפורמט זמן המובן לתוכנית ומתאים לחישובים נוספים.

בדיקת סיסמאות

נניח שעלינו לבדוק את רשימת הסיסמאות שהמציאו המשתמשים לצורך תקינותן. על פי הכללים שלנו, סיסמאות יכולות להכיל רק אותיות באנגלית (אותיות קטנות או רישיות) ומספרים. רווחים, קווים תחתונים וסימני פיסוק אחרים אינם מותרים.

ניתן לארגן את הבדיקה באמצעות הביטוי הרגולרי הפשוט הבא:

למעשה, עם דפוס כזה אנו דורשים שבין ההתחלה (^) וסוף ($) בטקסט שלנו היו רק תווים מהקבוצה הניתנת בסוגריים מרובעים. אם אתה צריך גם לבדוק את אורך הסיסמה (לדוגמה, לפחות 6 תווים), אז המכמת + ניתן להחליף במרווח "שישה או יותר" בטופס {6,}:

עיר מהכתובת

נניח שאנחנו צריכים למשוך את העיר מסרגל הכתובות. התוכנית הרגילה תעזור, תשלוף את הטקסט מ-"g." לפסיק הבא:

בואו נסתכל מקרוב על הדפוס הזה.

אם קראתם את הטקסט למעלה, אז כבר הבנתם שלחלק מהתווים בביטויים רגילים (נקודות, כוכביות, סימני דולר וכו') יש משמעות מיוחדת. אם אתה צריך לחפש את הדמויות האלה בעצמן, אז לפניהן יש קו נטוי אחורי (נקרא לפעמים מיגון). לכן, כאשר מחפשים את הקטע "g." עלינו לכתוב בביטוי רגולרי מר אם אנחנו מחפשים יתרון, אז + וכו '

שני התווים הבאים בתבנית שלנו, הנקודה והכוכבית המכמתת, מייצגים כל מספר של כל תווים, כלומר כל שם עיר.

יש פסיק בסוף התבנית, מכיוון שאנו מחפשים טקסט מ-"g". לפסיק. אבל יכולים להיות כמה פסיקים בטקסט, נכון? לא רק אחרי העיר, אלא גם אחרי הרחוב, הבתים וכו'. באיזה מהם תיעצר בקשתנו? בשביל זה נועד סימן השאלה. בלעדיו, הביטוי הרגולרי שלנו היה שולף את המיתר הארוך ביותר האפשרי:

מבחינת ביטויים רגילים, דפוס כזה הוא "חמדן". כדי לתקן את המצב, יש צורך בסימן שאלה - הוא הופך את המכמת שאחריו הוא "קמצן" - והשאילתה שלנו לוקחת את הטקסט רק עד לפסיק הנגד הראשון אחרי "g.":

שם הקובץ מהנתיב המלא

מצב נפוץ נוסף הוא לחלץ את שם הקובץ מהנתיב המלא. ביטוי רגולרי פשוט של הטופס יעזור כאן:

הטריק כאן הוא שהחיפוש, למעשה, מתרחש בכיוון ההפוך - מהסוף להתחלה, כי בסוף התבנית שלנו הוא $, ואנחנו מחפשים הכל לפני זה עד לאחור הראשון מימין. האלכסון האחורי הוא בריחה, כמו הנקודה בדוגמה הקודמת.

PS

"לקראת הסוף" אני רוצה להבהיר שכל האמור לעיל הוא חלק קטן מכל האפשרויות שביטויים רגולריים מספקים. יש הרבה דמויות וכללים מיוחדים לשימוש בהם, וספרים שלמים נכתבו על הנושא הזה (אני ממליץ לפחות על זה בתור התחלה). במובן מסוים, כתיבת ביטויים רגולריים היא כמעט אומנות. כמעט תמיד, ניתן לשפר או להוסיף ביטוי רגולרי שהומצא, מה שהופך אותו לאלגנטי יותר או מסוגל לעבוד עם מגוון רחב יותר של נתוני קלט.

כדי לנתח ולנתח ביטויים רגולריים של אנשים אחרים או לנפות באגים משלך, ישנם מספר שירותים מקוונים נוחים: RegEx101, RegExr ועוד

למרבה הצער, לא כל התכונות של ביטויים רגולריים קלאסיים נתמכות ב-VBA (לדוגמה, חיפוש הפוך או מחלקות POSIX) ויכולות לעבוד עם קירילית, אבל אני חושב שמה שיש מספיק בפעם הראשונה כדי לרצות אותך.

אם אינך חדש בנושא, ויש לך מה לשתף, השאר ביטויים רגולריים שימושיים בעבודה באקסל בהערות למטה. מוח אחד זה טוב, אבל שני מגפיים הם זוג!

• החלפה וניקוי טקסט עם הפונקציה SUBSTITUTE
• חיפוש והדגשה של תווים לטיניים בטקסט
• חפש את הטקסט הדומה הקרוב ביותר (איבנוב = איבונוב = איבנוף וכו')