בעת עבודה עם נתוני טקסט ב- Excel, ייתכן שיהיה עליך להסיר את הטקסט לפני או אחרי תו או מחרוזת טקסט ספציפית.
לדוגמה, אם ברשותך נתוני שם וציון של אנשים, ייתכן שתרצה להסיר את הייעוד לאחר הפסיק ולשמור רק את השם (או להיפך במקום בו אתה שומר את הכינוי ומסיר את השם).
לפעמים, ניתן לעשות זאת באמצעות נוסחה פשוטה או חיפוש והחלפה מהירים, ולפעמים, יש צורך בנוסחאות מורכבות יותר או בפתרונות עוקפים.
במדריך זה אראה לך כיצד להסיר את הטקסט לפני או אחרי תו ספציפי ב- Excel (באמצעות דוגמאות שונות).
אז בואו נתחיל עם כמה דוגמאות פשוטות.
הסר טקסט לאחר תו באמצעות חיפוש והחלפה
אם ברצונך להסיר במהירות את כל הטקסט לאחר מחרוזת טקסט ספציפית (או לפני מחרוזת טקסט), תוכל לעשות זאת באמצעות חיפוש והחלפה ותווים כלליים.
נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה להסיר את הייעוד אחרי תו הפסיק ולשמור את הטקסט לפני הפסיק.
להלן השלבים לביצוע פעולה זו:
- העתק והדבק את הנתונים מעמודה A לעמודה B (זה גם כדי לשמור על הנתונים המקוריים)
- כשהתאים בעמודה B נבחרים, לחץ על הכרטיסייה דף הבית
- בקבוצת העריכה, לחץ על האפשרות חפש ובחר
- באפשרויות המופיעות בתפריט הנפתח, לחץ על האפשרות החלף. פעולה זו תפתח את תיבת הדו -שיח חפש והחלפה
- בשדה 'מצא מה', הזן ,* (כלומר, פסיק ואחריו סימן כוכבית)
- השאר את השדה 'החלף ב-' ריק
- לחץ על הלחצן החלף הכל
השלבים שלעיל ימצאו את הפסיק במערך הנתונים ויסירו את כל הטקסט אחרי הפסיק (כולל הפסיק).
מכיוון שזה מחליף את הטקסט מהתאים שנבחרו, מומלץ להעתיק את הטקסט לעמודה אחרת ולאחר מכן לבצע פעולת חיפוש והחלפה זו, או ליצור עותק גיבוי של הנתונים שלך כך שהנתונים המקוריים יהיו שלמים
איך זה עובד?
* (סימן כוכבית) הוא תו כללי שיכול לייצג כל מספר תווים.
כשאני משתמש בו אחרי הפסיק (בשדה 'מצא מה') ולאחר מכן לוחץ על כפתור החלף הכל, הוא מוצא את הפסיק הראשון בתא ורואה בו התאמה.
הסיבה לכך היא שסימן הכוכבית (*) נחשב להתאמה לכל מחרוזת הטקסט העוקבת אחרי הפסיק.
אז כאשר אתה לוחץ על כפתור החלף הכל, הוא מחליף את הפסיק ואת כל הטקסט הבא.
הערה: שיטה זו עובדת היטב ואז יש לך רק פסיק אחד בכל תא (כמו בדוגמה שלנו). במקרה שיש לך מספר פסיקים, שיטה זו תמיד תמצא את הפסיק הראשון ולאחר מכן תסיר הכל אחריה. לכן אינך יכול להשתמש בשיטה זו אם ברצונך להחליף את כל הטקסט לאחר הפסיק השני ולשמור את הטקסט הראשון כפי שהוא.במקרה שתרצה להסיר את כל התווים לפני הפסיק, שנה את ערך השדה מצא איזה סימן על ידי סימן הכוכבית לפני הפסיק (*, במקום,*)
הסר טקסט באמצעות נוסחאות
אם אתה צריך שליטה רבה יותר על איך למצוא ולהחליף טקסט לפני או אחרי תו ספציפי, עדיף להשתמש בנוסחאות הטקסט המובנות ב- Excel.
נניח שיש לך את מערך הנתונים שלהלן היכן ברצונך להסיר את כל הטקסט לאחר הפסיק.
להלן הנוסחה לביצוע פעולה זו:
= LEFT (A2, FIND (",", A2) -1)
הנוסחה לעיל משתמשת בפונקציה FIND כדי למצוא את מיקום הפסיקה בתא.
לאחר מכן, מספר המיקום הזה משמש את הפונקציה LEFT כדי לחלץ את כל התווים לפני הפסיק. מכיוון שאני לא רוצה פסיק כחלק מהתוצאה, הפסקתי את 1 מהערך המתקבל של נוסחת Find.
זה היה תרחיש פשוט.
בואו ניקח אחד מעט מורכב.
נניח שיש לי את מערך הנתונים שלהלן שבו אני רוצה להסיר את כל הטקסט אחרי הפסיק השני.
להלן הנוסחה שתעשה זאת:
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", 2))-1)
מכיוון שמערך הנתונים הזה כולל פסיקים מרובים, אינני יכול להשתמש בפונקציית FIND כדי לקבל את המיקום של הפסיק הראשון ולחלץ הכל משמאלו.
אני צריך איכשהו לברר את המיקום של הפסיק השני ואז לחלץ את כל מה שנמצא משמאל לפסיקה השנייה.
לשם כך השתמשתי בפונקציה SUBSTITUTE כדי לשנות את הפסיק השני לסימן קריאה. עכשיו זה נותן לי אופי ייחודי בתא. כעת אוכל להשתמש במיקום סימן הקריאה כדי לחלץ הכל משמאל לפסיקה השנייה.
מיקום זה של סימן הקריאה משמש בפונקציה LEFT כדי לחלץ הכל לפני הפסיק השני.
בינתיים הכל טוב!
אך מה יקרה אם יש מספר פסיקים לא עקבי במערך הנתונים.
לדוגמה, במערך הנתונים שלהלן לחלק מהתאים יש שני פסיקים ולחלק מהתאים יש שלוש פסיקים, ואני צריך לחלץ את כל הטקסט לפני הפסיק האחרון.
במקרה זה, אני צריך איכשהו להבין את המיקום של ההתרחשות האחרונה של הפסיקה ואז לחלץ הכל משמאל לה.
להלן הנוסחה שתעשה זאת
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
הנוסחה לעיל משתמשת בפונקציה LEN כדי למצוא את האורך הכולל של הטקסט בתא כמו גם את אורך הטקסט ללא פסיק.
כאשר אני מפחית את שני הערכים האלה, זה נותן לי את המספר הכולל של פסיקים בתא.
אז זה ייתן לי 3 לתא A2 ו -2 לתא A4.
ערך זה משמש לאחר מכן בתוך נוסחת SUBSTITUTE להחלפת הפסיק האחרון בסימן קריאה. ואז תוכל להשתמש בפונקציה השמאלית לחלץ את כל מה שנמצא משמאל לסימן הקריאה (שם היה הפסיק האחרון)
כפי שאתה יכול לראות בדוגמאות, שימוש בשילוב נוסחאות טקסט מאפשר לך להתמודד עם מצבים רבים ושונים.
כמו כן, מכיוון שהתוצאה מקושרת לנתונים המקוריים, כאשר תשנה את הנתונים המקוריים התוצאה תתעדכן אוטומטית.
הסר טקסט באמצעות מילוי פלאש
Flash Fill הוא כלי שהוצג ב- Excel 2013 וזמין בכל הגרסאות לאחר מכן.
זה עובד על ידי זיהוי דפוסים כאשר אתה מזין את הנתונים באופן ידני ולאחר מכן מחושב כדי לתת לך את הנתונים עבור העמודה כולה.
אז אם אתה רוצה להסיר את הטקסט לפני או אחרי תו ספציפי, אתה רק צריך להראות לפיית פלאש איך תראה התוצאה (על ידי הזנתו ידנית כמה פעמים), ומילוי פלאש יבין אוטומטית את התבנית ונותן לך התוצאות.
תן לי להראות לך זאת עם דוגמה.
למטה יש לי את ערכת הנתונים שבה אני רוצה להסיר את כל הטקסט אחרי הפסיק.
להלן השלבים לביצוע פעולה זו באמצעות מילוי פלאש:
- בתא B2, שהיא העמודה הסמוכה לנתונים שלנו, הזן ידנית את 'ג'פרי האגינס' (שזו התוצאה הצפויה)
- בתא B3, הזן את 'טים סקוט' (שהיא התוצאה הצפויה לתא השני)
- בחר את הטווח B2: B10
- לחץ על הכרטיסייה דף הבית
- בקבוצה עריכה, לחץ על האפשרות הנפתחת מילוי
- לחץ על מילוי פלאש
השלבים שלעיל יתנו לך את התוצאה כפי שמוצג להלן:
תוכל גם להשתמש בקיצור המקשים Flash Fill Control + E. לאחר בחירת התאים בעמודה התוצאה (עמודה B בדוגמה שלנו)
Flash Fill הוא כלי נפלא וברוב המקרים הוא מסוגל לזהות את התבנית ולתת לך את התוצאה הנכונה. אך במקרים מסוימים יתכן שהיא לא תוכל לזהות את התבנית בצורה נכונה ויכולה לתת לך את התוצאות הלא נכונות.
לכן הקפד לבדוק שוב את תוצאות מילוי Flash שלך
ובדיוק כמו שהסרנו את כל הטקסט לאחר תו ספציפי באמצעות מילוי פלאש, תוכל להשתמש באותם שלבים כדי להסיר את הטקסט לפני תו ספציפי. רק הצג מילוי פלאש כיצד התוצאה צריכה להיראות כמו האינטרנט שלי באופן ידני בעמודה הסמוכה, וזה היה עושה את השאר.
הסר טקסט באמצעות VBA (פונקציה מותאמת אישית)
כל הרעיון של הסרת הטקסט לפני או אחרי דמות מסוימת תלוי במציאת המיקום של אותה דמות.
כאילו נראה למעלה, מציאת ההופעה האחרונה של אותה דמות טובה פירושה שימוש במכלול של נוסחאות.
אם זה משהו שאתה צריך לעשות לעתים קרובות למדי, אתה יכול לפשט את התהליך על ידי יצירת פונקציה מותאמת אישית באמצעות VBA (הנקראת פונקציות מוגדרות על ידי המשתמש).
לאחר היצירה, תוכל להשתמש בפונקציה זו שוב ושוב. זה גם הרבה יותר פשוט וקל לשימוש (מכיוון שרוב ההרמה הכבדה מתבצעת על ידי קוד ה- VBA בקצה האחורי).
מתחת לקוד VBA שבו תוכל להשתמש ליצירת הפונקציה המותאמת אישית ב- Excel:
פונקציה LastPosition (rCell As Range, rChar As String) 'פונקציה זו נותנת את המיקום האחרון של התו שצוין' קוד זה פותח על ידי Sumit Bansal (https://trumpexcel.com) Dim rLen כמספר שלם rLen = Len (rCell) עבור i = rLen לשלב -1 אם אמצע (rCell, i - 1, 1) = rChar ואז LastPosition = i - 1 יציאה מפונקציית סיום אם הבא i פונקציה סוף
עליך למקם את קוד VBA במודול רגיל בעורך VB, או בחוברת העבודה המאקרו האישית. ברגע שיש לך אותו שם, אתה יכול להשתמש בו כמו כל פונקציה אחרת של גליון עבודה בחוברת העבודה.
פונקציה זו לוקחת 2 ארגומנטים:
- הפניה לתא שעבורה ברצונך למצוא את המופע האחרון של תו או מחרוזת טקסט ספציפית
- הדמות או מחרוזת הטקסט שאת המיקום שלה אתה צריך למצוא
נניח שעכשיו יש לך את מערך הנתונים שלהלן ואתה רוצה להסיר את כל הטקסט אחרי הפסיק האחרון ורק את הטקסט לפני הפסיק האחרון.
להלן הנוסחה שתעשה זאת:
= LEFT (A2, LastPosition (A2, ",")-1)
בנוסחה שלעיל, ציינתי כדי למצוא את המיקום של הפסיק האחרון. במקרה שאתה רוצה למצוא את המיקום של תו או מחרוזת טקסט אחרים, עליך להשתמש בזה כארגומנט השני בפונקציה.
כפי שאתה יכול לראות זה הרבה יותר קצר וקל יותר לשימוש, בהשוואה לנוסחת הטקסט הארוכה שבה השתמשנו בסעיף הקודם
אם אתה שם את קוד VBA במודול בחוברת עבודה, תוכל להשתמש בפונקציה מותאמת אישית זו רק בחוברת העבודה הספציפית הזו. אם אתה רוצה להשתמש בזה בכל חוברות העבודה במערכת שלך, עליך להעתיק ולהדביק את הקוד הזה בחוברת העבודה המאקרו האישית.
אז אלו חלק מהודעות שבהן תוכל להשתמש כדי להסיר את הטקסט במהירות לפני או אחרי תו ספציפי ב- Excel.
אם מדובר בדבר חד פעמי פשוט, תוכל לעשות זאת באמצעות חיפוש והחלפה. מה אם זה קצת יותר מורכב, אז אתה צריך להשתמש בשילוב של נוסחאות Excel מובנות, או אפילו ליצור נוסחה מותאמת אישית משלך באמצעות VBA.
אני מקווה שמצאת הדרכה שימושית.
