חלץ מספרים ממחרוזת ב- Excel (באמצעות נוסחאות או VBA)

צפה בוידאו - כיצד לחלץ מספרים ממחרוזת טקסט ב- Excel (באמצעות פורמולה ו- VBA)

אין פונקציה מובנית ב- Excel לחלץ את המספרים ממחרוזת בתא (או להיפך - הסר את החלק המספרי וחלץ את חלק הטקסט ממחרוזת אלפאנומרית).

עם זאת, ניתן לעשות זאת באמצעות קוקטייל של פונקציות Excel או קוד VBA פשוט.

תן לי קודם כל להראות לך על מה אני מדבר.

נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה לחלץ את המספרים מהמחרוזת (כפי שמוצג להלן):

השיטה שתבחר תלויה גם בגרסת ה- Excel שבה אתה משתמש:

• עבור גירסאות לפני Excel 2016, עליך להשתמש בנוסחאות מעט ארוכות יותר
• עבור Excel 2016, תוכל להשתמש בפונקציה TEXTJOIN שהוצגה לאחרונה
• ניתן להשתמש בשיטת VBA בכל גרסאות האקסל

לחץ כאן להורדת קובץ הדוגמה

חלץ מספרים מהמחרוזת ב- Excel (נוסחה עבור Excel 2016)

נוסחה זו תפעל רק ב- Excel 2016 מכיוון שהיא משתמשת בפונקציה TEXTJOIN שהוצגה לאחרונה.

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

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

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

להלן הנוסחה שתיתן לך חלק מספרי ממחרוזת ב- Excel.

= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), ""))

זוהי נוסחת מערך, לכן עליך להשתמש ב- 'Control + Shift + Enter'במקום להשתמש ב- Enter.

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

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

תן לי לשבור נוסחה זו ולנסות להסביר כיצד היא פועלת:

• ROW (INDIRECT ("1:" & LEN (A2))) - חלק זה של הנוסחה ייתן סדרת מספרים החל מאחד. הפונקציה LEN בנוסחה מחזירה את מספר התווים הכולל במחרוזת. במקרה של "העלות היא 100 דולר", היא תחזיר 19. הנוסחאות כך יהפכו לשורות (INDIRECT ("1:19"). לאחר מכן הפונקציה ROW תחזיר סדרה של מספרים - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
• (MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1) - חלק זה של הנוסחה יחזיר מערך של #VALUE! שגיאות או מספרים המבוססים על המחרוזת. כל תווי הטקסט במחרוזת הופכים ל- #VALUE! שגיאות וכל הערכים המספריים נשארים כפי שהם. זה קורה כאשר הכפלנו את פונקציית MID עם 1.
• IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), "") - כאשר נעשה שימוש בפונקציה IFERROR, היא תסיר את כל #VALUE! טעויות ורק המספרים יישארו. הפלט של חלק זה ייראה כך - {""; ""; ""; "" ""; "" "" ";" ";" "" "" "" "" "" "; ""; ""; ""; 1; 0; 0}
• = TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), "")) - הפונקציה TEXTJOIN משלבת כעת פשוט את תווי המחרוזת זה נשאר (שהם המספרים בלבד) ומתעלם מהמחרוזת הריקה.

טיפ למקצוענים: אם ברצונך לבדוק את הפלט של חלק מהנוסחה, בחר את התא, הקש F2 כדי להיכנס למצב העריכה, בחר את חלק הנוסחה שלשמה אתה רוצה את הפלט ולחץ על F9. אתה תראה את התוצאה באופן מיידי. ואז זכור ללחוץ על Control + Z או ללחוץ על מקש הבריחה. אל תלחץ על מקש enter.

הורד את קובץ הדוגמה

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

= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), MID (A2, ROW (INDIRECT ("1:" & LEN) (A2))), 1), ""))

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

לאחר מכן TEXTJOIN משמש לשילוב כל תווי הטקסט.

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

חלץ מספרים מהמחרוזת ב- Excel (עבור Excel 2013/2010/2007)

אם יש לך Excel 2013. 2010. או 2007, אינך יכול להשתמש בנוסחת TEXTJOIN, כך שתצטרך להשתמש בנוסחה מסובכת כדי לבצע זאת.

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

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

= IF (SUM (LEN (A2) -LEN (SUBSTITUTE (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," "))))> 0, SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (-MID (A2, ROW (INDIRECT (" $ 1: $ "& LEN (A2))), 1)) * ROW (INDIRECT ("$ 1: $" & LEN (A2))), 0), ROW (INDIRECT ("$ 1: $" & LEN (A2))))+1,1) * 10^ROW (INDIRECT ("$ 1: $" ו- LEN (A2)))/10), "")

במקרה שאין מספר במחרוזת הטקסט, נוסחה זו תחזיר ריק (מחרוזת ריקה).

למרות שזו נוסחת מערך, אתה לא צריך להשתמש ב- 'Control-Shift-Enter' כדי להשתמש בזה. Enter פשוט עובד עבור נוסחה זו.

קרדיט לנוסחה זו מגיע לפורום המדהים של מר אקסל.

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

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

הפרד טקסט ומספרים ב- Excel באמצעות VBA

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

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

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

חלץ מספרים מהמחרוזת ב- Excel (באמצעות VBA)

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

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

פונקציה GetNumeric (CellRef כמחרוזת) Dim StringLength כמספר שלם StringLength = Len (CellRef) עבור i = 1 ל- StringLength אם IsNumeric (Mid (CellRef, i, 1)) ואז התוצאה = תוצאה ואמצע (CellRef, i, 1) הבא i GetNumeric = פונקציית סיום התוצאה

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

• עבור לכרטיסייה מפתחים.
• לחץ על Visual Basic (תוכל גם להשתמש בקיצור המקשים ALT + F11)
• במערך האחורי של עורך VB שנפתח, לחץ באמצעות לחצן העכבר הימני על כל אחד מאובייקטים של חוברת העבודה.
• עבור אל הוספה ולחץ על מודול. זה יכניס את אובייקט המודול לחוברת העבודה.
• בחלון קוד המודול, העתק והדבק את קוד ה- VBA שהוזכר לעיל.
• סגור את עורך VB.

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

זה ייתן לך באופן מיידי רק את החלק המספרי של המחרוזת.

שים לב שמכיוון שחוברת העבודה כוללת כעת קוד VBA, עליך לשמור אותו עם סיומת .xls או .xlsm.

הורד את קובץ הדוגמה

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

חלץ טקסט ממחרוזת ב- Excel (באמצעות VBA)

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

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

פונקציה GetText (CellRef כמחרוזת) Dim StringLength כמספר שלם StringLength = Len (CellRef) For i = 1 ל- StringLength אם לא (IsNumeric (Mid (CellRef, i, 1))) ואז התוצאה = תוצאה ואמצע (CellRef, i, 1 ) הבא i GetText = פונקציית סיום התוצאה

להלן השלבים ליצירת פונקציה זו ולאחר מכן שימוש בה בדף העבודה:

• עבור לכרטיסייה מפתחים.
• לחץ על Visual Basic (תוכל גם להשתמש בקיצור המקשים ALT + F11)
• במערך האחורי של עורך VB שנפתח, לחץ באמצעות לחצן העכבר הימני על כל אחד מאובייקטים של חוברת העבודה.
• עבור אל הוספה ולחץ על מודול. זה יכניס את אובייקט המודול לחוברת העבודה.
  • אם כבר יש לך מודול, לחץ עליו פעמיים (אין צורך להכניס מודול חדש אם יש לך אותו כבר).
• בחלון קוד המודול, העתק והדבק את קוד ה- VBA שהוזכר לעיל.
• סגור את עורך VB.

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

זה ייתן לך באופן מיידי רק את החלק המספרי של המחרוזת.

שים לב שמכיוון שחוברת העבודה כוללת כעת קוד VBA, עליך לשמור אותו עם סיומת .xls או .xlsm.

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

במקרה שאתה משתמש ב- Excel 2013 או גירסאות קודמות ואין לך