Gestire i numeri provenienti da Microsoft Dynamics® NAV eliminando gli spazi bianchi

Immagine
Gestire i numeri provenienti da Microsoft Dynamics® NAV eliminando gli spazi bianchi
Argomento

Esporti i dati da Microsoft Dynamics® NAV in Excel e hai problemi ad eliminare gli spazi bianchi nei numeri ?
In questo tutorial ti propongo una soluzione per toglierli :-)

Con questo tutorial possiamo vedere come risolvere l'importazione dei dati numerici provenienti da

 

Obiettivo

Eliminare gli spazi bianchi nelle colonne che contengono numeri. ES 2 000 deve diventare 2000.

 

Cosa impariamo

Cosa sono i codici carattere e come effettuare la sostituzione nei testi.

 

Situazione

Nell'esempio dell'esportazione dei dati effettua da Microsoft Dynamics® NAV la colonna quantità contiene dei numeri separati da uno spazio bianco.

Eliminare spazi bianchi in Excel

Non è possibile in questo modo effettuare nessuna operazione come ad esempio la somma.

Cominciamo con il copiare tutta la colonna I nel Foglio2

Copia della colonna

Proviamo ad effettuare la sostituzione dello spazio bianco con la funzione SOSTITUISCI().

In pratica chiediamo di sostituire ogni occorrenza di " " (spazio bianco) con "" (nulla) nella cella A2.

Formula sostituisci in excel

Il risultato però non è quello atteso. Lo spazio è ancora presente !

Formula sostituisci in Excel

Cerchiamo di comprendere cosa non funziona.

È veramente uno spazio bianco quello ce si trova in seconda posizione nella stringa "2 000" a partire da sinistra ?

Per comprendere qual'è il carattere che fa da "spazio bianco" procediamo per gradi.

Grazie alla funzione STRINGA.ESTRAI() andiamo ad estrarre il carattere che si trova in seconda posizione nella cella A2

funzione stringa estrai in excel

Come risultato nella cella D2 non appare nulla.

Facciamoci aiutare della funzione CODICE() che restituisce come risultato il codice numerico del primo carattere contenuto nel testo.

Formula codice in excel

Lo spazio che separa il "2" da "000" corrisponde al codice 160. Per chi ha familiarità con la tabella ASCII, che nel 1968 definì la corrispondenza tra i numeri e i caratteri, questa cosa è strana. Normalmente il codice associato allo spazio bianco è 32.

Infatti se scriviamo manualmente "2 000" nella cella A2 (andando a sovrascrivere il contenuto)

scovrascrivere il contenuto

e trasciniamo in giù le formule contenute in B2:E2 otteniamo nella cella E2 appunto 32.

codice carattere in excel

Infatti ora la formula che da B2 è stata trascinata in B3 ora funziona e lo spazio viene eliminato.

In pratica nella tabella ASCII estesa (UTF-8) che viene usata da Excel esistono due modi per rappresentare lo spazio bianco:

  • il codice 32
  • il codice 160

Nell'esportare i dati Microsoft Dynamics® NAV utilizza il codice 160.

Ora che abbiamo scoperto l'inghippo possiamo procedere alla risoluzione dl nostro problema.

Modifichiamo la formula in B2. La formula che dato il codice restituisce un carattere è CODICE.CARATT. Quindi utilizzeremo CODICE.CARATT(160) al posto di " ".

Formula sostituisci con codice.carattere in excel

Se trasciniamo la formula otteniamo:

Formula trascinata

Sembra che abbiamo ottenuto il risultato voluto ma in realtà quello che appare nella colonna B sono ancora stringe e non numeri !

Infatti se selezioniamo alcune celle nella colonna B non compare la somma nella riga di stato.

seleziopne di testi e non numeri in excel

questo è più evidente se effettuiamo la somma del contenuto della colonna B. Il risultato è 0

Somma in excel

Occorre trasformare i testi contenuti nella colonna B in numeri. Torniamo nella cella B2 e modifichiamo la formula in:

=SOSTITUISCI(A2;CODICE.CARATT(160);"")*1

trasformare un testo in un numero in excel

Se moltiplicando il testo "2000" per 1 Excel lo trasforma in un numero.

Possiamo anche allinearlo a destra.

Allineare a destra

Finalmente possiamo trascinare in giù la nuova formula contenuta in B2 e ora la somma funziona.

sostituzione dello spazio bianco e traformazione in numero in excel

 

Migliorie

A questo punto volgiamo riportare le quantità contenute nella colonna I del Foglio 1 nel Foglio 4. Utilizzeremo la formula vista in precedenza migliorata.

Siccome il numero di righe che contengono la quantità può variare nel tempo, vogliamo che la formula che inseriremo tenga conto se ci sono dati nella cella esame della colonna B.

Con SE(Foglio1!I2<>""; verifichiamo se il contenuto della cella Foglio1!I2 non è vuoto. Se non è vuoto applichiamo la nostra sostituzione altrimenti restituiamo "" cioè nulla.

eliminare spazio bianco da un testo che contiene numeri in excel

In questo modo nel Foglio4 possiamo prevedere 100 righe contenenti la formula, indipendentemente da quante righe siano presenti nel Foglio1.

Inoltre se dal sistema estraiamo con Microsoft Dynamics® NAV i dati aggiornati, basterà copiare la nuova tabella nel nostro Foglio1 e le formule funzioneranno automaticamente.

Buon divertimento.