=VALUE(A2) ממירה מספר שמאוחסן כטקסט ב-A2 למספר אמיתי. מספרים כטקסט נראים רגילים, אבל SUM, AVERAGE ו-COUNT מדלגות עליהם, ולכן סכום יכול לצאת 0:
| A | B | C | |
|---|---|---|---|
| 1 | Imported | VALUE | |
| 2 | 120 | 120 | |
| 3 | 85 | 85 | |
| 4 | 240 | 240 | |
| 5 | 15 | 15 | |
| 6 | 0 | 460 |
הסכום ב-A6 הוא 0 כי ארבעת התאים בעמודה A הם טקסט (הוקלדו עם גרש, כמו שקורה הרבה בנתונים שיובאו מ-CSV או מדף אינטרנט). עמודה C ממירה כל אחד מהם ו-C6 נותן את הסכום האמיתי, 460.
איך מזהים מספר שמאוחסן כטקסט
באקסל, מספר שמאוחסן כטקסט:
- יושב בצד שמאל של התא, בעוד שמספרים יושבים בצד ימין (אלא אם היישור שונה);
- מציג משולש ירוק קטן בפינה השמאלית העליונה של התא, וכשבוחרים אותו מופיע סמל אזהרה שאומר "מספר מאוחסן כטקסט";
- נספר על ידי COUNTA אבל לא על ידי COUNT.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Value | ISNUMBER | ISTEXT | Text numbers | |
| 2 | 120 | TRUE | FALSE | 2 | |
| 3 | 85 | FALSE | TRUE | ||
| 4 | 240 | TRUE | FALSE | ||
| 5 | 15 | FALSE | TRUE |
E2 סופר כמה תאים בטווח מלאים אבל אינם מספרים: כאן שני התאים שהוקלדו עם גרש. על עמודה נקייה של מספרים הוא מחזיר 0.
ארבע נוסחאות שממירות
| A | B | C | |
|---|---|---|---|
| 1 | Text | Result | How |
| 2 | 1250 | 1250 | VALUE |
| 3 | 1250 | double minus | |
| 4 | 1250 | multiply by 1 | |
| 5 | 1250 | add 0 |
כל פעולת חשבון מכריחה את אקסל לקרוא את הטקסט כמספר, ו-VALUE היא הגרסה המפורשת. -- (שני סימני מינוס: מינוס, ואז שוב מינוס) היא הבחירה הנפוצה בתוך נוסחאות אחרות, כי היא קצרה: =SUMPRODUCT(--A2:A5) מסכמת עמודה של מספרים כטקסט בלי עמודת עזר. VALUE קוראת גם טקסט עם סימן מטבע, מפרידי אלפים או סימן אחוז: VALUE("$1,250") היא 1250 ו-VALUE("12%") היא 0.12.
| A | B | |
|---|---|---|
| 1 | Quantity (text) | Quantity |
| 2 | 125 |
תורכם: הכמות ב-A2 יובאה כטקסט. ב-B2, המירו אותה למספר.
טקסט עם יחידות או עם מפרידים אחרים
VALUE מחזירה #VALUE! כשבטקסט יש משהו שהיא לא יכולה לקרוא כמספר. שני מקרים נפוצים:
| A | B | C | |
|---|---|---|---|
| 1 | Text | Fixed | Without the fix |
| 2 | 120 kg | 120 | #VALUE! |
| 3 | 1.234,5 | 1234.5 | #VALUE! |
- יחידה או מילה: הסירו אותה קודם עם SUBSTITUTE, כמו ב-B2.
- פסיק כמפריד עשרוני, כמו ב-
1.234,5ממערכת גרמנית או ברזילאית: NUMBERVALUE (Excel 2013 ואילך) מקבלת את המפריד העשרוני ואת מפריד הקבוצות כארגומנט השני והשלישי שלה. VALUE מכירה רק את המפרידים של האקסל שלכם, ולכן C3 נכשל.
רווחים לפני הספרות או אחריהן לא עוצרים את VALUE, אבל רווחים קשיחים מדפי אינטרנט יכולים; SUBSTITUTE(A2,CHAR(160),"") מסירה אותם קודם. בשביל הסיבות הכלליות לשגיאה הזו, ראו #VALUE!.
| A | B | |
|---|---|---|
| 1 | Amount | Total |
| 2 | 120 | |
| 3 | 45 | |
| 4 | 80 |
תורכם: הסכומים ב-A2:A4 הם מספרים שמאוחסנים כטקסט. ב-B2, החזירו את הסכום שלהם בנוסחה אחת.
המרה במקום בלי נוסחה
נוסחאות שמות את המספרים בעמודה חדשה. כדי לתקן את התאים עצמם:
- המר למספר. בחרו את התאים (התא הראשון שנבחר חייב להיות תא עם המשולש הירוק), לחצו על סמל האזהרה שליד הבחירה, ובחרו המר למספר. זה התיקון המהיר ביותר.
- טקסט לעמודות. בחרו את העמודה, נתונים > טקסט לעמודות, ולחצו מיד על סיום. אקסל מזין מחדש כל תא והופך את המספרים שהם טקסט למספרים.
- הדבקה מיוחדת, הכפלה. הקלידו 1 בתא ריק והעתיקו אותו. בחרו את המספרים שהם טקסט, בית > הדבק > הדבקה מיוחדת, בחרו הכפלה ולחצו על אישור.
אם התאים מעוצבים כטקסט (בית > תבנית מספר מציג "טקסט"), העבירו אותם קודם לכללי; אחרת כל מה שתקלידו בהם יאוחסן שוב כטקסט.
טעות נפוצה: חיפושים בין טקסט למספרים
ערך חיפוש 101 לא מתאים לטקסט 101: VLOOKUP, XLOOKUP ו-MATCH מחזירות #N/A, ו-=A2=101 היא FALSE, אף ששני התאים נראים אותו דבר. המירו צד אחד כדי ששניהם יהיו מאותו סוג. כשעמודת החיפוש היא זו שמכילה טקסט, המירו במקום את הערך שאתם מחפשים:
=VLOOKUP(TEXT(E2,"0"), A2:C6, 3, FALSE) E2 is a number, column A holds text numbers
=VLOOKUP(--E2, A2:C6, 3, FALSE) E2 holds a text number, column A holds numbers
בעמוד על ISNUMBER ו-ISTEXT רואים איך בודקים כל צד.
שאלות נפוצות
איך ממירים טקסט למספר באקסל?
עם נוסחה, =VALUE(A2) או =--A2. בלי נוסחה, בחרו את התאים, לחצו על סמל האזהרה שמופיע לידם ובחרו המר למספר.
למה SUM מחזירה 0 באקסל?
המספרים מאוחסנים כטקסט, ו-SUM מדלגת על טקסט. בדרך כלל הם מיושרים לשמאל ומוצג בהם משולש ירוק קטן. המירו אותם עם =VALUE(A2), או סכמו אותם ישירות עם =SUMPRODUCT(--A2:A10).
למה המר למספר לא עובד או לא מופיע?
אקסל מציע את זה רק לטקסט שהוא יכול לקרוא כמספר. רווח קשיח מדף אינטרנט, יחידה כמו kg או פסיק עשרוני שהאקסל שלכם לא משתמש בו מסתירים את המשולש הירוק. נקו קודם את הטקסט: =VALUE(SUBSTITUTE(A2,CHAR(160),"")) או =NUMBERVALUE(A2,",",".").
איך ממירים מספרים עם פסיק כמפריד עשרוני?
השתמשו ב-NUMBERVALUE וציינו את המפרידים: =NUMBERVALUE(A2,",",".") הופכת את 1.234,5 ל-1234.5. VALUE מבינה רק את המפרידים של הגדרות האקסל שלכם.
