Există multe modalități de a converti datele formatate ca text în date reale în Excel și acest tutorial își propune să le exploreze pe toate.
Când importați date în Excel, acestea pot apărea în multe forme diferite pe care Excel nu le recunoaște. Uneori, când datele sunt importate dintr-o sursă externă, acestea vor veni formatate ca text.
De asemenea, este important să știți că Excel aplică formate de dată pe baza setărilor pentru regiunea sistemului dumneavoastră. Deci, atunci când importați date în Excel care provin dintr-o altă țară, este posibil ca Excel să nu le recunoască și să le stocheze ca intrări de text.
Dacă aveți această problemă, există multe moduri pe care le puteți utiliza pentru a converti textul într-o dată în Excel și le vom acoperi pe toate în acest tutorial.
Metode de conversie a textului în dată
Dacă foaia dvs. de lucru conține date formatate ca text, în loc de date reale, nu este posibil să le utilizați în niciun calcul. Deci, trebuie să le convertiți înapoi la datele reale.
Dacă vedeți datele dvs. aliniate la stânga, înseamnă că sunt formatate ca text, deoarece textele sunt aliniate la stânga în mod implicit. Și numărul și datele sunt întotdeauna aliniate la dreapta.
Există câteva moduri diferite în care puteți converti text într-o dată în Excel, acestea sunt:
- Opțiune de verificare a erorilor
- Caracteristica Excel Text în coloane
- Găsiți și înlocuiți
- Lipire instrument special
- Formula și funcțiile Excel
Convertiți textul în dată folosind opțiunea de verificare a erorilor
Excel are o funcție încorporată de verificare a erorilor care identifică unele erori evidente în datele dvs. Dacă găsește vreo eroare, vă va afișa un mic triunghi verde (un indicator de eroare) în colțul din stânga sus al celulei, care are eroarea. Dacă selectați acea celulă, va apărea un semn de avertizare cu un semn de exclamare galben. Când mutați cursorul peste acel semn, și Excel vă va informa despre posibila problemă cu acea celulă.
De exemplu, când introduceți anul în format de două cifre în data dvs., Excel presupune data respectivă ca text și o stochează ca text. Și dacă selectați acea celulă, va apărea un semn de exclamare cu un avertisment: „Această celulă conține un șir de dată reprezentat cu doar 2 cifre ale anului”.
Dacă celulele dvs. arată acel indicator de eroare, faceți clic pe semnul exclamării și va afișa câteva opțiuni pentru a converti datele formatate ca text în date reale. Excel vă va afișa opțiuni pentru a-l converti în 19XX sau 20XX (19XX pentru 1915, 20XX pentru 2015). Selectați opțiunea corespunzătoare.
Apoi, textul va fi convertit în formatul de dată adecvat.
Cum să activați opțiunea de verificare a erorilor în Excel
De obicei, opțiunea de verificare a erorilor este activată în Excel în mod implicit. Dacă funcția de verificare a erorilor nu funcționează pentru dvs., trebuie să activați Verificarea erorilor în Excel.
Pentru a face acest lucru, faceți clic pe fila „Fișier” și selectați „Opțiuni” în panoul din stânga.
În fereastra Opțiuni Excel, faceți clic pe „Formule” în panoul din stânga, iar în panoul din dreapta, activați „Activați verificarea erorilor de fundal” în secțiunea Verificare erori. Și bifați „Celulele care conțin ani reprezentați ca 2 cifre” din secțiunea Reguli de verificare a erorilor.
Convertiți text în dată folosind funcția Excel text în coloană
Text în coloană este o caracteristică excelentă în Excel care vă permite să împărțiți datele în mai multe coloane și, de asemenea, este un instrument puternic pentru conversia valorilor textului în valori ale datei. Această metodă recunoaște mai multe formate de date diferite și le convertește în formatul de dată adecvat.
Conversia șirurilor de text simple în date
Să presupunem că datele tale sunt formatate în șiruri de text astfel:
Puteți folosi expertul Text în coloane pentru a le reforma rapid pe toate înapoi la date.
Mai întâi, selectați gama de intrări de text pe care doriți să le convertiți în date. Apoi, accesați fila „Date” din Panglică și faceți clic pe opțiunea „Text în coloane” din grupul Instrumente de date.
Va apărea expertul Text în coloane. La pasul 1 al expertului Text în coloană, alegeți opțiunea „Delimitat” sub Tipul de date original și faceți clic pe „Următorul”.
La pasul 2, debifați toate casetele „Delimitatoare” și faceți clic pe Următorul.
În pasul final al expertului, selectați „Data” sub Format de date pe coloană și alegeți formatul de dată din lista derulantă de lângă „Date” și faceți clic pe butonul „Terminare”. În cazul nostru, convertim datele text reprezentate ca „01 02 1995” (ziua lună an), așa că alegem „DMY” din lista derulantă „Data:”.
Acum, Excel convertește datele text în date reale și le afișează aliniate la dreapta în celule.
Notă: Înainte de a începe să utilizați funcția Text în coloană, asigurați-vă că toate șirurile de text sunt într-un format identic, altfel textele nu vor fi convertite. De exemplu, dacă unele dintre datele textului dvs. sunt formatate ca lună/zi/an (MDY), în timp ce altele sunt zi/lună/an (DMY), iar când alegeți „DMY” la pasul 3, veți obține rezultate incorecte. Așa cum se arată în poza de mai jos.
Conversia șirului de text complex în dată
Funcția Text în coloane este utilă atunci când doriți să convertiți șiruri de text complexe în date. Vă permite să utilizați delimitatorii pentru a identifica unde datele ar trebui să fie împărțite și afișate pe 2 sau mai multe coloane. Și combinați părțile împărțite ale datelor în întreaga dată folosind funcția DATE.
De exemplu, dacă datele dvs. sunt afișate în șiruri de text cu mai multe părți, astfel:
miercuri, 01 februarie 2020
01 februarie 2020, ora 16.10
Puteți utiliza expertul Text în coloană pentru a separa informațiile despre zi, dată și oră care sunt delimitate de virgulă și să le afișați pe mai multe coloane.
Mai întâi, selectați toate șirurile de text pe care doriți să le convertiți în date. Faceți clic pe butonul „Text în coloane” din fila „Date”. La pasul 1 al expertului Text în coloană, alegeți opțiunea „Delimitat” sub Tipul de date original și faceți clic pe „Următorul”.
În pasul 2 al expertului, selectați delimitatorii pe care îi conțin șirurile de text și faceți clic pe „Următorul”. Exemplul nostru de șiruri de text separate prin virgulă și spațiu – „Luni, 01 februarie 2015, 1:00 PM”. Ar trebui să alegem „virgulă” și „spații” ca delimitatori pentru a împărți șirurile de text în mai multe coloane.
În pasul final, selectați formatul „General” pentru toate coloanele din secțiunea Previzualizare date. Specificați unde ar trebui să fie inserate coloanele în câmpul „Destinație”, dacă nu o faceți, se vor suprascrie datele originale. Dacă doriți să ignorați o parte din datele originale, faceți clic pe ea în secțiunea Previzualizare date și selectați opțiunea „Nu importați coloana (săriți)”. Apoi, faceți clic pe „Terminare”.
Acum, părțile de date (zile ale săptămânii, lună, an, oră) sunt împărțite în coloanele B, C, D, E, F și G.
Apoi, combinați părțile date împreună cu ajutorul unei formule DATE pentru a obține întreaga dată.
Sintaxa funcției Excel DATE:
=DATA(an,lună,zi)În exemplul nostru, părțile lunii, zilei și anului sunt în coloanele C, D și, respectiv, E.
Funcția DATE recunoaște doar numere, nu text. Deoarece valorile lunii din coloana C sunt toate șiruri de text, trebuie să le convertim în numere. Pentru a face acest lucru, trebuie să utilizați funcția LUNA pentru a schimba numele lunii în numărul lunii.
Pentru a converti numele unei luni în numărul unei luni, utilizați această funcție LUNA din cadrul funcției DATE:
=LUNA(1&C1)Funcția LUNA adaugă 1 la celula C2 care conține numele lunii pentru a converti numele lunii în numărul lunii corespunzător.
Aceasta este funcția DATE pe care trebuie să o folosim pentru a combina părți de dată din diferite coloane:
=DATA(E1,LUNA(1&C1),Z1)
Acum utilizați mânerul de umplere din colțul de jos al celulei formulei și aplicați formula pe coloană.
Convertiți textul în dată folosind metoda Găsiți și înlocuiți
Această metodă folosește delimitatori pentru a schimba formatul textului în date. Dacă ziua, luna și anul din datele dvs. sunt separate de un alt delimitator decât o liniuță (-) sau o bară oblică (/), Excel nu le va recunoaște ca date și le va stoca ca text.
Pentru a remedia această problemă, utilizați funcția Găsiți și înlocuiți. Prin schimbarea delimitatorilor de perioade nestandard (.) cu bare oblice (/) sau liniuță (-), Excel va schimba automat valorile în date.
Mai întâi, selectați toate datele text pe care doriți să le convertiți în date. În fila „Acasă”, faceți clic pe butonul „Găsiți și selectați” din colțul din dreapta al Panglicii și selectați „Înlocuiți”. Alternativ, apăsați Ctrl+H pentru a deschide caseta de dialog Găsiți și înlocuiți.
În caseta de dialog Găsiți și înlocuiți, introduceți delimitatorul pe care îl conține textul dvs. (în cazul nostru punct (.) în câmpul „Găsiți ce” și o bară oblică (/) sau liniuță (-) în câmpul „Înlocuiți cu” Faceți clic pe butonul „Înlocuiește tot” pentru a înlocui delimitatorii și faceți clic pe „Închidere” pentru a închide fereastra.
Acum, Excel recunoaște că șirurile dvs. de text sunt acum date și le formatează automat ca date. Datele dvs. vor fi aliniate la dreapta, așa cum se arată mai jos.
Convertiți textul în dată folosind instrumentul special Paste
O altă modalitate rapidă și ușoară de a converti șirurile de text în date este prin adăugarea 0 la șirul de text folosind opțiunea specială de lipire. Adăugarea zero la valoare transformă textul în numărul de serie al datei, pe care îl puteți formata ca dată.
Mai întâi, selectați o celulă goală și copiați-o (selectați-o și apăsați Ctrl + C a copia).
Apoi, selectați celulele care conțin datele text pe care doriți să le convertiți, faceți clic dreapta și selectați opțiunea „Lipire specială”.
În caseta de dialog Lipire specială, selectați „Toate” în secțiunea Lipire, alegeți „Adăugați” în secțiunea Operație și faceți clic pe „OK”.
De asemenea, puteți efectua alte operații aritmetice scăderea/înmulțirea/împărțirea valorii din celula destinație cu valoarea lipită (cum ar fi înmulțirea celulelor cu 1 sau împărțirea cu 1 sau scăderea zero).
Când selectați „Adăugați” în Operațiune, se adaugă „zero” la toate datele text selectate, deoarece adăugarea unui „0” nu schimbă valorile, veți primi numărul de serie pentru fiecare dată. Acum tot ce trebuie să faci este să schimbi formatul celulelor.
Selectați numerele de serie și, în fila „Acasă”, faceți clic pe lista derulantă „Format număr” din grupul Numere. Selectați opțiunea „Data scurtă” din meniul drop-down.
După cum puteți vedea acum, numerele sunt formatate ca date și aliniate la dreapta.
Convertiți textul în dată folosind formule
Există două funcții utilizate în principal pentru a converti textul în data: DATEVALUE și VALUE.
Utilizarea funcției Excel DATEVALUE
Funcția Excel DATEVALUE este una dintre cele mai simple modalități de a converti o dată reprezentată ca text în numărul de serie al unei date.
Sintaxa funcției DATEVALUE:
=DATEVALUE(text_data)Argument: data_text specifică șirul de text pe care doriți să îl acoperiți sau să faceți referire la celula care conține date text.
Formula:
=DATEVALUE(A1)Următoarea imagine ilustrează modul în care funcția DATEVALUE gestionează câteva formate diferite de dată care sunt stocate ca text.
Aveți numerele de serie ale datei, acum trebuie să aplicați formatul de dată acestor numere. Pentru a face acest lucru, selectați celulele cu numere de serie, apoi accesați fila „Acasă” și alegeți „Data scurtă” din lista derulantă „Format număr”.
Acum aveți datele formatate în coloana C.
Chiar dacă nu există o parte de an în data textului dvs. (A8), DATEVALUE va folosi anul curent din ceasul computerului dvs.
Funcția DATEVALUE va converti numai valorile text care arată ca o dată. Nu poate converti un text care seamănă cu un număr până în prezent și nici nu poate modifica o valoare numerică până în prezent, pentru asta veți avea nevoie de funcția VALOARE din Excel.
Utilizarea funcției Excel VALUE
Funcția Excel VALUE este capabilă să convertească orice șir de text care seamănă cu o dată sau un număr într-o valoare numerică, deci este foarte utilă atunci când vine vorba de conversia oricărui număr, nu doar a datelor.
Funcția VALUE:
=VALUE(text)text– șirul de text pe care dorim să-l convertim sau trimiterea la celula care conține șirul de text.
Exemplul de formulă pentru a converti data textului:
=VALOARE(A1)Formula de mai jos VALUE poate schimba orice șir de text care arată ca o dată într-un număr, așa cum se arată mai jos.
Cu toate acestea, funcția VALUE nu acceptă toate tipurile de valori ale datei. De exemplu, dacă datele folosesc zecimale (A11), va returna #VALoare! eroare.
Odată ce aveți numărul de serie pentru data dvs., va trebui să formatați celula cu numărul de serie al datei pentru a face să arate ca o dată, așa cum am făcut pentru funcția DATEVALUE. Pentru a face acest lucru, selectați numerele de serie și selectați opțiunea „Dată” din meniul derulant „Format număr” din fila „Acasă”.
Asta este, acestea sunt cele 5 moduri diferite prin care puteți converti datele formatate ca text în date în Excel.