מצא את המיקום של ההתרחשות האחרונה של דמות ב- Excel

במדריך זה תלמד כיצד למצוא את המיקום של המופע האחרון של דמות במחרוזת ב- Excel.

לפני מספר ימים, עמית הגיע לבעיה זו.

הייתה לו רשימה של כתובות אתרים כפי שמוצג להלן, והוא היה צריך לחלץ את כל הדמויות לאחר קו הרוחב האחרון ("/").

כך למשל, מתוך https://example.com/archive/יָנוּאָר הוא נאלץ לחלץ את 'ינואר'.

זה היה ממש קל אם היו רק קו רוחב אחד קדימה בכתובות האתרים.

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

במקרים כאלה, הטריק הוא למצוא את המיקום של המופע האחרון של קו הרוחב הקדמי בכתובת ה- URL.

במדריך זה אראה לך שתי דרכים לעשות זאת:

• שימוש בנוסחת אקסל
• שימוש בפונקציה מותאמת אישית (נוצרה באמצעות VBA)

קבלת המיקום האחרון של דמות באמצעות נוסחת Excel

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

להלן הנוסחה שתמצא את המיקום האחרון של קו נטוי קדימה ותחלץ את כל הטקסט מימין לה.

= ימינה (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "/", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "/", "")))), 1 )) 

כיצד פועלת נוסחה זו?

בואו נשבור את הנוסחה ונסביר כיצד כל חלק בה עובד.

• תחליף (A2, ”/”,“”) - חלק זה של הנוסחה מחליף את קו הרוחב הקדמי במחרוזת ריקה. אז למשל, אם אתה רוצה למצוא את המופע של מחרוזת אחרת מלבד קו הרוחב קדימה, השתמש בזה כאן.
• LEN (A2) -LEN (תחליף (A2, ”/”,“”)) - חלק זה יגיד לך כמה קווים קדימה יש במחרוזת. זה פשוט חוסך את אורך המחרוזת ללא החתך הקדמי מאורך המחרוזת עם קווים קדימה.
• SUBSTITUTE (A2, ”/”, ”@”, LEN (A2) -LEN (SUBSTITUTE (A2, ”/”, ””))) - חלק זה של הנוסחה יחליף את קו הרוחב האחרון קדימה ב- @. הרעיון הוא להפוך את הדמות הזו לייחודית. אתה יכול להשתמש בכל דמות שאתה רוצה. רק וודא שהוא ייחודי ואינו מופיע כבר במחרוזת.
• FIND ("@", SUBSTITUTE (A2, "/", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "/", ""))), 1) - חלק זה של הנוסחה ייתן לך את המיקום של קו הרוחב האחרון קדימה.
• LEN (A2) -FIND ("@", SUBSTITUTE (A2, "/", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "/", "")))), 1) - חלק זה של הנוסחה יספר לנו כמה תווים יש לאחר החתך האחרון קדימה.
• = ימינה (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "/", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "/", "")))), 1 )) עכשיו זה פשוט היה נותן לנו את המחרוזת אחרי החתך האחרון קדימה.

קבלת המיקום האחרון של דמות באמצעות פונקציה מותאמת אישית (VBA)

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

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

הבה נשתמש באותה קבוצת נתונים של כתובות אתרים (כפי שמוצג להלן):

במקרה זה, יצרתי פונקציה בשם LastPosition, שמוצאת את המיקום האחרון של התו שצוין (המהווה קו נטוי קדימה במקרה זה).

להלן הנוסחה שתעשה זאת:

= ימינה (A2, LEN (A2) -LastPosition (A2, "/")+1)

אתה יכול לראות שזה הרבה יותר פשוט מזה שהשתמשנו למעלה.

כך זה עובד:

• LastPosition - שהיא הפונקציה המותאמת אישית שלנו - מחזירה את המיקום של קו החיתוך הקדמי. פונקציה זו לוקחת שני ארגומנטים - הפניה לתא שיש לה את כתובת האתר והתו שאת מיקומו עלינו למצוא.
• לאחר מכן הפונקציה RIGHT נותנת לנו את כל הדמויות לאחר קו הרוחב הקדמי.

להלן קוד ה- VBA שיצר את הפונקציה הזו:

פונקציה LastPosition (rCell As Range, rChar As String) 'פונקציה זו נותנת את המיקום האחרון של התו שצוין' קוד זה פותח על ידי Sumit Bansal (https://trumpexcel.com) Dim rLen כמספר שלם rLen = Len (rCell) עבור i = rLen עד שלב 1 -1 אם אמצע (rCell, i - 1, 1) = rChar ואז LastPosition = i יציאה מהפונקציה סוף אם הבא i פונקציה סוף

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

להלן השלבים להעתיק ולהדביק קוד זה בקצה האחורי של VB:

להלן השלבים למיקום הקוד הזה בעורך VB:

1. עבור לכרטיסייה מפתחים.
2. לחץ על האפשרות Visual Basic. פעולה זו תפתח את עורך ה- VB בקצה האחורי.
3. בחלונית סייר הפרויקט בעורך VB, לחץ באמצעות לחצן העכבר הימני על כל אובייקט עבור חוברת העבודה שבה ברצונך להכניס את הקוד. אם אינך רואה את סייר הפרוייקטים עבור לכרטיסייה תצוגה ולחץ על סייר הפרוייקטים.
4. עבור אל הוספה ולחץ על מודול. זה יכניס אובייקט מודול לחוברת העבודה שלך.
5. העתק והדבק את הקוד בחלון המודול.

כעת הנוסחה תהיה זמינה בכל גליונות העבודה של חוברת העבודה.

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

אולי תאהב גם את מדריכי האקסל הבאים:

• כיצד להשיג את ספירת המילים ב- Excel.
• כיצד להשתמש ב- VLOOKUP עם קריטריונים מרובים.
• מצא את ההופעה האחרונה של ערך חיפוש רשימה ברשימה ב- Excel.
• חלץ מחרוזת משנה ב- Excel.