Excel formülü: Metni tarihe dönüştürme -

Genel formül

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

Özet

Tanınmayan bir tarih biçimindeki metni uygun bir Excel tarihine dönüştürmek için, metni ayrıştırabilir ve birkaç işlevi temel alan bir formülle uygun bir tarih oluşturabilirsiniz: TARİH, SOL, ORTA ve SAĞ. Gösterilen örnekte, C6'daki formül şöyledir:

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

Bu formül, yıl, ay ve gün değerlerini ayrı ayrı çıkarır ve bunları 24 Ekim 2000 tarihinde birleştirmek için TARİH işlevini kullanır.

Arka fon

Başka bir sistemden alınan verilerle çalışırken, tarihlerin Excel tarafından düzgün bir şekilde tanınmadığı, bunun yerine tarihleri ​​metin gibi ele alan bir durumla karşılaşabilirsiniz. Örneğin, aşağıdaki gibi metin değerleriniz olabilir:

Metin Temsil edilen tarih
20001024 24 Ekim 2000
20050701 1 Temmuz 20115
19980424 24 Nisan 1998
28.02.2014 28 Şubat 2014

Excel bir tarih değerini metin olarak değerlendirdiğinde, bir seçenek, metni bileşenlerine (yıl, ay, gün) ayrıştırmak için bir formül kullanmak ve bunları TARİH işleviyle bir tarih oluşturmak için kullanmaktır. Yukarıda belirtildiği gibi, bir formül kullanmadan önce ilk olarak aşağıdaki çözümleri (sıfır ekleyerek ve sütunlara metin kullanarak) denemenizi tavsiye ederim. Her iki çözüm de daha hızlıdır ve daha az çaba gerektirir.

Açıklama

TARİH işlevi, üç bağımsız değişken kullanarak geçerli bir tarih oluşturur: yıl, ay ve gün:

=DATE(year,month,day)

C6 hücresinde, bu bileşenlerin her birini bir metin dizesinden çıkarmak için SOL, ORTA ve SAĞ işlevlerini kullanırız ve sonuçları TARİH işlevine besleriz:

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

SOL işlevi yıl için en soldaki 4 karakteri ayıklar, ORTA işlevi ay için 5-6 konumlarındaki karakterleri ayıklar ve SAĞ işlevi en sağdaki 2 karakteri gün olarak ayıklar. Her sonuç doğrudan DATE işlevine döndürülür. Nihai sonuç, istediğiniz şekilde biçimlendirilebilen uygun bir Excel tarihidir.

Bu yaklaşım gerektiği gibi özelleştirilebilir. Örneğin, 8. satırdaki tanınmayan tarih biçimi gg.aa.yyyy'dir ve C8'deki formül şu şekildedir:

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

Uzun biçimli metin

Bazen, Excel'in düzgün tanımadığı "Nisan 11 2020 08:43:13" gibi daha uzun bir biçimde tarihleriniz olabilir. Bu durumda, dizeyi, Excel'in tarihi SUBSTITUTE işleviyle doğru şekilde tanımasına izin verecek şekilde ayarlayabilirsiniz. Aşağıdaki formül, ikinci boşluğun ("") yerini virgül ve boşluk (",") ile değiştirir:

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

Ay adından sonra virgül eklediğimizde, Excel tarihi anlayacaktır, ancak yine de biraz "harekete geçmesi" gerekir. Bu yüzden sonuna sıfır ekliyoruz. Matematik işlemi Excel'in dizeyi denemesine ve bir sayıya dönüştürmesine neden olur. Başarılı olduğunda, bu geçerli bir Excel tarihi ile sonuçlanacaktır. Tarihi doğru şekilde görüntülemek için tarih numarası biçimlendirmesi uygulamanız gerekebileceğini unutmayın.

Formüller olmadan

Metinden bir tarihi manuel olarak ayrıştırmak ve oluşturmak için bir formül kullanmadan önce aşağıdaki düzeltmelerden birini deneyin. İlk seçenek, Excel'i biraz "dürtmek" ve metni bir sayı olarak değerlendirmeye zorlamak için bir matematik işlemi kullanır. Excel tarihleri ​​gerçekte sayı olduğundan, bu genellikle işe yarayabilir. İşlemler başarılı olursa bir tarih biçimi uygulamanız gerekebilir.

Tarihleri ​​düzeltmek için sıfır ekleyin

Bazen, Excel'in tanıması gereken bir metin biçiminde tarihlerle karşılaşırsınız. Bu durumda, değere sıfır ekleyerek Excel'i metin değerlerini tarihlere dönüştürmeye zorlayabilirsiniz. Sıfır eklediğinizde, Excel metin değerlerini sayılara zorlamaya çalışır. Tarihler yalnızca sayılar olduğundan, bu numara, Excel'in gerçekten anlaması gereken metin biçimindeki tarihleri ​​dönüştürmenin harika bir yoludur.

Tarihleri ​​sıfır ekleyerek yerinde dönüştürmek için Özel Yapıştır'ı deneyin:

  1. Kullanılmayan bir hücreye sıfır (0) girin ve panoya kopyalayın
  2. Sorunlu tarihleri ​​seçin
  3. Özel Yapıştır> Değerler> Ekle
  4. Bir tarih biçimi uygulayın (gerekirse)

Bunun gibi bir formüle sıfır da ekleyebilirsiniz:

=A1+0

A1, tanınmayan bir tarih içerir.

Tarihleri ​​düzeltmek için sütunlara metin

Excel'in tarihleri ​​tanımasını sağlamanın bir başka yolu da Metinden Sütunlara Özelliği kullanmaktır:

Tarih sütununu seçin, ardından Veri> Sütunlara metin> Sabit> Bitir'i deneyin.

Excel tarihleri ​​tanırsa, hepsini tek adımda düzeltir.

Ilginç makaleler...