לְהִצטַיֵן

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

Convert Text Date

נוסחת Excel: המרת טקסט לתאריךנוסחה כללית | _+_ | סיכום

כדי להמיר טקסט בתבנית תאריך לא מזוהה לתאריך אקסל תקין, תוכל לנתח את הטקסט ולהרכיב תאריך מתאים עם נוסחה המבוססת על מספר פונקציות: DATE, LEFT, MID ו- RIGHT. בדוגמה המוצגת, הנוסחה ב- C6 היא:

= DATE ( LEFT (text,4), MID (text,5,2), RIGHT (text,2))

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



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

רקע כללי

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



טֶקסט התאריך המיוצג
20001024 24 באוקטובר 2000
20050701 1 ביולי 2011 5
19980424 24 באפריל, 1998
02.28.2014 28 בפברואר 2014

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

הֶסבֵּר

ה פונקציית DATE יוצר תאריך תקף באמצעות שלושה ארגומנטים: שנה, חודש ויום:



 
= DATE ( LEFT (B6,4), MID (B6,5,2), RIGHT (B6,2))

בתא C6 אנו משתמשים בפונקציות LEFT, MID ו- RIGHT כדי לחלץ כל אחד מהרכיבים הללו ממחרוזת טקסט ולהזין את התוצאות לפונקציית DATE:

כמה קינן אם הצהרות מצטיינות
 
= DATE (year,month,day)

ה פונקציית שמאל מחלץ את 4 הדמויות השמאליות ביותר לשנה, ה- פונקציית MID מחלץ תווים במיקומים 5-6 למשך חודש, ו- הפונקציה RIGHT מחלץ את 2 התווים הימניים ביותר ביום. כל תוצאה מוחזרת ישירות לפונקציית DATE. התוצאה הסופית היא תאריך אקסל נכון שניתן לעצב בכל דרך שתרצו.

ניתן להתאים אישית גישה זו לפי הצורך. לדוגמה, תבנית התאריך הלא מזוהה בשורה 8 היא dd.mm.yyyy והנוסחה ב- C8 היא:



 
= DATE ( LEFT (B6,4), MID (B6,5,2), RIGHT (B6,2))

טקסט בעל צורה ארוכה

לפעמים יתכנו תאריכים בצורה ארוכה יותר כמו '11 באפריל 2020 08:43:13' ש- Excel אינו מזהה כראוי. במקרה זה, ייתכן שתוכל להתאים את המחרוזת באופן המאפשר ל- Excel לזהות נכון את התאריך עם פונקציית SUBSTITUTE . הנוסחה שלהלן מחליפה את המופע השני של רווח ('') בפסיק ומרווח (','):

 
= DATE ( RIGHT (B8,4), MID (B8,4,2), LEFT (B8,2))

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

בלי נוסחאות

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

הוסף אפס לתיקון תאריכים

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

תפקוד שמאל וימין באקסל

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

מחק את כל השורות הריקות ב- Excel
  1. הזן אפס (0) בתא שאינו בשימוש והעתק ללוח
  2. בחר את התאריכים הבעייתיים
  3. הדבק מיוחד> ערכים> הוסף
  4. החל פורמט תאריך (במידת הצורך)

אתה יכול גם להוסיף אפס בנוסחה כזו:

 
= SUBSTITUTE (A2,' ',', ',2)+0 // add comma after month

כאשר A1 מכיל תאריך לא מזוהה.

שלח טקסט לעמודות לתיקון תאריכים

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

בחר בעמודת התאריכים ולאחר מכן נסה נתונים> טקסט לעמודות> קבוע> סיום

אם Excel מזהה את התאריכים, זה יתקן את כולם בשלב אחד.

הסופר דייב בראנס


^