אין פונקציה מובנית באקסל לחילוץ מספרים ממחרוזת בתא (ולהיפך - הסר את החלק הנומרי וחילוץ את חלק הטקסט ממחרוזת אלפאנומרית).
עם זאת, ניתן לעשות זאת באמצעות פונקציית Excel או קוד VBA פשוט.
תן לי קודם כל לספר לך על מה אני מדבר.
נניח שיש לך מערך נתונים כמו למטה ואתה רוצה לחלץ מספרים ממחרוזות (כמו למטה):
השיטה שתבחר תלויה גם בגרסת האקסל שבה אתה משתמש:
- עבור גרסאות של Excel לפני 2016, עליך להשתמש בנוסחה מעט ארוכה יותר
- עבור Excel 2016 אתה יכול להשתמש בפונקציית TEXTJOIN שהוצגה לאחרונה
- שיטת VBA זמינה עבור כל הגירסאות של Excel
תוכן
חילוץ מספרים ממחרוזות ב-Excel (נוסחאות Excel 2016)
נוסחה זו פועלת רק ב-Excel 2016 מכיוון שהיא משתמשת בפונקציית TEXTJOIN שהוצגה לאחרונה.
בנוסף, נוסחה זו יכולה לחלץ מספרים בתחילת, בסוף או באמצע מחרוזת טקסט.
שים לב שנוסחת TEXTJOIN המתוארת בסעיף זה מחברת את כל התווים המספריים.לדוגמה, אם הטקסט הוא "10 כרטיסים הם $200", התוצאה היא 10200.
נניח שיש לך מערך נתונים כמו הבא, ואתה רוצה לחלץ מספרים ממחרוזות בכל תא:
הנוסחה שלהלן תיתן לך את החלק המספרי של מחרוזת באקסל.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
זוהי נוסחת מערך, אז אתה צריך להשתמש ב" Control + Shift + Enter " במקום להשתמש ב-Enter.
נוסחה זו תחזיר ריק (מחרוזת ריקה) אם אין מספרים במחרוזת הטקסט.
איך הנוסחה הזו עובדת?
תן לי לפרק את הנוסחה הזו ולנסות להסביר איך זה עובד:
- ROW(INDIRECT(“1:”&LEN(A2))) – חלק זה של הנוסחה ייתן סדרה של מספרים החל מאחד.הפונקציה LEN בנוסחה מחזירה את המספר הכולל של התווים במחרוזת.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(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) - חלק זה של הנוסחה יחזיר מערך של #VALUEs!שגיאות או מספרים מבוססי מחרוזת.כל תווי הטקסט במחרוזת הופכים ל-#VALUE!שגיאות וכל הערכים נשארים כפי שהם.זה קורה כאשר אנו מכפילים את פונקציית MID ב-1.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – בעת שימוש בפונקציה IFERROR, כל #VALUEs מוסרים!שגיאה, רק מספרים יישארו.הפלט של חלק זה נראה כך - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – הפונקציה TEXTJOIN פשוט משלבת תווי מחרוזת עדיין שם (רק מספרים) ומתעלם מחרוזות ריקות.
טיפ מקצוען:אם ברצונך לבדוק את הפלט של נוסחה חלקית, בחר את התא, הקש F2 כדי להיכנס למצב עריכה, בחר את החלק של הנוסחה שברצונך להפיק, והקש F9.אתה תראה את התוצאות מיד.לאחר מכן זכור ללחוץ על Control + Z או ללחוץ על Escape.אל תלחץ אנטר.
אתה יכול גם להשתמש באותו היגיון כדי לחלץ חלקי טקסט ממחרוזות אלפאנומריות.הנה הנוסחה לקבל את חלק הטקסט מהמחרוזת:
=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 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" כדי להשתמש בו.קלט פשוט עובד עבור הנוסחה הזו.
הקרדיט על הנוסחה הזו מגיע למדהימיםפורום מר אקסל.
שוב, נוסחה זו תחלץ את כל המספרים במחרוזת ללא קשר למיקום.לדוגמה, אם הטקסט הוא "10 כרטיסים הם $200", התוצאה היא 10200.
注意: למרות שנוסחה זו תקפה, היא משתמשת בפונקציה נדיפה (הפונקציה INDIRECT).משמעות הדבר היא שאם אתה משתמש בו עם מערך נתונים ענק, ייתכן שייקח זמן מה לתת לך תוצאות.עדיף ליצור גיבוי לפני השימוש בנוסחה זו באקסל.
הפרד טקסט ומספרים באקסל עם VBA
אם אתה צריך לעתים קרובות להפריד בין טקסט למספרים (או לחלץ מספרים מטקסט), אתה יכול גם להשתמש בשיטת VBA.
כל מה שאתה צריך לעשות הוא ליצור פונקציה מותאמת אישית המוגדרת על ידי משתמש (UDF) ב-Excel באמצעות קוד VBA פשוט, ולאחר מכן להשתמש בנוסחת VBA זו במקום נוסחה ארוכה ומורכבת.
הרשו לי להראות לכם כיצד ליצור שתי נוסחאות ב-VBA - אחת לחילוץ מספרים ואחת לחילוץ טקסט ממחרוזות.
חילוץ מספרים ממחרוזות באקסל (באמצעות VBA)
בחלק זה, אני אראה לך כיצד ליצור פונקציה מותאמת אישית כדי לקבל רק את החלק המספרי ממחרוזת.
להלן קוד ה-VBA בו נשתמש ליצירת פונקציה מותאמת אישית זו:
פונקציה GetNumeric(CellRef As String) Dim StringLength כמספר שלם StringLength = Len(CellRef) עבור i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) ואז Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = פונקציית סוף תוצאה
להלן השלבים ליצירת פונקציה זו ולאחר מכן להשתמש בה בגליון עבודה:
כעת תוכל להשתמש בפונקציה GetText בגליון העבודה.מכיוון שעשינו את כל המשימות הכבדות בקוד עצמו, כל מה שאתה צריך לעשות הוא להשתמש בנוסחה =GetNumeric(A2).
זה ייתן לך מיד את החלק המספרי של המחרוזת.
שים לב שמכיוון שחוברת העבודה מכילה כעת קוד VBA, תצטרך לשמור אותו עם סיומת .xls או .xlsm.
אם עליך להשתמש בנוסחה זו לעתים קרובות, תוכל גם לשמור אותה בחוברת העבודה המאקרו האישית שלך.זה יאפשר לך להשתמש בנוסחה מותאמת אישית זו בכל חוברת עבודה של Excel שבה אתה משתמש.
חלץ טקסט ממחרוזת באקסל (באמצעות VBA)
בחלק זה, אני אראה לך כיצד ליצור פונקציה מותאמת אישית כדי לקבל רק את חלק הטקסט ממחרוזת.
להלן קוד ה-VBA בו נשתמש ליצירת פונקציה מותאמת אישית זו:
פונקציה GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) עבור i = 1 To StringLength אם לא (IsNumeric(Mid(CellRef, i, 1))) ואז Result = Result & Mid(CellRef, i, 1 ) הבא i GetText = פונקציית סוף תוצאה
להלן השלבים ליצירת פונקציה זו ולאחר מכן להשתמש בה בגליון עבודה:
כעת תוכל להשתמש בפונקציה GetNumeric בגליון העבודה.מכיוון שעשינו את כל המשימות הכבדות בקוד עצמו, כל מה שאתה צריך לעשות הוא להשתמש בנוסחה =GetText(A2).
זה ייתן לך מיד את החלק המספרי של המחרוזת.
שים לב שמכיוון שחוברת העבודה מכילה כעת קוד VBA, תצטרך לשמור אותו עם סיומת .xls או .xlsm.
אם עליך להשתמש בנוסחה זו לעתים קרובות, תוכל גם לשמור אותה בחוברת העבודה המאקרו האישית שלך.זה יאפשר לך להשתמש בנוסחה מותאמת אישית זו בכל חוברת עבודה של Excel שבה אתה משתמש.
