Esistono molti modi per convertire le date formattate come testo in date effettive in Excel e questo tutorial mira a esplorarle tutte.
Quando importi dati in Excel, potrebbero presentarsi in molte forme diverse che Excel non riconosce. A volte, quando le date vengono importate da una fonte esterna, verranno formattate come testo.
È anche importante sapere che Excel applica i formati di data in base alle impostazioni dell'area di sistema. Pertanto, quando si importano dati in Excel provenienti da un paese diverso, Excel potrebbe non riconoscerli e memorizzarli come voci di testo.
Se hai questo problema, ci sono molti modi in cui puoi usare per convertire il testo in una data in Excel e li tratteremo tutti in questo tutorial.
Metodi per convertire il testo in data
Se il foglio di lavoro contiene date formattate come testo, anziché date effettive, non è possibile utilizzarle in alcun calcolo. Quindi, è necessario riconvertirli in date effettive.
Se vedi le tue date allineate a sinistra significa che sono formattate come testo perché i testi sono allineati a sinistra per impostazione predefinita. E il numero e le date sono sempre allineati a destra.
Esistono diversi modi per convertire il testo in una data in Excel, sono:
- Opzione di controllo degli errori
- Funzione Excel da testo a colonne
- Trova e sostituisci
- Strumento speciale Incolla
- Formula e funzioni di Excel
Converti testo in data utilizzando l'opzione di controllo degli errori
Excel ha una funzione di controllo degli errori incorporata che individua alcuni errori evidenti nei dati. Se trova un errore, ti mostrerà un piccolo triangolo verde (un indicatore di errore) nell'angolo in alto a sinistra della cella, che ha l'errore. Se selezioni quella cella, apparirà un segnale di attenzione con un punto esclamativo giallo. Quando sposti il cursore su quel segno, Excel ti informerà del possibile problema con quella cella.
Ad esempio, quando si immette l'anno nel formato a due cifre nella data, Excel assume tale data come testo e la memorizza come testo. E se selezioni quella cella, apparirà un punto esclamativo con un avviso: "Questa cella contiene una stringa di data rappresentata con solo 2 cifre dell'anno".
Se le tue celle mostrano quell'indicatore di errore, fai clic sul punto esclamativo e verranno visualizzate alcune opzioni per convertire le date formattate come testo in date effettive. Excel ti mostrerà le opzioni per convertirlo in 19XX o 20XX (19XX per 1915, 20XX per 2015). Seleziona l'opzione appropriata.
Quindi, il testo verrà convertito nel formato di data corretto.
Come abilitare l'opzione di controllo degli errori in Excel
Di solito, l'opzione Controllo errori è attivata in Excel per impostazione predefinita. Se la funzione di controllo degli errori non funziona per te, devi abilitare il controllo degli errori in Excel.
Per fare ciò, fai clic sulla scheda "File" e seleziona "Opzioni" nel pannello di sinistra.
Nella finestra Opzioni di Excel, fai clic su "Formule" nel pannello di sinistra e, nel pannello di destra, abilita "Abilita controllo errori in background" nella sezione Controllo errori. E spunta le "Celle contenenti anni rappresentati da 2 cifre" nella sezione Regole di controllo degli errori.
Converti testo in data utilizzando la funzione Testo in colonna di Excel
Text to Column è un'ottima funzionalità in Excel che ti consente di dividere i dati in più colonne ed è anche un potente strumento per convertire i valori di testo in valori di data. Questo metodo riconosce diversi formati di dati e li converte nel formato di data corretto.
Conversione di semplici stringhe di testo in date
Supponiamo che le tue date siano formattate in stringhe di testo come questa:
Puoi utilizzare la procedura guidata Testo su colonne per riformattarli rapidamente tutti alle date.
Innanzitutto, seleziona l'intervallo di voci di testo che desideri convertire in date. Quindi, vai alla scheda "Dati" nella barra multifunzione e fai clic sull'opzione "Testo in colonne" nel gruppo Strumenti dati.
Verrà visualizzata la procedura guidata Testo su colonne. Al passaggio 1 della procedura guidata Testo su colonna, scegli l'opzione "Delimitato" in Tipo di dati originale e fai clic su "Avanti".
Nel passaggio 2, deseleziona tutte le caselle "Delimitatori" e fai clic su Avanti.
Nella fase finale della procedura guidata, seleziona "Data" in Formato dati colonna e scegli il formato della data dall'elenco a discesa accanto a "Date" e fai clic sul pulsante "Fine". Nel nostro caso, stiamo convertendo le date di testo rappresentate come "01 02 1995" (giorno mese anno), quindi scegliamo "GMA" dall'elenco a discesa "Data:".
Ora, Excel converte le date del testo in date effettive e le visualizza allineate a destra nelle celle.
Nota: Prima di iniziare a utilizzare la funzione Testo su colonna, assicurati che tutte le stringhe di testo siano in un formato identico, altrimenti i testi non verranno convertiti. Ad esempio, se alcune delle tue date di testo sono formattate come il formato mese/giorno/anno (GMA) mentre altre sono giorno/mese/anno (GMA), e quando scegli "GMA" nel passaggio 3, otterresti risultati errati. Come è mostrato nell'immagine qui sotto.
Conversione di stringhe di testo complesse in data
La funzione Testo in colonne è utile quando si desidera convertire stringhe di testo complesse in date. Ti consente di utilizzare i delimitatori per identificare dove i tuoi dati devono essere suddivisi e visualizzati su 2 o più colonne. E combina le parti divise delle date in tutta la data usando la funzione DATE.
Ad esempio, se le tue date vengono visualizzate in stringhe di testo in più parti, in questo modo:
mercoledì 01 febbraio 2020
01 febbraio 2020, 16:10
È possibile utilizzare la procedura guidata Testo su colonna per separare le informazioni su giorno, data e ora delimitate da una virgola e visualizzarle su più colonne.
Innanzitutto, seleziona tutte le stringhe di testo che desideri convertire in date. Fare clic sul pulsante "Testo in colonne" nella scheda "Dati". Al passaggio 1 della procedura guidata Testo su colonna, scegli l'opzione "Delimitato" in Tipo di dati originale e fai clic su "Avanti".
Nel passaggio 2 della procedura guidata, seleziona i delimitatori contenuti nelle stringhe di testo e fai clic su "Avanti". Le nostre stringhe di testo di esempio separate da virgola e spazio - "Lunedì 1 febbraio 2015, 13:00". Dovremmo scegliere "Virgola" e "Spazi" come delimitatori per dividere le stringhe di testo in più colonne.
Nel passaggio finale, seleziona il formato "Generale" per tutte le colonne nella sezione Anteprima dati. Specifica dove devono essere inserite le colonne nel campo "Destinazione", in caso contrario, sovrascriverà i dati originali. Se desideri ignorare una parte dei dati originali, fai clic su di essa nella sezione Anteprima dati e seleziona l'opzione "Non importare la colonna (salta)". Quindi, fai clic su "Fine".
Ora, le parti delle date (giorni della settimana, mese, anno, ora) sono divise nelle colonne B, C, D, E, F e G.
Quindi, combina le parti della data insieme con l'aiuto di una formula DATE per ottenere l'intera data.
La sintassi della funzione DATA di Excel:
=DATA(anno,mese,giorno)Nel nostro esempio, le parti del mese, del giorno e dell'anno si trovano rispettivamente nelle colonne C, D ed E.
La funzione DATA riconosce solo i numeri, non il testo. Poiché i nostri valori mensili nella colonna C sono tutte stringhe di testo, dobbiamo convertirli in numeri. Per fare ciò, è necessario utilizzare la funzione MESE per modificare il nome del mese con il numero del mese.
Per convertire il nome di un mese nel numero di un mese, usa questa funzione MESE all'interno della funzione DATA:
=MESE(1&C1)La funzione MESE aggiunge 1 alla cella C2 che contiene il nome del mese per convertire il nome del mese nel numero del mese corrispondente.
Questa è la funzione DATA che dobbiamo usare per combinare parti della data da colonne diverse:
=DATA(E1,MESE(1&C1),D1)
Ora usa il quadratino di riempimento nell'angolo inferiore della cella della formula e applica la formula alla colonna.
Converti testo in data utilizzando il metodo Trova e sostituisci
Questo metodo utilizza i delimitatori per modificare il formato del testo in date. Se il giorno, il mese e l'anno nelle date sono separati da un delimitatore diverso da un trattino (-) o una barra (/), Excel non li riconoscerà come date e li memorizzerà come testo.
Per risolvere questo problema, utilizza la funzione Trova e sostituisci. Modificando i delimitatori di periodo non standard (.) con barre (/) o trattini (-), Excel cambierà automaticamente i valori in date.
Innanzitutto, seleziona tutte le date di testo che desideri convertire in date. Nella scheda "Home", fai clic sul pulsante "Trova e seleziona" nell'angolo più a destra della barra multifunzione e seleziona "Sostituisci". In alternativa, premere Ctrl+H per aprire la finestra di dialogo Trova e sostituisci.
Nella finestra di dialogo Trova e sostituisci, digita il delimitatore che contiene il tuo testo (nel nostro caso punto (.) nel campo "Trova cosa" e una barra (/) o un trattino (-) nel campo "Sostituisci con" Fare clic sul pulsante "Sostituisci tutto" per sostituire i delimitatori e fare clic su "Chiudi" per chiudere la finestra.
Ora, Excel riconosce che le stringhe di testo sono ora date e le formatta automaticamente come date. Le tue date saranno allineate a destra come mostrato di seguito.
Converti testo in data utilizzando lo strumento speciale Incolla
Un altro modo semplice e veloce per convertire le stringhe di testo in date è aggiungere 0 alla stringa di testo utilizzando l'opzione incolla speciale. L'aggiunta di zero al valore converte il testo nel numero di serie della data che è possibile formattare come data.
Per prima cosa, seleziona una cella vuota e copiala (selezionala e premi Ctrl + C copiare).
Quindi, seleziona le celle contenenti le date di testo che desideri convertire, fai clic con il pulsante destro del mouse e seleziona l'opzione "Incolla speciale".
Nella finestra di dialogo Incolla speciale, seleziona "Tutto" nella sezione Incolla, scegli "Aggiungi" nella sezione Operazione e fai clic su "OK".
Puoi anche eseguire altre operazioni aritmetiche sottrarre/moltiplicare/dividere il valore nella cella di destinazione con il valore incollato (come moltiplicare le celle con 1 o dividere per 1 o sottrarre zero).
Quando selezioni "Aggiungi" in Operazione, aggiunge "zero" a tutte le date di testo selezionate, poiché l'aggiunta di uno "0" non cambia i valori, otterrai il numero di serie per ogni data. Ora tutto ciò che devi fare è cambiare il formato delle celle.
Selezionare i numeri di serie e nella scheda "Home", fare clic sull'elenco a discesa "Formato numero" nel gruppo Numero. Seleziona l'opzione "Data breve" dal menu a discesa.
Come puoi vedere ora i numeri sono formattati come date e allineati a destra.
Converti testo in data usando le formule
Esistono due funzioni principalmente utilizzate per convertire il testo in data: DATEVALUE e VALUE.
Utilizzo della funzione DATA.VALORE di Excel
La funzione DATAVALORE di Excel è uno dei modi più semplici per convertire una data rappresentata come testo in un numero seriale di una data.
La sintassi della funzione DATA.VALORE:
=DATA.VALORE(data_testo)Discussione: data_testo specifica la stringa di testo che si desidera nascondere o fare riferimento alla cella contenente le date di testo.
La formula:
=DATAVALORE(A1)L'immagine seguente illustra come la funzione DATEVALUE gestisce alcuni formati di data diversi che vengono archiviati come testo.
Hai i numeri di serie della data, ora devi applicare il formato della data a questi numeri. Per farlo, seleziona le celle con i numeri di serie, quindi vai alla scheda "Home" e scegli "Data breve" dall'elenco a discesa "Formato numero".
Ora hai le tue date formattate nella colonna C.
Anche se non c'è una parte dell'anno nella tua data di testo (A8), DATEVALUE utilizzerà l'anno corrente dall'orologio del tuo computer.
La funzione DATEVALUE convertirà solo i valori di testo che assomigliano a una data. Non può convertire un testo che assomiglia a un numero fino ad oggi, né può modificare un valore numerico fino ad oggi, per questo avrai bisogno della funzione VALORE di Excel.
Utilizzo della funzione VALORE di Excel
La funzione VALORE di Excel è in grado di convertire qualsiasi stringa di testo che assomigli a una data o un numero in un valore numerico, quindi è davvero utile quando si tratta di convertire qualsiasi numero, non solo le date.
La funzione VALORE:
=VALORE(testo)testo– la stringa di testo che vogliamo convertire o fare riferimento alla cella contenente la stringa di testo.
La formula di esempio per convertire la data del testo:
=VALORE(A1)La formula VALUE seguente può modificare qualsiasi stringa di testo che assomiglia a una data in un numero come mostrato di seguito.
Tuttavia, la funzione VALUE non supporta tutti i tipi di valori di data. Ad esempio, se le date utilizzano posizioni decimali (A11), restituirà il #VALORE! errore.
Una volta che hai il numero di serie per la tua data, dovrai formattare la cella con il numero di serie della data per farlo sembrare una data come abbiamo fatto per la funzione DATA.VALORE. Per farlo, seleziona i numeri di serie e seleziona l'opzione "Data" dal menu a discesa "Formato numero" nella scheda "Home".
Ecco, questi sono i 5 diversi modi in cui puoi convertire le date formattate come testo in date in Excel.