Excel

Converteix text a la data

Convert Text Date

Fórmula Excel: convertiu text a la dataFórmula genèrica
= DATE ( LEFT (text,4), MID (text,5,2), RIGHT (text,2))
Resum

Per convertir text en un format de data no reconegut a una data Excel adequada, podeu analitzar el text i muntar una data adequada amb una fórmula basada en diverses funcions: DATA, ESQUERRA, MID i DRETA. A l'exemple que es mostra, la fórmula a C6 és:





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

Aquesta fórmula extreu els valors de l'any, mes i dia per separat i utilitza la funció DATE per reunir-los a la data del 24 d'octubre de 2000.

Nota: abans d’utilitzar una fórmula, vegeu a continuació altres maneres de convertir text a dates.

Antecedents

Quan treballeu amb dades d’un altre sistema, us podeu trobar amb una situació en què Excel no reconeix correctament les dates, que en canvi tracta les dates com a text. Per exemple, podeu tenir valors de text com aquest:





Text Data representada
20001024 24 d’octubre del 2000
20050701 1 de juliol de 20115
19980424 24 d’abril de 1998
2014.02.28 28 de febrer de 2014

Quan Excel ha avaluat un valor de data com a text, una opció és utilitzar una fórmula per analitzar el text en els seus components (any, mes, dia) i utilitzar-los per fer una data amb la funció DATE. Com s’ha indicat anteriorment, us recomano que proveu primer les solucions següents (afegir zero i utilitzar text a les columnes) abans d’utilitzar una fórmula. Ambdues solucions són més ràpides i requereixen menys esforç.

Explicació

El Funció DATA crea una data vàlida mitjançant tres arguments: any, mes i dia:



 
= DATE (year,month,day)

A la cel·la C6, fem servir les funcions LEFT, MID i RIGHT per extreure cadascun d’aquests components d’una cadena de text i introduir els resultats a la funció DATE:

com trobar el quartil 1 a Excel
 
= DATE ( LEFT (B6,4), MID (B6,5,2), RIGHT (B6,2))

El Funció ESQUERRA extreu els 4 caràcters més a l'esquerra de l'any, el Funció MID extreu caràcters a les posicions 5-6 durant el mes i Funció DRETA extreu els 2 caràcters més a la dreta com a dia. Cada resultat es torna directament a la funció DATE. El resultat final és una data Excel adequada que es pot formatar com vulgueu.

Aquest enfocament es pot personalitzar segons sigui necessari. Per exemple, el format de data no reconegut de la fila 8 és dd.mm.yyyy i la fórmula de C8 és:

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

Text de forma llarga

De vegades és possible que tingueu dates més llargues com ara '11 d'abril de 2020 08:43:13' que Excel no reconeix correctament. En aquest cas, és possible que pugueu ajustar la cadena de manera que Excel permeti reconèixer correctament la data amb el fitxer Funció SUBSTITUT . La fórmula següent substitueix la segona instància d'un espai ('') per una coma i un espai (','):

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

Un cop afegim la coma després del nom del mes, Excel entendrà la data, però encara necessita una petita 'puntada'. És per això que al final afegim zero. L'operació matemàtica fa que Excel intenti convertir la cadena en un número. Quan tingui èxit, es traduirà en una data vàlida d'Excel. Tingueu en compte que potser haureu d’aplicar la data format de números per mostrar la data correctament.

Sense fórmules

Abans d'utilitzar una fórmula per analitzar i construir manualment una data a partir del text, proveu una de les correccions següents. La primera opció utilitza una operació matemàtica per 'empènyer' una mica l'Excel i forçar-lo a provar i avaluar el text com a número. Perquè Les dates d'Excel són de fet nombres , això sovint pot fer el truc. Pot ser que hagueu d'aplicar un format de data si les operacions tenen èxit.

Afegiu zero per fixar dates

De vegades, trobareu dates en un format de text que Excel hauria de reconèixer. En aquest cas, és possible que pugueu forçar Excel a convertir els valors del text en dates afegint zero al valor. Quan afegiu zero, Excel intentarà coaccionar els valors de text als números. Com que les dates són només números, aquest truc és una manera excel·lent de convertir les dates en format de text que Excel hauria de comprendre realment.

quina és la superfície de la trama a excel

Per convertir les dates en lloc afegint zero, proveu Enganxa especial :

com fer histograma a Excel
  1. Introduïu zero (0) en una cel·la no utilitzada i copieu-la al porta-retalls
  2. Seleccioneu les dates problemàtiques
  3. Enganxa Especial> Valors> Afegeix
  4. Apliqueu un format de data (si cal)

També podeu afegir zero en una fórmula com aquesta:

 
=A1+0

on A1 conté una data no reconeguda.

Envieu un text a les columnes per corregir les dates

Una altra manera d’aconseguir que Excel reconegui les dates és fer servir el fitxer Text a columnes Característica:

Seleccioneu la columna de dates i, a continuació, proveu Dades> Text a columnes> Corregit> Finalitza

Si Excel reconeix les dates, les arreglarà totes en un sol pas.

Autor Dave Bruns


^