Questo tutorial mostra diversi modi per aggiungere zeri iniziali in Excel: come mantenere gli zeri durante la digitazione, mostrare zeri iniziali nelle celle, rimuovere o nascondere gli zeri.
Se si utilizza Excel non solo per calcolare i numeri, ma anche per mantenere record come codici postali, numeri di sicurezza o ID dipendenti, potrebbe essere necessario mantenere gli zeri iniziali nelle celle. Tuttavia, se si tenta di digitare un codice postale come ” 00123 “in una cella, Excel lo troncerà immediatamente a”123”.,
Il punto è che Microsoft Excel considera i codici postali, i numeri di telefono e altre voci simili come numeri, applica il formato Generale o numerico e rimuove automaticamente gli zeri precedenti. Fortunatamente, Excel fornisce anche i mezzi per mantenere gli zeri iniziali nelle celle, e più avanti in questo tutorial troverai una manciata di modi per farlo.,
- Tenere gli zeri iniziali in Excel come tipo
- Mostra zeri iniziali utilizzando un formato personalizzato
- Aggiungi gli zeri iniziali per i numeri (funzione di TESTO)
- Pad stringhe di testo con zeri
- Aggiungere un numero fisso di precedenti zeri
- Rimuovere gli zeri iniziali in Excel
- Nascondi zero i valori in Excel
Come mantenere gli zeri iniziali in Excel come tipo
Per cominciare, andiamo a vedere come si può mettere 0 a fronte di un numero in Excel, per esempio tipo 01 in una cella., Per questo, è sufficiente modificare il formato della cella in Testo:
- Selezionare le celle in cui si desidera prefisso numeri con 0.
- Vai alla scheda Home > Gruppo di numeri e seleziona Testo nella casella Formato numero.
Non appena si digita uno zero(s) prima del numero, Excel visualizzerà un piccolo triangolo verde nell’angolo in alto a sinistra della cella che indica che qualcosa non va nel contenuto della cella. Per rimuovere tale indicatore di errore, selezionare le celle, fare clic sul segnale di avviso e quindi fare clic su Ignora errore.,
La seguente schermata mostra il risultato:
Un altro modo per mantenere gli zeri iniziali in Excel è quello di anteporre un numero con un apostrofo (‘). Ad esempio, invece di digitare 01, digitare ’01. In questo caso, non è necessario modificare il formato della cella.
Bottom line: Questa semplice tecnica ha una limitazione significativa: il valore risultante è una stringa di testo, non un numero, e di conseguenza non può essere utilizzato nei calcoli e nelle formule numeriche., Se questo non è ciò che si desidera, modificare solo la rappresentazione visiva del valore applicando un formato numerico personalizzato come dimostrato nell’esempio successivo.
Come mostrare gli zeri iniziali in Excel utilizzando un formato numerico personalizzato
Per visualizzare gli zeri iniziali, applicare un formato numerico personalizzato eseguendo questi passaggi:
- Selezionare una cella in cui si desidera mostrare gli zeri iniziali e premere Ctrl+1 per aprire la finestra di dialogo Formato celle.
- In Categoria, selezionare Personalizzato.
- Digitare un codice di formato nella casella Tipo.,
Nella maggior parte dei casi, è necessario un codice di formato composto da 0 segnaposto, come 00. Il numero di zeri nel codice formato corrisponde al numero totale di cifre che si desidera visualizzare in una cella (troverete alcuni esempi di seguito).
- Fare clic su OK per salvare le modifiche.
Ad esempio, per aggiungere zeri iniziali per creare un numero a 5 cifre, utilizzare il seguente codice di formato: 00000
Utilizzando i formati di numeri personalizzati di Excel, è possibile aggiungere zeri iniziali per creare numeri a lunghezza fissa, come nell’esempio precedente, e numeri a lunghezza variabile., Tutto si riduce a quale segnaposto si utilizza nel codice del formato:
- 0 – visualizza zeri extra
- # – non visualizza zeri extra
Ad esempio, se si applica il formato 000# a qualche cella, qualsiasi numero digitato in quella cella avrà fino a 3 zeri iniziali.
I formati numerici personalizzati possono includere anche spazi, trattini, parentesi, ecc. La spiegazione dettagliata può essere trovata qui: Formato numero Excel personalizzato.
Il seguente foglio di calcolo fornisce alcuni altri esempi di formati personalizzati che possono mostrare zeri iniziali in Excel.,
E i seguenti codici di formato possono essere utilizzati per visualizzare i numeri in formati speciali come codici postali, numeri di telefono, numeri di carta di credito e numeri di previdenza sociale.,
Linea di fondo: Questo metodo è meglio essere utilizzato in situazioni in cui si lavora con un set di dati numerici e i risultati dovrebbero essere numeri, non testo. Cambia solo la visualizzazione di un numero, ma non il numero stesso: gli zeri iniziali vengono visualizzati nelle celle, il valore effettivo viene visualizzato nella barra della formula. Quando si fa riferimento a tali celle nelle formule, i calcoli vengono profumati con i valori originali., I formati personalizzati possono essere applicati solo ai dati numerici (numeri e date) e il risultato è anche un numero o una data.
Come aggiungere zeri iniziali in Excel con la funzione di TESTO
Mentre un formato numerico personalizzato mostra lo zero di fronte a un numero senza effettivamente modificare il valore sottostante, la funzione di TESTO di Excel riempie i numeri con zeri inserendo “fisicamente” zeri iniziali nelle celle.
Per aggiungere zeri iniziali con una formula TEXT(value, format_text), si utilizzano gli stessi codici di formato dei formati numerici personalizzati., Tuttavia, il risultato della funzione TEXT è sempre una stringa di testo, anche se assomiglia molto a un numero.
Per esempio, per inserire 0 prima di un valore nella cella A2, utilizzare questa formula:
=TEXT(A2, "0#")
Per creare una zero-il prefisso stringa di lunghezza fissa, diciamo un 5-stringa di caratteri, uso questo:
=TEXT(A2, "000000")
si Prega di prestare attenzione che la funzione di TESTO richiede che racchiude i codici di formato tra virgolette.,ike in Excel:
| A | B | C | |
|---|---|---|---|
| 1 | Original number | Padded number | Formula |
| 2 | 1 | 01 | =TEXT(B2, “0#”) |
| 3 | 12 | 12 | =TEXT(B3, “0#”) |
| 4 | 1 | 00001 | =TEXT(B4,”00000″) |
| 5 | 12 | 00012 | =TEXT(B5,”00000″) |
For more information about Text formulas, please see How to use the TEXT function in Excel.,
Bottom line: La funzione di TESTO Excel restituisce sempre una stringa di testo, non un numero, e quindi non sarà possibile utilizzare i risultati in calcoli aritmetici e altre formule, a meno che non sia necessario confrontare l’output con altre stringhe di testo.
Come aggiungere zeri iniziali alle stringhe di testo
Negli esempi precedenti, hai imparato come aggiungere zero prima di un numero in Excel. Ma cosa succede se hai bisogno di mettere zero (s) davanti a una stringa di testo come 0A102? In tal caso, né il TESTO né il formato personalizzato funzioneranno perché si occupano solo di valori numerici.,
Se il valore da riempire con zero contiene lettere o altri caratteri di testo, utilizzare una delle seguenti formule, che offrono una soluzione universale applicabile sia ai numeri che alle stringhe di testo.
Formula 1. Aggiungi zeri iniziali usando la funzione GIUSTA
Il modo più semplice per mettere zeri iniziali prima delle stringhe di testo in Excel sta usando la funzione GIUSTA:
Dove:
- “0000” è il numero massimo di zeri che si desidera aggiungere. Ad esempio, per aggiungere 2 zeri, si digita “00”.,la cella
- è un riferimento alla cella contenente il valore originale.
- String_length è il numero di caratteri che la stringa risultante deve contenere.
Ad esempio, per creare una stringa di 6 caratteri con prefisso zero basata su un valore nella cella A2, utilizzare questa formula:
=RIGHT("000000"&A2, 6)
Ciò che fa la formula è aggiungere 6 zeri al valore in A2 (“000000″&A2), e quindi estrarre il diritto 6 caratteri., Come risultato, si inserisce il giusto numero di zeri per raggiungere il totale specificata stringa limite:
Nell’esempio precedente, il numero massimo di zeri è uguale alla lunghezza della stringa (6 caratteri), e di conseguenza a tutti risultante stringhe sono 6 caratteri (lunghezza fissa). Se applicata a una cella vuota, la formula restituirebbe una stringa composta da 6 zeri.,
A seconda della logica aziendale, è possibile fornire diversi numeri di zeri e caratteri totali, ad esempio:
=RIGHT("00"&A2, 6)
Di conseguenza, si otterranno stringhe di lunghezza variabile che contengono fino a 2 zeri iniziali:
Formula 2.,ore una stringa di testo in Excel utilizzando questa combinazione di REPT e LEN funzioni:
RIPETI(0, numero di zeri-LEN(cella))&cella
Per esempio, per aggiungere zeri iniziali per il valore in A2 per creare un 6-stringa di caratteri, questa formula va come segue:
=REPT(0, 6-LEN(A2))&A2
Come questa formula funziona:
Sapendo che la funzione RIPETI ripete un dato carattere di un determinato numero di volte, e LEN restituisce la lunghezza totale della stringa, la formula logica è facile da capire:
- LEN(A2) si ottiene il numero totale di caratteri nella cella A2.,
- REPT(0, 6-LEN(A2)) aggiunge il numero richiesto di zeri. Per calcolare quanti zeri devono essere aggiunti, si sottrae la lunghezza della stringa in A2 dal numero massimo di zeri.
- Infine, si concatenano gli zeri con il valore A2 e si ottiene il seguente risultato:
Linea di fondo: Questa formula può aggiungere zeri iniziali sia ai numeri che alle stringhe di testo, ma il risultato è sempre testo, non numero.,
Come aggiungere un numero fisso di zeri precedenti
Per anteporre tutti i valori in una colonna (numeri o stringhe di testo) con un certo numero di zeri, utilizzare la funzione CONCATENATE o l’operatore e commerciale.,
Per esempio, mettere 0 prima di un numero nella cella A2, utilizzare uno dei seguenti formule:
=CONCATENATE(0,A2)
o
=0&A2
Come mostrato nello screenshot qui sotto, la formula aggiunge uno zero a tutte le celle in una colonna a prescindere dal numero di caratteri che il valore originale contiene:
allo stesso modo, è possibile inserire 2 zeri (00), a 3 zeri (000) o come molti zeri che si desidera prima di numeri e stringhe di testo.,
Bottom line: Il risultato di questa formula è anche una stringa di testo anche quando si concatenano zeri con numeri.
Come rimuovere gli zeri iniziali in Excel
Il metodo da utilizzare per rimuovere gli zeri iniziali in Excel dipende da come quegli zeri sono stati aggiunti:
- Se zeri precedenti sono stati aggiunti con un formato di numero personalizzato (gli zeri sono visibili in una cella, ma non nella barra della formula), applicare un altro formato personalizzato o ripristinare Generale, come mostrato qui.,
- Se gli zeri sono stati digitati o altrimenti inseriti in celle formattate come Testo (un piccolo triangolo verde è visualizzato nell’angolo in alto a sinistra della cella), convertire il testo in numero.
- Se gli zeri iniziali sono stati aggiunti utilizzando una formula (la formula appare nella barra della formula quando la cella è selezionata), utilizzare la funzione VALORE per rimuoverli.,
L’immagine seguente mostra tutti e tre i casi, per aiutarvi a scegliere la giusta tecnica di:
Rimuovere gli zeri iniziali, cambiando il formato della cella
Se zeri iniziali sono riportati nelle celle con un formato personalizzato, quindi modificare il formato della cella di default Generale, o applicare un altro formato di numero che non mostra precedente zeri.,
Rimuovere gli zeri iniziali di conversione di testo in numero
Quando il prefisso zeri vengono visualizzati in un formato Testo cella, il modo più semplice per rimuoverli è selezionare la cella(s), fare clic sul punto esclamativo, e quindi fare clic su Converti Numero:
Rimuovere gli zeri iniziali utilizzando una formula
nel caso In cui un precedente zero(s) è aggiunto, con una formula di, usare un’altra formula per rimuoverlo. La formula di rimozione dello zero è semplice come:
=VALUE(A2)
Dove A2 è la cella da cui si desidera rimuovere gli zeri precedenti.,
Questo metodo può anche essere utilizzato per eliminare gli zeri digitati direttamente nelle celle (come nell’esempio precedente) o importati in Excel da qualche fonte esterna. Nel complesso, se si ha a che fare con una stringa con prefisso zero che rappresenta un numero, è possibile utilizzare la funzione VALORE per convertire il testo in numero e rimuovere gli zeri iniziali lungo il percorso.
La seguente schermata mostra due formule:
- La formula del testo in B2 aggiunge zeri al valore in A2 e
- La formula del valore in C2 rimuove gli zeri iniziali dal valore in B2.,
Come nascondere gli zeri in Excel
Se non si desidera visualizzare i valori zero nel foglio Excel, sono disponibili le seguenti due opzioni:
- Per nascondere gli zeri su tutto il foglio, deselezionare l’opzione Mostra uno zero nelle celle con valore zero., Per questo, fare clic su File > Opzioni > Avanzate, e scorrere verso il basso per le opzioni di Visualizzazione per questa sezione del foglio di lavoro:
- Per nascondere i valori zero in alcune cellule, applicare il seguente formato di numero personalizzato a quelle cellule: #;#;;@
Per questo, selezionare le celle in cui si desidera nascondere gli zeri, fare clic su Ctrl+1 per aprire la finestra di dialogo formatta Celle, selezionare Personalizzata sotto la Categoria, il tipo e il formato di cui sopra di codice nella casella Tipo.,
Lo screenshot qui sotto mostra che la cella B2 contiene un valore zero, ma non viene visualizzata nella cella:
Questo è il modo in cui puoi aggiungere, rimuovere e nascondere gli zeri in Excel. Per comprendere meglio le tecniche descritte in questo tutorial, siete invitati a scaricare l’esempio Excel Leading Zeros Cartella di lavoro. Vi ringrazio per la lettura e spero di vedervi sul nostro blog la prossima settimana.,
- Come formattare le celle in Excel
- Personalizzato di Excel formato di numero
- Come convertire la data in testo utilizzando Excel funzione di TESTO e non-formula modi
- Come scambio di colonne in Excel con il drag-n-drop e altri modi per spostare le colonne
