כיצד להמיר טקסט לתאריך באקסל

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

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

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

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

שיטות להמרת טקסט לתאריך

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

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

ישנן כמה דרכים שונות שבהן תוכל להמיר טקסט לתאריך ב- Excel, הן:

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

המר טקסט לתאריך באמצעות אפשרות בדיקת שגיאות

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

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

אם התאים שלך מציגים את מחוון השגיאה הזה, לחץ על סימן הקריאה, והוא יציג כמה אפשרויות להמרת תאריכים המעוצבים כטקסט לתאריכים בפועל. Excel יראה לך אפשרויות להמיר אותו ל-19XX או 20XX (19XX עבור 1915, 20XX עבור 2015). בחר באפשרות המתאימה.

לאחר מכן, הטקסט יומר לפורמט תאריך מתאים.

כיצד להפעיל אפשרות בדיקת שגיאות ב- Excel

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

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

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

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

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

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

נניח שהתאריכים שלך מעוצבים במחרוזות טקסט כך:

אתה יכול להשתמש באשף טקסט לעמודות כדי לאתחל מחדש את כולם במהירות לתאריכים.

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

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

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

בשלב האחרון של האשף, בחר 'תאריך' תחת פורמט נתוני עמודה ובחר את פורמט התאריך שלך מהרשימה הנפתחת שליד 'תאריכים', ולחץ על כפתור 'סיום'. במקרה שלנו, אנו ממירים את תאריכי הטקסט המיוצגים כ-"01 02 1995" (יום חודש שנה), אז אנו בוחרים 'DMY' מהרשימה הנפתחת 'תאריך:'.

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

הערה: לפני שתתחיל להשתמש בתכונת טקסט לעמודה, ודא שכל מחרוזות הטקסט שלך הן בפורמט זהה, אחרת טקסטים לא יומרו. לדוגמה, אם חלק מתאריכי הטקסט שלך מעוצבים כמו פורמט חודש/יום/שנה (MDY) בעוד שאחרים הם יום/חודש/שנה (DMY), וכאשר תבחר 'DMY' בשלב 3, תקבל תוצאות שגויות. כמו שזה מוצג בתמונה למטה.

המרת מחרוזת טקסט מורכבת לתאריך

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

לדוגמה, אם התאריכים שלך מוצגים במחרוזות טקסט מרובי חלקים, כך:

יום רביעי, 01 בפברואר, 2020

1 בפברואר 2020, 16:10

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

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

בשלב 2 של האשף, בחר את המפרידים שמכילות מחרוזות הטקסט שלך ולחץ על 'הבא'. מחרוזות הטקסט לדוגמה שלנו מופרדות על ידי פסיק ורווח - "יום שני, 01 בפברואר, 2015, 13:00". עלינו לבחור 'פסיק' ו'רווחים' כמפרידים כדי לפצל את מחרוזות הטקסט למספר עמודות.

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

כעת, חלקי התאריכים (ימים בשבוע, חודש, שנה, זמן) מחולקים בעמודות B, C, D, E, F ו-G.

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

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

=DATE(שנה,חודש,יום)

בדוגמה שלנו, חלקי החודש, היום והשנה נמצאים בעמודות C, D ו-E בהתאמה.

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

כדי להמיר שם של חודש למספר של חודש, השתמש בפונקציית MONTH זו בתוך הפונקציה DATE:

=MONTH(1&C1)

הפונקציה MONTH מוסיפה 1 לתא C2 המכיל את שם החודש כדי להמיר את שם החודש למספר החודש המתאים.

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

=DATE(E1,MONTH(1&C1),D1)

כעת השתמש בידית המילוי בפינה התחתונה של תא הנוסחה והחל את הנוסחה על העמודה.

המר טקסט לתאריך באמצעות שיטת חיפוש והחלפה

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

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

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

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

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

המר טקסט לתאריך באמצעות כלי הדבקה מיוחד

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

ראשית, בחר תא ריק והעתק אותו (בחר אותו והקש Ctrl + C להעתיק).

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

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

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

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

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

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

המר טקסט לתאריך באמצעות נוסחאות

ישנן שתי פונקציות המשמשות בעיקר להמרת טקסט לתאריך: DATEVALUE ו-VALUE.

שימוש בפונקציית DATEVALUE של Excel

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

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

=DATEVALUE(date_text)

טַעֲנָה: תאריך_טקסט מציין את מחרוזת הטקסט שברצונך להסתיר או הפניה לתא המכיל תאריכי טקסט.

הנוסחה:

=DATEVALUE(A1)

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

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

כעת יש לך את התאריכים המעוצבים שלך בעמודה C.

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

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

שימוש בפונקציית VALUE של Excel

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

הפונקציה VALUE:

=VALUE(טקסט)

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

הנוסחה לדוגמה להמרת תאריך הטקסט:

=VALUE(A1)

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

עם זאת, הפונקציה VALUE אינה תומכת בכל סוגי ערכי התאריך. לדוגמה, אם תאריכים משתמשים במקומות עשרוניים (A11), זה יחזיר את הערך #VALUE! שְׁגִיאָה.

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

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