Come aggiungere zeri iniziali in Excel: tutto ciò che devi sapere

Ci sono situazioni in cui è necessario aggiungere zeri iniziali a un set di dati in Excel. Questo potrebbe essere il caso se mantieni record in Excel, come ID dipendente o ID transazione.

Ad esempio, potresti voler ottenere un aspetto coerente nel tuo set di dati come mostrato di seguito:

In questo tutorial imparerai vari modi per aggiungere zeri iniziali in Excel:

• Conversione del formato in testo
• Utilizzo della formattazione dei numeri personalizzata
• Utilizzo della funzione di testo
• Utilizzo delle funzioni REPT/LEN
• Utilizzo di VBA

Ciascuno di questi metodi ha alcuni vantaggi e svantaggi (trattati in ciascuna sezione).

Vediamo come funziona ciascuno di questi.

Aggiungi zeri iniziali convertendo il formato in testo

Quando usare: Quando si dispone di un piccolo set di dati numerici e si prevede di eseguire questa modifica manualmente.

Supponiamo di avere ID dei dipendenti del reparto marketing come mostrato di seguito e di voler rendere coerenti questi ID aggiungendo degli zeri iniziali.

Quindi provi a modificare l'id inserendo degli zeri iniziali (00001 invece di 1).

Ma con tuo stupore, Excel lo converte di nuovo in 1.

Ciò accade poiché Excel comprende che 00001 e 1 sono gli stessi numeri e dovrebbero seguire le stesse regole di visualizzazione.

Ora, per quanto frustrante possa essere per te, Excel ha le sue ragioni.

Quindi, per portare a termine il lavoro senza piegare le regole di Excel, dovrai approfittare del fatto che questa regola non si applica alla formattazione del testo.

Quindi ecco cosa devi fare:

1. Seleziona le celle in cui desideri aggiungere manualmente gli zeri iniziali.
2. Vai a Home → Gruppo di numeri e seleziona Testo dal menu a discesa.

Questo è tutto!

Ora, quando si immettono manualmente gli zeri iniziali, Excel si adeguerà prontamente.

Attenzione: quando si converte il formato in Testo, alcune funzioni di Excel non funzioneranno correttamente. Ad esempio, la funzione SOMMA/CONTEGGIO ignorerebbe la cella poiché è in formato testo.

Aggiungi zeri iniziali utilizzando la formattazione dei numeri personalizzata

Quando usare: quando si dispone di un set di dati numerico e si desidera che il risultato sia numerico (non di testo).

Quando visualizzi un numero in un formato specifico, non cambia il valore sottostante del numero. Ad esempio, posso visualizzare il numero 1000 come 1000 o 1.000 o 1000.00 o 001000 o 26-09-1902 (anche le date sono numeri nel back-end in Excel).

In tutti i diversi modi per visualizzare il numero, il valore del numero non cambia mai. È solo il modo in cui viene visualizzato che è cambiato.

Per aggiungere zeri iniziali, possiamo formattarlo per mostrarlo in questo modo, mentre il valore sottostante rimarrebbe invariato.

Ecco i passaggi per utilizzare questa tecnica per aggiungere zeri iniziali in Excel:

1. Seleziona le celle in cui desideri aggiungere gli zeri iniziali.
2. Vai a Home → Gruppo di numeri e fai clic sul lanciatore di dialogo (una piccola freccia inclinata in basso a destra). Si aprirà la finestra di dialogo Formato celle. In alternativa, puoi anche utilizzare la scorciatoia da tastiera: Control + 1.
3. Nella finestra di dialogo Formato celle, all'interno della scheda Numero, selezionare Personalizzato nell'elenco Categoria.
4. Nel campo Tipo, inserisci 00000
5. Fare clic su OK.

In questo modo verranno sempre visualizzati tutti i numeri come cinque cifre, dove gli 0 iniziali vengono aggiunti automaticamente se il numero è inferiore a 5 cifre. Quindi 10 diventerebbe 00010 e 100 diventerebbe 00100.

In questo caso, abbiamo utilizzato sei zeri, ma se i tuoi dati hanno numeri con più cifre, devi utilizzare il formato di conseguenza.

Nota: questa tecnica funzionerebbe solo per un set di dati numerico. Nel caso in cui disponi di ID dipendente come A1, A2, A3 e così via, questi sono testo e non cambieranno quando applichi il formato personalizzato come mostrato sopra.

Aggiungi zeri iniziali utilizzando la funzione TESTO

Quando usare: Quando vuoi che il risultato sia testo.

La funzione TESTO consente di modificare il valore nel formato desiderato.

Ad esempio, se si desidera che 1 venga visualizzato come 001, è possibile utilizzare la funzione TEXT.

Tuttavia, ricorda che la funzione TESTO cambierà il formato e lo renderà TESTO. Ciò significa che quando si crea 1 come 001, Excel tratta il nuovo risultato come testo con tre caratteri (proprio come abc o xyz).

Ecco come aggiungere zeri iniziali utilizzando la funzione TESTO:

1. Se hai i numeri nella colonna A (diciamo da A2: A100), seleziona B2: B100 e inserisci la seguente formula:
   =TESTO(LA2,”00000″)
2. Premi Ctrl + Invio per applicare la formula a tutte le celle selezionate.

Questo visualizzerà tutti i numeri come cinque cifre, dove gli 0 iniziali vengono aggiunti automaticamente se il numero è inferiore a 5 cifre.

Un vantaggio della conversione dei dati in testo è che ora puoi utilizzarli nelle formule di ricerca come CERCA.VERT o INDICE/CONFRONTA per recuperare i dettagli di un dipendente utilizzando il suo ID dipendente.

Nota: questa tecnica funzionerebbe solo per un set di dati numerico. Nel caso in cui tu abbia ID dipendente come A1, A2, A3 e così via, questi sono testo e non cambierebbero quando applichi il formato personalizzato come mostrato sopra.

Aggiungi zeri iniziali utilizzando le funzioni REPT e LEN

Quando usare: Quando si dispone di un set di dati numerico/alfanumerico e si desidera che il risultato sia testo.

Lo svantaggio dell'uso della funzione TEXT era che avrebbe funzionato solo con dati numerici. Ma nel caso in cui si disponga di un set di dati alfanumerico (ad esempio A1, A2, A3 e così via), la funzione TEXT fallirebbe.

In questi casi, una combinazione della funzione REPT e LEN fa il trucco.

Ecco come farlo:

1. Se hai i numeri nella colonna A (diciamo da A2: A100), seleziona B2: B100 e inserisci la seguente formula:
   =REPT(0,5-LEN(A2))&A2
2. Premi Ctrl + Invio per applicare la formula a tutte le celle selezionate.

Ciò renderebbe tutti i numeri/stringhe lunghi 5 caratteri con zeri iniziali dove necessario.

Ecco come funziona questa formula:

• LEN(A2) fornisce la lunghezza della stringa/numeri nella cella.
• =REPT(0,5-LEN(A2)) darebbe il numero di 0 che dovrebbe essere aggiunto. Qui ho usato 5 nella formula in quanto quella era la lunghezza massima di stringa/numeri nel mio set di dati. Puoi cambiarlo in base ai tuoi dati.
• =REPT(0,5-LEN(A2))&A2 aggiungerà semplicemente il numero di zeri al valore della cella. Ad esempio, se il valore nella cella è 123, restituirà 00123.

Aggiungi zeri iniziali utilizzando la funzione personalizzata (VBA)

Se l'aggiunta di zeri iniziali in Excel è qualcosa che devi fare abbastanza spesso, utilizzare una funzione personalizzata è una buona idea.

Ecco il codice VBA che creerà una semplice funzione per l'aggiunta di zeri iniziali:

'Codice di Sumit Bansal da http://trumpexcel.com Funzione AddLeadingZeroes(ref As Range, Length As Integer) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len(ref) For i = 1 To Length If i <= StrLen Then Result = Risultato & Mid(ref, i, 1) Else Risultato = "0" & ​​Risultato End If Next i AddLeadingZeroes = Risultato End Function

Aggiungi semplicemente questo codice alla finestra del codice del modulo e sarai in grado di usarlo come qualsiasi altra funzione del foglio di lavoro.

Oppure crea un componente aggiuntivo per questo ed essere in grado di condividerlo con i tuoi colleghi.