Questo tutorial copre i diversi metodi per aggiungere o mantenere gli zeri iniziali e rimuovere gli zeri iniziali in Excel.
Ogni volta che si immettono o si importano numeri con uno o più zeri iniziali, come 000652, Excel rimuove automaticamente quegli zeri e nelle celle viene visualizzato solo il numero stesso (652). Questo perché gli zeri iniziali non sono necessari per i calcoli e non contano.
Tuttavia, ci sono momenti in cui sono necessari quegli zeri iniziali, come quando si immettono numeri ID, numeri di telefono, numeri di carta di credito, codici prodotto o codici postali, ecc. Fortunatamente, Excel ci offre diversi modi per aggiungere o mantenere gli zeri iniziali nelle cellule. In questo articolo, ti mostreremo i diversi modi per aggiungere o mantenere gli zeri iniziali e rimuovere gli zeri iniziali.
Aggiunta di zeri iniziali in Excel
Essenzialmente, ci sono 2 metodi che puoi usare per aggiungere zeri iniziali: uno, formatta il tuo numero come "Testo"; due, usa la formattazione personalizzata per aggiungere zeri iniziali. Il metodo che vuoi usare può dipendere da cosa vuoi fare con il numero.
Potresti voler aggiungere lo zero iniziale quando inserisci numeri ID univoci, numeri di conto, numeri di previdenza sociale o codici postali, ecc. Ma non utilizzerai questi numeri per calcoli o in funzioni, quindi è meglio convertirli numeri al testo. Non sommerai mai o farai la media di numeri di telefono o numeri di conto.
Esistono diversi modi per aggiungere o riempire gli zeri prima dei numeri formattandoli come testo:
- Modificare il formato della cella in Testo
- Aggiunta di un apostrofo (')
- Utilizzo della funzione TESTO
- Utilizzo della funzione REPT/LEN
- Usa funzione CONCATENATE/operatore commerciale (&)
- Utilizzo della funzione DESTRA
Modificare il formato della cella in testo
Questo è uno dei modi più semplici per aggiungere zeri iniziali ai tuoi numeri. Se hai intenzione di inserire solo numeri e vuoi mantenere gli zeri iniziali durante la digitazione, questo è il metodo che fa per te. Modificando il formato della cella da Generale o Numero a Testo, puoi forzare Excel a trattare i tuoi numeri come valori di testo e tutto ciò che digiti nella cella rimarrà esattamente lo stesso. Ecco come lo fai:
Seleziona le celle in cui desideri aggiungere gli zeri iniziali. Vai alla scheda "Home", fai clic sulla casella a discesa "Formato" nel gruppo Numeri e seleziona "Testo" dalle opzioni di formato.
Ora quando digiti i tuoi numeri, Excel non eliminerà nessuno zero iniziale da esso.
Potresti vedere un piccolo triangolo verde (indicatore di errore) nell'angolo in alto a sinistra della cella e quando selezioni quella cella, ti mostrerà un segnale di avvertimento che indica che hai memorizzato il numero come testo.
Per rimuovere il messaggio di errore, seleziona le celle, fai clic sul segnale di avviso, quindi seleziona "Ignora errore" dall'elenco.
Puoi anche digitare numeri di telefono con uno spazio o un trattino tra i numeri, Excel tratterà automaticamente questi numeri come testo.
Usando Leading Apostrofo ( ' )
Un altro modo per aggiungere zeri iniziali in Excel è aggiungere un apostrofo (') all'inizio del numero. Ciò costringerà Excel a inserire un numero come testo.
Basta digitare un apostrofo prima di qualsiasi numero e premere "Invio". Excel lascerà intatti gli zeri iniziali, ma il (') non sarà visibile nel foglio di lavoro, a meno che non si selezioni la cella.
Utilizzo della funzione di testo
Il metodo sopra aggiunge zeri ai numeri mentre li digiti, ma se hai già un elenco di numeri e vuoi riempire gli zeri iniziali prima di essi, allora la funzione TESTO è il metodo giusto per te. La funzione TESTO consente di convertire i numeri in stringhe di testo durante l'applicazione della formattazione personalizzata.
La sintassi della funzione TESTO:
= TESTO( valore, formato_testo)Dove,
- valore - È il valore numerico che devi convertire in testo e applicare la formattazione.
- format_text – è il formato che si desidera applicare.
Con la funzione TESTO, puoi specificare quante cifre deve avere il tuo numero. Ad esempio, se vuoi che i tuoi numeri siano lunghi 8 cifre, digita 8 zeri nel secondo argomento della funzione: "00000000". Se hai un numero di 6 cifre in una cella, la funzione aggiungerà manualmente 2 zeri iniziali e se hai numeri di 2 cifre come 56, il resto sarà zero (00000056).
Ad esempio, per aggiungere zeri iniziali e rendere i numeri lunghi 6 cifre, usa questa formula:
=TESTO(A2,"000000")Poiché abbiamo 6 zeri nel secondo argomento della formula, la funzione converte la stringa numerica in una stringa di testo e aggiunge 5 zeri iniziali per rendere la stringa lunga 6 cifre.
Nota: Ricordarsi di racchiudere i codici formato tra virgolette doppie nella funzione.
Ora puoi applicare la stessa formula al resto delle celle trascinando la maniglia di riempimento. Come puoi vedere, la funzione converte i numeri in testi e aggiunge zeri iniziali ai numeri in modo che il numero totale di cifre sia 6.
La funzione TEXT restituirà sempre il valore come stringa di testo, non come numero, quindi non sarai in grado di usarli nei calcoli aritmetici ma puoi comunque usarli nelle formule di ricerca come VLOOKUP o INDEX/MATCH per recuperare i dettagli di un prodotto utilizzando gli ID prodotto.
Utilizzo della funzione CONCATENATE/operatore e commerciale (&)
Se si desidera aggiungere un numero fisso di zeri iniziali prima di tutti i numeri in una colonna, è possibile utilizzare la funzione CONCATENATE o l'operatore e commerciale (&).
Sintassi della funzione CONCATENA:
=CONCATENA(testo1, [testo2], ...)Dove,
Testo 1 - Il numero di zeri da inserire prima del numero.
testo2 – Il numero originale o il riferimento di cella
Sintassi dell'operatore e commerciale:
=Valore_1 & Valore_2 Dove,
Valore_1 sono gli zeri iniziali da inserire prima del numero e Valore_2 è il numero.
Ad esempio, per aggiungere solo due zeri prima di un numero, utilizzare una di queste formule:
=CONCATENA("00",A2)Il primo argomento è due zeri ("00") perché vogliamo riempire due zeri prima del numero in A2 (che è il secondo argomento).
O,
="00"&A2Qui, il primo argomento è 2 zeri, seguito dall'operatore "&" e il secondo argomento è il numero.
Come puoi vedere, la formula aggiunge solo due zeri iniziali a tutti i numeri in una colonna indipendentemente da quante cifre contiene il numero.
Entrambe queste formule uniscono un certo numero di zeri prima dei numeri originali e li memorizzano come stringhe di testo.
Utilizzo della funzione REPT/LEN
Se vuoi aggiungere zeri iniziali a dati numerici o alfanumerici e convertire la stringa in testo, usa la funzione RIPETI. La funzione REPT viene utilizzata per ripetere uno o più caratteri un numero specifico di volte. Questa funzione può essere utilizzata anche per inserire numeri fissi di zeri iniziali prima del numero.
=RIPETI(testo, numero_volte)Dove "testo" è il carattere che vogliamo ripetere (nel nostro caso "0") e l'argomento "numero_volte" è il numero di volte che vogliamo ripetere quel carattere.
Ad esempio, per generare cinque zeri prima dei numeri, la formula sarà simile a:
=REPT(0,5)&A2Ciò che fa la formula è ripetere 5 zeri e unire la stringa numerica in A2 e restituire il risultato. Quindi, la formula viene applicata alla cella B2: B6 utilizzando il quadratino di riempimento.
La formula precedente aggiunge un numero fisso di zeri prima del numero, ma la lunghezza totale del numero varia a seconda del numero.
Se si desidera aggiungere zeri iniziali ovunque sia necessario per creare stringhe di caratteri lunghi (lunghezza fissa), è possibile utilizzare le funzioni RIPETI e LUNGHEZZA insieme.
Sintassi:
=REPT(testo, numero_volte-LEN(testo))&cellaAd esempio, per aggiungere zeri prefissati al valore in A2 e creare una stringa lunga 5 caratteri, prova questa formula:
=REPT(0,5-LEN(A2))&A2Qui, 'LEN(A2)' ottiene la lunghezza totale della stringa/numeri nella cella A2. '5' è la lunghezza massima della stringa/numeri che la cella dovrebbe avere. E la parte 'REPT(0,5-LEN(A2))' aggiunge il numero di zeri sottraendo la lunghezza della stringa in A2 dal numero massimo di zeri (5). Quindi, un numero di 0 viene unito prima del valore di A2 per creare una stringa di lunghezza fissa.
Utilizzo della funzione DESTRA
Un altro modo per riempire gli zeri iniziali prima di una stringa in Excel è utilizzare la funzione DESTRA.
La funzione RIGHT può aggiungere un numero di zeri all'inizio di un numero ed estrarre gli N caratteri più a destra dal valore.
Sintassi:
= DESTRA (testo, num_caratteri)- testo è la cella o il valore da cui si desidera estrarre i caratteri.
- num_chars è il numero di caratteri da estrarre dal testo. Se questo argomento non viene fornito, verrà estratto solo il primo carattere.
Per questo metodo, concateniamo il numero massimo di zeri con il riferimento di cella che contiene la stringa nell'argomento "testo".
Per creare un numero di 6 cifre basato sulla stringa numerica in A con zeri iniziali, prova questa formula:
=DESTRA("0000000"&A2,6)Il primo argomento (testo) della formula aggiunge 7 zeri al valore in A2 ("0000000" e A2), quindi restituisce i 7 caratteri più a destra, che risultano in alcuni zeri iniziali.
Aggiunta di zeri iniziali utilizzando la formattazione dei numeri personalizzata
Se utilizzi uno dei metodi precedenti per inserire gli zeri prima dei numeri, otterrai sempre una stringa di testo, non un numero. E non saranno molto utili nei calcoli o nelle formule numeriche.
Il modo migliore per aggiungere zeri iniziali in Excel è applicare una formattazione numerica personalizzata. Se aggiungi zeri iniziali aggiungendo un formato numerico personalizzato alla cella, non cambia il valore della cella ma solo il modo in cui viene visualizzato. Il valore rimarrà comunque un numero, non un testo.
Per modificare la formattazione del numero delle celle, procedi nel seguente modo:
Seleziona la cella o l'intervallo di celle in cui desideri visualizzare gli zeri iniziali. Quindi, fai clic con il pulsante destro del mouse in qualsiasi punto dell'intervallo selezionato e seleziona l'opzione "Formatta celle" dal menu di scelta rapida. Oppure premi i tasti di scelta rapida Ctrl + 1.
Nella finestra Formato celle, vai alla scheda "Numero" e seleziona "Personalizzato" sotto le opzioni della categoria.
Immettere il numero di zeri nella casella "Tipo:" per specificare il numero totale di cifre che si desidera visualizzare in una cella. Ad esempio, se desideri che il numero sia lungo 6 cifre, inserisci "000000" come codice del formato personalizzato. Quindi, fare clic su "OK" per applicare.
Questo mostrerà gli zeri iniziali prima dei numeri e se il numero è inferiore a 6 cifre, lo precede lo zero.
I numeri sembreranno avere solo zeri iniziali mentre il valore sottostante rimarrebbe invariato. Se selezioni una cella con formattazione personalizzata, ti mostrerà il numero originale nella barra della formula
Esistono molti segnaposto digitali che puoi utilizzare nel tuo formato numerico personalizzato. Ma ci sono solo due segnaposto primari che puoi usare per aggiungere zeri iniziali nei numeri.
- 0 – È il segnaposto della cifra che mostra gli zeri extra. Visualizza le cifre forzate 0-9 indipendentemente dal fatto che la cifra sia rilevante per il valore. Ad esempio, se si digita 2.5 con il codice di formato 000.00, verrà visualizzato 002.50.
- # – È il segnaposto della cifra che visualizza cifre facoltative e non include zeri aggiuntivi. Ad esempio, se si digita 123 con il codice di formattazione 000#, verrà visualizzato 0123.
Inoltre, qualsiasi segno di punteggiatura o altro carattere che includi nel codice del formato verrà visualizzato così com'è. Puoi usare caratteri come trattino (-), virgola (,), barra (/), ecc.
Ad esempio, puoi anche formattare i numeri come numeri di telefono utilizzando il formato personalizzato.
La finestra di dialogo Formato codice nella finestra di dialogo Formato celle:
Il risultato:
Applichiamo questo codice di formattazione nel seguente esempio:
##0000
Come puoi vedere, "0" aggiunge zeri extra mentre "#" non aggiunge zeri insignificanti:
È inoltre possibile utilizzare codici formato predefiniti nella sezione "Formati speciali" della finestra di dialogo Formato celle per codici postali, numeri di telefono e codici fiscali.
La tabella seguente mostra i numeri con zeri iniziali in cui vengono applicati codici di formato "Speciale" diversi a colonne diverse:
Rimozione degli zeri iniziali in Excel
Ora, hai imparato come aggiungere zeri iniziali in Excel, vediamo come rimuovere gli zeri iniziali dal numero di stringhe. A volte, quando si importano dati da una fonte esterna, i numeri potrebbero finire per avere degli zeri di prefisso e essere formattati come testo. In questi casi, è necessario rimuovere gli zeri iniziali e riconvertirli in numeri, in modo da poterli utilizzare nelle formule.
Esistono vari modi per rimuovere gli zeri iniziali in Excel e li vedremo uno per uno.
Rimuovi gli zeri iniziali modificando la formattazione della cella
Se gli zeri iniziali sono stati aggiunti mediante la formattazione dei numeri personalizzata, è possibile rimuoverli facilmente modificando il formato delle celle. Puoi sapere se le tue celle sono formattate in modo personalizzato guardando la barra degli indirizzi (gli zeri saranno visibili nella cella non nella barra degli indirizzi).
Per rimuovere gli zeri prefissati, seleziona le celle con gli zeri iniziali, fai clic sulla casella "Formato numero" e seleziona l'opzione di formattazione "Generale" o "Numero".
Ora, gli zeri iniziali sono spariti:
Elimina gli zeri iniziali convertendo il testo in numeri
Se gli zeri iniziali sono stati aggiunti modificando il formato della cella o aggiungendo apostrofi prima dei numeri o aggiunti automaticamente durante l'importazione dei dati, il modo più semplice per convertirli in numeri è utilizzare l'opzione di controllo degli errori. Ecco come lo fai:
Puoi usare questo metodo se i tuoi numeri sono allineati a sinistra e le tue celle hanno un piccolo triangolo verde (Un indicatore di errore) nell'angolo in alto a sinistra delle celle. Ciò significa che i numeri sono formattati come testo.
Seleziona quelle celle e vedrai un avviso giallo nella parte in alto a destra della selezione. Quindi, fai clic sull'opzione "Converti in numero" dal menu a discesa.
I tuoi zeri verranno rimossi e i numeri verranno riconvertiti nel formato numerico (allineato a destra).
Rimozione degli zeri iniziali di Moltiplica/Dividi per 1
Un altro modo semplice e migliore per rimuovere l'interlinea è moltiplicare o dividere i numeri per 1. Dividere o moltiplicare il valore non cambia il valore, ma semplicemente riconverte il valore in un numero e rimuove gli zeri iniziali.
Per fare ciò, digitare la formula nell'esempio seguente in una cella e premere INVIO. Gli zeri iniziali verranno rimossi e la stringa verrà riconvertita in un numero.
Quindi, applica questa formula ad altre celle usando il quadratino di riempimento.
Puoi ottenere gli stessi risultati usando il comando "Incolla speciale". Ecco come:
Digita il valore numerico "1" in una cella (diciamo in B2) e copia quel valore.
Quindi, seleziona le celle in cui desideri rimuovere gli zeri iniziali. Quindi, fare clic con il pulsante destro del mouse sulla selezione e quindi selezionare l'opzione "Incolla speciale".
Nella finestra di dialogo Incolla speciale, in Operazione, scegliere l'opzione "Moltiplica" o "Dividi" e fare clic su "OK".
Ecco fatto, i tuoi zeri iniziali verranno rimossi lasciando le stringhe come numeri.
Rimuovi gli zeri iniziali usando le formule
Un altro modo semplice per eliminare gli zeri prefissati consiste nell'utilizzare la funzione VALUE. Questo metodo potrebbe essere utile se gli zeri iniziali sono stati aggiunti utilizzando un'altra formula o apostrofo o una formattazione personalizzata.
=VALORE(A1)L'argomento della formula potrebbe essere un valore o il riferimento di cella che ha il valore. La formula rimuove gli zeri iniziali e converte il valore da testo a numero. Quindi applica la formula al resto delle celle.
A volte, potresti voler rimuovere gli zeri iniziali ma voler mantenere i numeri nel formato testo. In questi casi, devi usare le funzioni TEXT() e VALUE() insieme in questo modo:
=TESTO(VALORE(A1),"#")La funzione VALORE converte il valore in A1 in numero. Ma il secondo argomento, '#' converte il valore in formato testo senza zeri aggiuntivi. Di conseguenza, otterresti i numeri senza zeri iniziali ma ancora nel formato testo (allineato a sinistra).
Rimuovi gli zeri iniziali utilizzando la funzione Text to Columns di Excel
Un altro modo per rimuovere gli zeri iniziali è utilizzare la funzione Testo su colonne di Excel.
Seleziona l'intervallo di celle che hanno i numeri con gli zeri iniziali.
Quindi, vai alla scheda "Dati" e fai clic sul pulsante "Testo in colonne" nel gruppo Strumenti dati.
Verrà visualizzata la procedura guidata "Converti testo in colonne". Nel passaggio 1 di 3, seleziona "Delimitato" e fai clic su "Avanti".
Nel passaggio 2 di 3, deseleziona tutti i delimitatori e fai clic su "Avanti".
Nel passaggio finale, lascia l'opzione Formato dati colonna su "Generale" e scegli la destinazione (la prima cella dell'intervallo) in cui desideri i tuoi numeri senza zeri iniziali. Quindi, fai clic su "Fine"
E otterrai i numeri con l'interlinea rimosso in una colonna separata come mostrato di seguito.
Questo è tutto.