פונקציות REGEX לאיתור דפוסים בטקסט

אם אתם עובדים עם קבצים שמגיעים ממערכות חיצוניות שלא שומרות על מבנה טבלאי תקני, או אם יש לכם קבצים שהעובדים בארגון ממלאים באופן ידני בלי להקפיד על פורמט אחיד,
אתם בטח מכירים את התרחיש:
כתובות מייל או תעודות זהות שמופיעות בתוך טקסט ולא בעמודה נפרדת, מספרי טלפון שמגיעים עם או בלי מקפים ועוד מקרים רבים של חוסר אחידות, כיד הדימיון הטובה על מוסרי המידע…
כדי לנתח עם המידע, נדרשת עבודה סיזיפית לחילוץ הפרטים הרלוונטיים,
כשלא תמיד ניתן להשתמש בפונקציות הרגילות של אקסל כמו Mid, Right או Left משום שהן פועלות לפי מיקום, תו מסוים, או חיתוך קבוע,
ולא על פי תבניות או דפוסים, וקשה לבנות נוסחה אחת שתכסה את כל האפשרויות.

אז מה הפתרון?

באקסל 365 נוספו פונקציות REGEX (Regular Expression), שיודעות לעשות בדיוק את זה –
לזהות דפוסים או תבניות, ולשלוף את המידע הרצוי.
הפונקציות האלה אינן ייחודיות לאקסל, אך לאחר ששולבו בו, הן משמשות כלי עזר עוצמתי לקבלת המידע הרלוונטי.

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

REGEXTEST

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

=REGEXTEST(A1,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")

(אין צורך להבהל, בהמשך תקבלו גלגלי הצלה…)

REGEXEXTRACT

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

=REGEXEXTRACT(A1,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")

שימו לב שבתא A2 לא קיימת כתובת מייל תקנית, ולכן קיבלנו הודעת שגיאה, בה ניתן לטפל באמצעות IFERROR

REGEXREPLACE

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

=REGEXREPLACE(A1,".*@","")

וזו התוצאה שהתקבלה:

דברים שכדאי לשים אליהם לב בכתיבת REGEX

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

כמה מילים על הארגומנטים של פונקציות REGEX

הארגומנט הראשון יכול להיות:

• הפניה לתא (למשל – A2)
• טקסט ישיר
• טווח שלם (למשל A2:A10)

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

תחביר בסיסי

[a-z] מייצג אותיות קטנות באנגלית
[A-Z] מייצג אותיות גדולות באנגלית
[0-9] מייצג ספרות
ואם נרצה את כולם יחד, זו התבנית:
[A-Za-z0-9]
בנוסף, ישנם סימנים שנותנים הוראות כמו חיפוש מההתחלה, חיפוש עד לתו רווח וכו'.

אבל האמת היא שהבטחתי גלגל הצלה

הדרך הפשוטה ביותר לכתיבת ה REGEX היא לבקש מ Chat GPT (או כל תכנת AI אחרת) לעשות את זה.
כל מה שצריך זה לכתוב פרומפט, לדוגמה: כתוב לי בבקשה REGEX לחילוץ המייל מתוך הטקסט וכל מה שנשאר לנו לעשות זה להדביק את מה שהתקבל בתוך הגרשיים.

שאלות ותשובות בנושא REGEX באקסל

שאלה:
כיצד ניתן להגדיר את התבנית (הארגומנט השני) בתוך פונקציות אלו?
תשובה:
הארגומנט השני בפונקציה הוא ה-REGEX, המייצג את הדפוס שאותו מחפשים, ויש לכתוב אותו תמיד בתוך מרכאות כפולות.
ניתן להשתמש בתחביר בסיסי כגון [a-z] לאותיות קטנות, [A-Z] לאותיות גדולות, או [0-9] לספרות.
למי שמתקשה בכתיבת הקוד, ניתן להסתייע בכלי AI כמו Chat GPT כדי להפיק את התבנית הנדרשת ולהדביקה בנוסחה.

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

שאלה:
כיצד ניתן לטפל במצבים בהם פונקציית REGEXEXTRACT לא מוצאת התאמה בטקסט?
תשובה:
כאשר הפונקציה אינה מזהה את התבנית המבוקשת בתא, היא מחזירה הודעת שגיאה מסוג #N/A.
כדי לשמור על גיליון נקי ומקצועי, ניתן לעטוף את הנוסחה בפונקציית IFERROR, המאפשרת להגדיר ערך חלופי שיוצג במקום השגיאה.