כיצד לחלץ מספר ממחרוזת באקסל

איך ל

על ידיאדם דוידסון

יצא לאור7 ביוני 2023

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

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

כיצד לחלץ מספר מסוף מחרוזת באמצעות Flash Fill

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

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

כדי לחלץ מספר מקצה מחרוזת באמצעות Flash Fill:

1. בחר את התא שליד התא הראשון שמכיל את המחרוזות שלך.
   
2. הזן את המספר שמופיע בסוף המחרוזת.
   
3. אם כל הנתונים שלך נמצאים בעמודה רציפה ללא פערים, בחר את התא ישירות מתחת לתא שבו זה עתה הזנת את המספר.
   
4. אם לנתונים שלך יש פערים בשורות מסוימות, בחר את התא הראשון ואת כל התאים שמתחתיו שברצונך לחלץ מהם מספרים, כולל כל שורות ריקות.
   
5. ללחוץ Ctrl+E ב-Windows או Cmd+E ב-Mac כדי להפעיל את תכונת מילוי הבזק.
6. התאים הנותרים שלך יתמלאו אוטומטית על סמך הדפוס ש-Excel זיהה. ברוב המקרים, המספרים מקצה המחרוזת יחולצו בצורה נכונה.
   

כיצד לחלץ מספר מסוף מחרוזת באמצעות נוסחה

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

כדי לחלץ מספר מקצה מחרוזת באמצעות נוסחה:

1. לחץ בתא שבו ברצונך שהמספר שחולץ יופיע.
2. הזן את הנוסחה הבאה, החלף את 'B2' בתא המכיל את המחרוזת שלך:

   =VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT(" 1:"&LEN(B2))),0))))

3. ללחוץ להיכנס.
4. יש לחלץ את המספר מהמחרוזת.
5. כדי להחיל את הנוסחה על כל התאים שנותרו, לחץ על ידית הגרירה בפינה הימנית התחתונה של התא.
   
6. גרור מטה מעל התאים שבהם ברצונך שהנוסחה תשוכפל.
   
7. שחרר את העכבר והנוסחה תועתק לתאים שנבחרו.
8. תאים אלה אמורים להכיל כעת את המספרים שחולצו.
   
9. כל תאים ללא מחרוזת לחלץ ממנה יחזירו שגיאה. אם אתה רוצה להחזיר במקום תא ריק, אתה יכול במקום זאת להשתמש בנוסחה:

   =IFERROR(VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT ("1:"&LEN(B2))),0)))),"")

10. כל תאים ריקים יחזירו כעת רווח ריק.
    

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

לדוגמה, AB5HG23 ימיר ל-1, 2, 0, 4, 5, 0, 0. לאחר מכן הוא מחפש את הערך הגבוה ביותר ברשימה זו, שהוא המיקום של התו האחרון שאינו מספר. לבסוף, הוא מחזיר את כל התווים לאחר נקודה זו, שהם המספרים בסוף המחרוזת. הפונקציה VALUE ממירה את זה בחזרה למספר. פשוט, נכון?

כיצד לחלץ מספר מתחילת מחרוזת באמצעות Flash Fill

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

כדי לחלץ מספר מתחילת מחרוזת באמצעות Flash Fill:

1. בחר את התא מימין למחרוזת הראשונה שיש בה מספר שברצונך לחלץ.
2. הזן את המספר שמופיע בתחילת המחרוזת.
   
3. אם כל הנתונים שלך נמצאים בעמודה רציפה ללא פערים, בחר את התא ישירות למטה.
   
4. אם לנתונים שלך יש פערים, בחר את התא הראשון ואת כל התאים שמתחתיו ברצונך לחלץ מספרים, כולל כל שורות ריקות.
   
5. ללחוץ Ctrl+E ב-Windows או Cmd+E ב-Mac כדי להפעיל את Flash Fill.
6. התאים שבחרת אמורים להציג כעת את המספרים שחולצו.
   

כיצד לחלץ מספר מתחילת מחרוזת באמצעות נוסחה

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

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

1. בחר את התא שבו ברצונך שהמספר שחולץ יופיע.
2. הזן את הנוסחה הבאה, החלף את 'B18' בתא המכיל את המחרוזת שלך:

   =VALUE(LEFT(B18,MATCH(FALSE, ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))

3. ללחוץ להיכנס והמספר יחולץ.
   
4. אתה יכול השתמש במילוי אוטומטי באקסל כדי להעתיק את הנוסחה לכל תאים אחרים שאתה צריך.
5. אם אתה רוצה ששורות ריקות יחזירו תוצאה ריקה, השתמש בנוסחה הבאה במקום זאת:

   =IFERROR(VALUE(LEFT(B18,MATCH(FALSE, ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))," ")

כיצד לחלץ מספרים מכל מקום במחרוזת באמצעות Flash Fill

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

כדי לחלץ מספרים מכל מקום במחרוזת באמצעות Flash Fill:

1. בתא שליד המחרוזת הראשונה, הקלד ידנית את המספרים שנמצאים במחרוזת.
   
2. אם כל הנתונים שלך נמצאים בעמודה רציפה, בחר את התא ישירות למטה.
   
3. אם לנתונים שלך יש כמה פערים, בחר את התא הראשון ואת כל התאים שמתחתיו אתה רוצה לחלץ מספרים, כולל כל שורות ריקות.
   
4. ללחוץ Ctrl+E ב-Windows או Cmd+E ב-Mac כדי להפעיל את Flash Fill.
5. כעת התאים צריכים להתמלא במספרים שחולצו.
   

כיצד לחלץ מספרים מכל מקום במחרוזת באמצעות נוסחה

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

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

1. בחר את התא שבו ברצונך שהמספר שחולץ יופיע.
2. הזן את הנוסחה הבאה, החלף את 'B9' בתא המכיל את המחרוזת שלך:

   =SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN(B9) )),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

3. הנוסחה שלך צריכה לחלץ את כל המספרים מהמחרוזת.
   
4. אם אתה רוצה שתאים ריקים יחזירו תא ריק, במקום אפס, השתמש בנוסחה זו במקום זאת:

   =IFERROR(SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN( B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)," ")

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

הרחב את כישורי האקסל שלך

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

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