ל- Excel יש קבוצה של פונקציות TEXT שיכולות לעשות פלאים. אתה יכול לבצע כל מיני פעולות טקסט וקוביות טקסט באמצעות פונקציות אלה.
אחת המשימות הנפוצות לאנשים שעובדים עם נתוני טקסט היא לחלץ מחרוזת משנה ב- Excel (כלומר, לקבל psrt של הטקסט מתא).
למרבה הצער, אין פונקציית מחרוזת משנה באקסל שיכולה לעשות זאת בקלות. עם זאת, עדיין ניתן לעשות זאת באמצעות נוסחאות טקסט וכן כמה תכונות מובנות אחרות של Excel.
הבה נבחן תחילה כמה מפונקציות הטקסט בהן נשתמש במדריך זה.
פונקציות טקסט של Excel
ל- Excel מגוון פונקציות טקסט שיקלו ממש על חילוץ מחרוזת משנה מהטקסט המקורי ב- Excel. להלן פונקציות הטקסט של Excel בהן נשתמש במדריך זה:
- הפונקציה RIGHT: מחלץ את מספר התווים שצוין מימין למחרוזת הטקסט.
- הפונקציה LEFT: מחלץ את מספר התווים שצוין משמאל למחרוזת הטקסט.
- פונקציית MID: מחלץ את מספר התווים שצוין ממיקום ההתחלה שצוין במחרוזת טקסט.
- פונקציית FIND: מוצאת את מיקום ההתחלה של הטקסט שצוין במחרוזת הטקסט.
- פונקציית LEN: מחזירה את מספר התווים במחרוזת הטקסט.
חלץ מחרוזת משנה ב- Excel באמצעות פונקציות
נניח שיש לך מערך נתונים כפי שמוצג להלן:
אלה כמה מזהי דוא"ל אקראיים (אבל גיבוריים) (למעט שלי), ובדוגמאות להלן, אראה לך כיצד לחלץ את שם המשתמש ושם הדומיין באמצעות פונקציות הטקסט ב- Excel.
דוגמה 1 - חילוץ שמות משתמש ממזהי דוא"ל
בעת שימוש בפונקציות טקסט, חשוב לזהות תבנית (אם קיימת). זה ממש מקל על בניית נוסחה. במקרה הנ"ל, התבנית היא סימן @ בין שם המשתמש ושם הדומיין, ואנו נשתמש בו כהפניה כדי לקבל את שמות המשתמש.
להלן הנוסחה לקבלת שם המשתמש:
= LEFT (A2, FIND ("@", A2) -1)
הנוסחה לעיל משתמשת בפונקציה LEFT כדי לחלץ את שם המשתמש על ידי זיהוי המיקום של הסימן @ במזהה. זה נעשה באמצעות הפונקציה FIND, המחזירה את המיקום של @.
לדוגמה, במקרה של [email protected], FIND ("@", A2) יחזיר 11, שזה מיקומו במחרוזת הטקסט.
כעת אנו משתמשים בפונקציה LEFT כדי לחלץ 10 תווים משמאל למחרוזת (אחד פחות מהערך המוחזר על ידי הפונקציה LEFT).
דוגמה 2 - חילוץ שם הדומיין ממזהי דוא"ל
ניתן להשתמש באותו היגיון המשמש בדוגמה שלמעלה כדי לקבל את שם הדומיין. הבדל קטן כאן הוא שצריך לחלץ את התווים מימין למחרוזת הטקסט.
להלן הנוסחה שתעשה זאת:
= ימינה (A2, LEN (A2) -FIND ("@", A2))
בנוסחה שלעיל, אנו משתמשים באותה לוגיקה, אך מתאימים אותה כדי לוודא שאנו מקבלים את המחרוזת הנכונה.
בואו ניקח שוב את הדוגמה של [email protected]. הפונקציה FIND מחזירה את המיקום של סימן @, שהוא 11 במקרה זה. כעת עלינו לחלץ את כל הדמויות לאחר @. אז אנו מזהים את האורך הכולל של המחרוזת ומחסירים את מספר התווים עד ל- @. הוא נותן לנו את מספר התווים המכסים את שם הדומיין מימין.
כעת אנו יכולים פשוט להשתמש בפונקציה RIGHT כדי לקבל את שם הדומיין.
דוגמה 3 - חילוץ שם הדומיין ממזהי דוא"ל (ללא .com)
כדי לחלץ מחרוזת משנה מאמצע מחרוזת טקסט, עליך לזהות את מיקום הסמן ממש לפני ואחרי מחרוזת המשנה.
לדוגמה, בדוגמה למטה, כדי לקבל את שם הדומיין ללא החלק .com, הסמן יהיה @ (שנמצא ממש לפני שם הדומיין) ו-. (שזה ממש אחריו).
להלן הנוסחה שתחלץ את שם הדומיין בלבד:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
פונקציית MID של Excel מחלצת את מספר התווים שצוין ממיקום ההתחלה שצוין. בדוגמה זו למעלה, FIND ("@", A2) +1 מציין את מיקום ההתחלה (שנמצא ממש אחרי@), ו- FIND (".", A2) -FIND ("@", A2) -1 מזהה את מספר התווים בין '@' וה '.‘
עדכון: אחד הקוראים וויליאם 19 הזכיר שהנוסחה לעיל לא תפעל במקרה שיש נקודה (.) במזהה הדוא"ל (למשל [email protected]). אז הנה הנוסחה להתמודד עם מקרים כאלה:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
שימוש בטקסט לעמודות לחילוץ מחרוזת משנה ב- Excel
השימוש בפונקציות לחילוץ מחרוזת משנה ב- Excel הוא היתרון של דינאמיות. אם תשנה את הטקסט המקורי, הנוסחה תעדכן את התוצאות באופן אוטומטי.
אם זה משהו שאולי אינך צריך, השימוש בתכונה טקסט לעמודות יכול להיות דרך קלה ומהירה לפצל את הטקסט למצעים המבוססים על סמנים שצוינו.
הנה איך לעשות זאת:
- בחר את התאים שבהם יש לך את הטקסט.
- עבור אל נתונים -> כלי נתונים -> טקסט לעמודות.
- באשף הטקסט לעמודה שלב 1, בחר מופרד ולחץ על הבא.
- בשלב 2, סמן את האפשרות אחר והזן @ בתיבה שנמצאת ממש אליה. זה יהיה התוחם שלנו שאקסל ישתמש בו כדי לפצל את הטקסט למצעים. אתה יכול לראות את התצוגה המקדימה של הנתונים למטה. לחץ על הבא.
- בשלב 3, הגדרה כללית עובדת מצוין במקרה זה. עם זאת, תוכל לבחור פורמט אחר אם אתה מפצל מספרים/תאריכים. כברירת מחדל, תא היעד הוא המקום שבו יש לך את הנתונים המקוריים. אם אתה רוצה לשמור על הנתונים המקוריים על כנם, שנה אותם לתא אחר.
- לחץ על סיום.
זה ייתן לך מיד שתי קבוצות של substrings לכל מזהה דוא"ל המשמש בדוגמה זו.
אם ברצונך לפצל את הטקסט עוד יותר (לדוגמה, פיצל batman.com לבאטמן וקום), חזור על אותו תהליך איתו.
שימוש ב- FIND ו- REPLACE כדי לחלץ טקסט מתא ב- Excel
FIND ו- REPLACE יכולה להיות טכניקה עוצמתית כאשר אתה עובד עם טקסט ב- Excel. בדוגמאות להלן תלמד כיצד להשתמש ב- FIND ו- REPLACE עם תווים כלליים כדי לעשות דברים מדהימים ב- Excel.
ראה גם: למד הכל על תווים כלליים ב- Excel.
ניקח את אותן דוגמאות של מזהי דוא"ל.
דוגמה 1 - חילוץ שמות משתמש ממזהי דוא"ל
להלן השלבים לחילוץ שמות משתמש ממזהי דוא"ל באמצעות הפונקציונליות Find and Replace:
- העתק והדבק את הנתונים המקוריים. מכיוון ש- Find and Replace פועל ומשנה את הנתונים שעליהם הם מיושמים, עדיף שיהיה גיבוי של הנתונים המקוריים.
- בחר את הנתונים ועבור לדף הבית -> עריכה -> מצא ובחר -> החלף (או השתמש בקיצור המקשים Ctrl + H).
- בתיבת הדו -שיח חפש והחלף, הזן את הדברים הבאים:
- למצוא מה: @*
- החלף ב: (השאר את זה ריק)
- לחץ על החלף הכל.
פעולה זו תסיר באופן מיידי את כל הטקסט לפני ה- @ במזהי הדוא"ל. תקבל את התוצאה כפי שמוצג להלן:
איך זה עובד ?? - בדוגמה לעיל, השתמשנו בשילוב של @ ו- *. כוכבית (*) היא תו כללי המייצג כל מספר תווים. מכאן ש-** מתכוון למחרוזת טקסט שמתחילה ב- @ ויכולה לכלול מספר תווים לאחריה. לדוגמה ב- [email protected], @* יהיה @batman.com. כאשר אנו מחליפים את @* בריק, הוא מסיר את כל התווים שאחרי @(כולל @).
דוגמה 2 - חילוץ שם הדומיין ממזהי דוא"ל
באמצעות אותו היגיון, תוכל לשנות את הקריטריונים 'מצא מה' כדי לקבל את שם הדומיין.
להלן השלבים:
- בחר את הנתונים.
- עבור לדף הבית -> עריכה -> מצא ובחר -> החלף (או השתמש בקיצור המקשים Ctrl + H).
- בתיבת הדו -שיח חפש והחלף, הזן את הדברים הבאים:
- למצוא מה: *@
- החלף ב: (השאר את זה ריק)
- לחץ על החלף הכל.
פעולה זו תסיר באופן מיידי את כל הטקסט לפני ה- @ במזהי הדוא"ל. תקבל את התוצאה כפי שמוצג להלן:
