האם אתה מייבא או מזין אי פעם נתונים ב- Excel המכילים אפסים מובילים, כגון 00123, או מספרים גדולים כגון 1234 5678 9087 6543? דוגמאות לכך הן מספרי תעודת זהות, מספרי טלפון, מספרי כרטיסי אשראי, קודי מוצר, מספרי חשבונות או מיקודי דואר. Excel מסיר באופן אוטומטי אפסים מובילים, וממיר מספרים גדולים לסימן מדעי, כגון 1.23E+15, כדי לאפשר לנוסחאות ולפעולות מתמטיות לעבוד עליהן. מאמר זה עוסק באופן שבו תוכל לשמור את הנתונים בתבנית המקורית שלהם, אשר Excel מתייחס אליה כטקסט.
המרת מספרים לטקסט בעת ייבוא נתוני טקסט
השתמש בחוויה Get & Transform (Power Query) של Excel כדי לעצב עמודות בודדות כטקסט בעת ייבוא נתונים. במקרה זה, אנו מייבאים קובץ טקסט, אך שלבי המרת הנתונים זהים עבור נתונים המיובאים ממקורות אחרים, כגון XML, אינטרנט, JSON וכן הלאה.
-
לחץ על הכרטיסיה נתונים ולאחר מכן על מטקסט/CSV לצד לחצן קבל נתונים. אם אינך רואה את לחצן קבל נתונים, עבור אל שאילתה חדשה >מקובץ > מהטקסט, אתר את קובץ הטקסט ולאחר מכן הקש על יבא.
-
Excel יטען את הנתונים שלך בחלונית תצוגה מקדימה. הקש על ערוך בחלונית התצוגה המקדימה כדי לטעון את עורך Power Query.
-
אם יש להמיר עמודות כלשהן לטקסט, בחר את העמודה שברצונך להמיר על-ידי לחיצה על כותרת העמודה ולאחר מכן עבור אל בית > המרה > נתונים > טקסט.
עצה: באפשרותך לבחור עמודות מרובות באמצעות Ctrl+לחיצה באמצעות לחצן העכבר השמאלי.
-
לאחר מכן, לחץ על החלף נוכחי בתיבת הדו-שיח שינוי סוג עמודה, ו- Excel ימיר את העמודות שנבחרו לטקסט.
-
לאחר שתסיים, לחץ על סגור &, ו- Excel יחזיר את נתוני השאילתה לגליון העבודה שלך.
אם הנתונים שלך משתנים בעתיד, באפשרותך לעבור אל נתונים > רענון , ו- Excel יעדכן את הנתונים שלך באופן אוטומטי ויחיל את ההמרות עבורך.
ב- Excel 2010 וב- Excel 2013, קיימות שתי שיטות לייבוא קבצי טקסט ולהמרת מספרים לטקסט. השיטה המומלצת היא להשתמש Power Query, הזמינה אם אתה מוריד את Power Query התוספת החדשה. אם אינך מצליח להוריד את התוספת Power Query, באפשרותך להשתמש באשף ייבוא הטקסט. במקרה זה, אנו מייבאים קובץ טקסט, אך שלבי המרת הנתונים זהים עבור נתונים המיובאים ממקורות אחרים, כגון XML, אינטרנט, JSON וכן הלאה.
-
לחץ על Power Query ברצועת הכלים ולאחר מכן בחר קבל נתונים >מהטקסט.
-
Excel יטען את הנתונים שלך בחלונית תצוגה מקדימה. הקש על ערוך בחלונית התצוגה המקדימה כדי לטעון את עורך Power Query.
-
אם יש להמיר עמודות כלשהן לטקסט, בחר את העמודה שברצונך להמיר על-ידי לחיצה על כותרת העמודה ולאחר מכן עבור אל בית > המרה > נתונים > טקסט.
עצה: באפשרותך לבחור עמודות מרובות באמצעות Ctrl+לחיצה באמצעות לחצן העכבר השמאלי.
-
לאחר מכן, לחץ על החלף נוכחי בתיבת הדו-שיח שינוי סוג עמודה, ו- Excel ימיר את העמודות שנבחרו לטקסט.
-
לאחר שתסיים, לחץ על סגור &, ו- Excel יחזיר את נתוני השאילתה לגליון העבודה שלך.
אם הנתונים שלך משתנים בעתיד, באפשרותך לעבור אל נתונים > רענון , ו- Excel יעדכן את הנתונים שלך באופן אוטומטי ויחיל את ההמרות עבורך.
שימוש בתבנית מותאמת אישית כדי לשמור על האפסים המובילים
אם ברצונך לפתור את הבעיה בתוך חוברת העבודה מכיוון שהיא אינה משמשת תוכניות אחרות כמקור נתונים, באפשרותך להשתמש בתבנית מותאמת אישית או מיוחדת כדי לשמור על האפסים המובילים. אפשרות זו פועלת עבור קודי מספרים המכילים פחות מ- 16 ספרות. בנוסף, באפשרותך לעצב את קודי המספר שלך באמצעות מקפים או סימני פיסוק אחרים. לדוגמה, כדי להפוך מספר טלפון לקריא יותר, באפשרותך להוסיף מקף בין הקוד הבינלאומי, קידומת המדינה/אזור, אזור החיוג, הקידומת ומספרי המספרים האחרונים.
|
קוד מספר |
דוגמה |
תבנית מספר מותאמת אישית |
|---|---|---|
|
חברתית |
012345678 |
000-00-0000 |
|
טלפון |
0012345556789 |
00-0-000-000-0000 |
|
הדואר |
00123 |
400000 2000 00:40 |
שלבים
-
בחר את התא או את טווח התאים שברצונך לעצב.
-
הקש Ctrl+1 כדי לטעון את תיבת הדו-שיח עיצוב תאים.
-
בחר את הכרטיסיה מספר ולאחר מכן, ברשימה קטגוריה, לחץ על מותאם אישית ולאחר מכן, בתיבה סוג, הקלד את תבנית המספר, כגון 000-00-0000 עבור קוד מספר תעודת זהות, או 00000 עבור מיקוד בן חמש ספרות.
עצה: באפשרותך גם ללחוץ על מיוחד ולאחר מכן לבחור מיקוד, מיקוד + 4, מספר טלפון או מספר תעודת זהות.
חפש מידע נוסף אודות קודים מותאמים אישית, ראה יצירה או מחיקה של תבנית מספר מותאמת אישית.
הערה: פעולה זו לא תשחזר אפסים מובילים שהוסרו לפני העיצוב. היא תשפיע רק על מספרים שהוזנו לאחר החלת התבנית.
שימוש בפונקציה TEXT להחלת תבנית
באפשרותך להשתמש בעמודה ריקה לצד הנתונים ולהשתמש בפונקציה TEXT כדי להמיר אותה לתבנית הרצויה.
|
קוד מספר |
דוגמה (בתא A1) |
הפונקציה TEXT והתבנית החדשה |
|---|---|---|
|
חברתית |
012345678 |
=TEXT(A1,"000-00-0000") |
|
טלפון |
0012345556789 |
=TEXT(A1,"00-0-000-000-0000") |
|
הדואר |
00123 |
=TEXT(A1,"00000") |
מספרי כרטיסי אשראי מעוגלים כלפי מטה
דיוק מרבי של Excel הוא 15 ספרות משמעותיות, כלומר, עבור כל מספר המכיל 16 ספרות או יותר, כגון מספר כרטיס אשראי, כל המספרים מעבר לספרה ה- 15 מעוגלים כלפי מטה לאפס. במקרה של קודי מספרים המכילים 16 ספרות ומעלה, עליך להשתמש בתבנית טקסט. לשם כך, באפשרותך לבצע אחת משתי פעולות:
-
עיצוב העמודה כטקסט
בחר את טווח הנתונים והקש Ctrl+1 כדי להפעיל את תיבת הדו-שיח עיצוב > תאים . בכרטיסיה מספר, לחץ על טקסט.
הערה: פעולה זו לא תשנה מספרים שכבר הוזנו. היא תשפיע רק על מספרים שהוזנו לאחר החלת התבנית.
-
שימוש בתו גרש
באפשרותך להקליד גרש (') לפני המספר, ו- Excel יתייחס אליו כטקסט.
זקוק לעזרה נוספת?
תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.
