כיצד להוסיף אפסים מובילים באקסל

מדריך זה מכסה את השיטות השונות להוסיף או לשמור אפסים מובילים וכן להסיר אפסים מובילים ב- Excel.

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

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

הוספת אפסים מובילים באקסל

בעיקרו של דבר, ישנן 2 שיטות שבהן אתה יכול להשתמש כדי להוסיף אפסים מובילים: האחת, עצב את המספר שלך כ'טקסט'; שנית, השתמש בעיצוב מותאם אישית כדי להוסיף אפסים מובילים. השיטה שבה תרצה להשתמש עשויה להיות תלויה במה שאתה רוצה לעשות עם המספר.

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

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

• שינוי פורמט התא לטקסט
• הוספת אפוסתרוף (')
• שימוש בפונקציית TEXT
• שימוש בפונקציית REPT/LEN
• השתמש בפונקציה CONCATENATE/אופרטור אמפרסנד (&)
• שימוש בפונקציית RIGHT

שינוי תבנית התא לטקסט

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

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

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

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

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

אתה יכול גם להקליד מספרי טלפון עם רווח או מקף בין מספר, Excel יתייחס אוטומטית למספרים האלה כטקסט.

שימוש ב-Leading אפוסתרוף ( ' )

דרך נוספת להוסיף אפסים מובילים באקסל היא להוסיף אפוסתרוף (') בתחילת המספר. פעולה זו תאלץ את Excel להזין מספר כטקסט.

פשוט הקלד אפוסתרוף לפני מספרים כלשהם ולחץ על 'Enter'. Excel ישאיר את האפסים המובילים ללא פגע, אך ה-(') לא יהיה גלוי בגליון העבודה אלא אם תבחר את התא.

שימוש בפונקציית טקסט

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

הפונקציה תחביר של TEXT:

= TEXT( value, format_text)

איפה,

• ערך - זהו הערך המספרי שאתה צריך כדי להמיר לטקסט ולהחיל עיצוב.
• format_text - הוא הפורמט שאתה רוצה ליישם.

עם הפונקציה TEXT, אתה יכול לציין כמה ספרות המספר שלך צריך להיות. לדוגמה, אם אתה רוצה שהמספרים שלך יהיו באורך 8 ספרות, הקלד 8 אפסים בארגומנט השני של הפונקציה: "00000000". אם יש לך מספר בן 6 ספרות בתא, אז הפונקציה תוסיף ידנית 2 אפסים מובילים ואם יש לך 2 ספרות כמו 56, השאר יהיו אפסים (00000056).

לדוגמה, כדי להוסיף אפסים מובילים ולהפוך את המספרים באורך 6 ספרות, השתמש בנוסחה זו:

=TEXT(A2,"000000")

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

הערה: אנא זכור לכלול את קודי הפורמט במרכאות כפולות בפונקציה.

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

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

שימוש בפונקציית CONCATENATE/אופרטור אמפרסנד (&)

אם ברצונך להוסיף מספר קבוע של אפסים מובילים לפני כל המספרים בעמודה, תוכל להשתמש בפונקציה CONCATENATE או באופרטור אמפרסנד (&).

תחביר של הפונקציה CONCATENATE:

=CONCATENATE(טקסט1, [טקסט2], ...)

איפה,

טקסט1 - מספר האפסים שיש להוסיף לפני המספר.

טקסט2 - המספר המקורי או הפניה לתא

תחביר של אופרטור אמפרסנד:

=Value_1 & Value_2 

איפה,

Value_1 הוא האפסים המובילים להוספה לפני המספר ו- Value_2 הוא המספר.

לדוגמה, כדי להוסיף רק שני אפסים לפני מספר, השתמש באחת מהנוסחאות הבאות:

=CONCATENATE("00",A2)‎

הארגומנט הראשון הוא שני אפסים ("00") מכיוון שאנו רוצים לרפד שני אפסים לפני המספר ב-A2 (שהוא הארגומנט השני).

אוֹ,

="00"&A2

כאן, הארגומנט הראשון הוא 2 אפסים, ואחריו האופרטור '&', והארגומנט השני הוא המספר.

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

שתי הנוסחאות הללו מצטרפות למספר מסוים של אפסים לפני המספרים המקוריים ומאחסנות אותם כמחרוזות טקסט.

שימוש בפונקציית REPT/LEN

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

=REPT(טקסט, מספר_פעמים)

כאשר 'טקסט' הוא התו שאנו רוצים לחזור עליו (במקרה שלנו '0') והארגומנט 'מספר_פעמים' הוא מספר הפעמים שאנו רוצים לחזור על התו הזה.

לדוגמה, כדי ליצור חמישה אפסים לפני מספרים, הנוסחה תיראה כך:

=REPT(0,5)&A2

מה שהנוסחה עושה זה לחזור על 5 אפסים ולהצטרף למחרוזת המספרים ב-A2 ומחזירה את התוצאה. לאחר מכן, הנוסחה מיושמת על תא B2:B6 באמצעות ידית המילוי.

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

אם אתה רוצה להוסיף אפסים מובילים בכל מקום שצריך כדי ליצור מחרוזות באורך תו ספציפי (באורך קבוע), אתה יכול להשתמש בפונקציות REPT ו-LEN יחד.

תחביר:

=REPT(text, number_times-LEN(text))&cell

לדוגמה, כדי להוסיף קידומת אפסים לערך ב-A2 וליצור מחרוזת באורך 5 תווים, נסה את הנוסחה הזו:

=REPT(0,5-LEN(A2))&A2

כאן, 'LEN(A2)' מקבל את האורך הכולל של המחרוזת/מספרים בתא A2. '5' הוא האורך המרבי של מחרוזת/מספרים שהתא צריך להיות. וחלק 'REPT(0,5-LEN(A2))' מוסיף את מספר האפסים על ידי הפחתת אורך המחרוזת ב-A2 מהמספר המרבי של אפסים (5). לאחר מכן, מספר 0 מצטרף לפני הערך של A2 כדי ליצור מחרוזת באורך קבוע.

שימוש בפונקציה הימנית

דרך נוספת לרפד אפסים מובילים לפני מחרוזת ב-Excel היא להשתמש בפונקציה RIGHT.

הפונקציה RIGHT יכולה להוסיף מספר אפסים לתחילתו של מספר ולחלץ את N התווים הימניים ביותר מהערך.

תחביר:

= ימינה (טקסט, מספר_תווים)

• טֶקסט הוא התא או הערך שממנו ברצונך לחלץ תווים.
• num_chars הוא מספר התווים שיש לחלץ מהטקסט. אם הטיעון הזה לא יינתן, רק התו הראשון ייחלץ.

עבור שיטה זו, אנו משרשרים את המספר המרבי של אפסים עם הפניה לתא המכילה את המחרוזת בארגומנט 'טקסט'.

כדי ליצור מספר בן 6 ספרות המבוסס על מחרוזת המספרים ב-A עם אפסים מובילים, נסה את הנוסחה הזו:

=RIGHT("0000000"&A2,6)

הארגומנט הראשון (הטקסט) של הנוסחה מוסיף 7 אפסים לערך ב-A2 ("0000000"&A2), ולאחר מכן מחזיר את 7 התווים הימניים ביותר, מה שמביא לכמה אפסים מובילים.

הוספת אפסים מובילים באמצעות עיצוב מספרים מותאם אישית

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

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

כדי לשנות את עיצוב המספרים של תאים, בצע את השלבים הבאים:

בחר את התא או טווח התאים שבהם ברצונך להציג אפסים מובילים. לאחר מכן, לחץ לחיצה ימנית בכל מקום בטווח שנבחר ובחר באפשרות 'עיצוב תאים' מתפריט ההקשר. או הקש על מקשי הקיצור Ctrl + 1.

בחלון עיצוב תאים, עבור ללשונית 'מספר' ובחר 'מותאם אישית' תחת אפשרויות הקטגוריה.

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

זה יציג אפסים מובילים לפני המספרים ואם המספר הוא פחות מ-6 ספרות, הוא מוסיף אפס לפניו.

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

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

• 0 – זהו מציין המיקום של הספרה שמציג אפסים נוספים. הוא מציג ספרות מאולצות 0-9 בין אם הספרה רלוונטית לערך ובין אם לאו. לדוגמה, אם תקליד 2.5 עם קוד הפורמט 000.00, הוא יציג 002.50.
• # – זהו מציין המיקום של הספרות שמציג ספרות אופציונליות ואינו כולל אפסים נוספים. לדוגמה, אם תקליד 123 עם קוד העיצוב 000#, הוא יציג 0123.

כמו כן, כל סימן פיסוק או תו אחר שתכלול בקוד הפורמט יוצג כפי שהוא. אתה יכול להשתמש בתווים כמו מקף (-), פסיק (,), לוכסן קדימה (/) וכו'.

לדוגמה, אתה יכול גם לעצב מספרים כמספרי טלפון באמצעות פורמט מותאם אישית.

תיבת הדו-שיח עיצוב קוד בעיצוב תאים:

התוצאה:

הבה ניישם את קוד העיצוב הזה בדוגמה הבאה:

##0000

כפי שאתה יכול לראות '0' יוסיף אפסים נוספים בעוד '#' לא יוסיף אפסים לא משמעותיים:

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

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

הסרת אפסים מובילים באקסל

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

ישנן דרכים שונות שבהן תוכל להסיר אפסים מובילים ב-Excel ונראה אותם אחד אחד.

הסר אפסים מובילים על ידי שינוי עיצוב התא

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

כדי להסיר את הקידומת של האפסים, בחר את התאים עם האפסים המובילים, לחץ על התיבה 'פורמט מספר' ובחר באפשרות העיצוב 'כללי' או 'מספר'.

כעת, האפסים המובילים נעלמו:

מחק אפסים מובילים על ידי המרת הטקסט למספרים

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

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

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

האפסים שלך יוסרו והמספרים יומרו בחזרה לפורמט המספרים (מיושר לימין).

הסרת אפסים מובילים על ידי הכפל/חלק ב-1

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

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

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

אתה יכול להשיג את אותן תוצאות על ידי שימוש בפקודה 'הדבקה מיוחדת'. כך:

הקלד ערך מספרי '1' בתא (נניח ב-B2) והעתק את הערך הזה.

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

בתיבת הדו-שיח הדבקה מיוחדת, תחת פעולה, בחר באפשרות 'הכפל' או 'חלוקה' ולחץ על 'אישור'.

זהו, האפסים המובילים שלך יוסרו תוך השארת המחרוזות כמספרים.

הסר אפסים מובילים באמצעות נוסחאות

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

=VALUE(A1)

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

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

=TEXT(VALUE(A1),"#")

הפונקציה VALUE ממירה את הערך ב-A1 למספר. אבל הארגומנט השני, '#' ממיר את הערך בחזרה לפורמט טקסט ללא אפסים נוספים. כתוצאה מכך, תקבל את המספרים ללא אפסים מובילים אך עדיין בפורמט הטקסט (מיושר לשמאל).

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

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

בחר את טווח התאים עם המספרים עם אפסים מובילים.

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

יופיע אשף 'המר טקסט לעמודות'. בשלב 1 מתוך 3, בחר 'מופרד' ולחץ על 'הבא'.

בשלב 2 מתוך 3, בטל את הסימון של כל המפריד ולחץ על 'הבא'.

בשלב האחרון, השאר את אפשרות פורמט הנתונים של העמודה בתור 'כללי' ובחר את היעד (התא הראשון של הטווח) שבו תרצה את המספרים שלך ללא אפסים מובילים. לאחר מכן, לחץ על 'סיום'

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

זה הכל.