| | Post: 139 | Registrato il: 24/07/2013
| Città: SALERNO | Età: 52 | Utente Junior | Excel LTSC 2021 | | OFFLINE | |
|
  09/11/2023 00:28 | |
Buonasera a tutti gli utenti del Forum
Mi ripresento con il mio Nome e Cognome, in luogo del mio Nickname (Pierpaolo Cavallo) e con un argomento simile a quello di una mia recente discussione, ma che continua a crearmi disagio: il formato numero di dati da testo.
Vengo al dunque:
Mi è stato inviato un file in cui c'è una serie di codici in colonna 'A', tutti con l'apostrofo all'inizio per favorire la visualizzazione di uno o più numeri zero prima delle altre cifre.
Come è noto, ciò conferisce al dato in questione il formato di testo; cosa che a volte - anche se nella fattispecie non accade - può rendere i valori in questione inutilizzabili per formule collegate.
Togliere l'apostrofo con la formula di 'Trova e Sostituisci' pregiudicherebbe la visualizzazione dei codici con gli zeri in questione.
Trasferirli in colonna 'C' senza una formula risolverebbe il problema, ma comporterebbe il dover impostare di volta in volta - per ciascuna cella - il formato 'personalizzato' con uno o più zeri e inserire ogni dato manualmente
Utilizzare le formule Stringa.Estrai e 'Lunghezza' garantisce una soluzione immediata per tutti i dati, ma parziale; nel senso che restituisce la visualizzazione desiderata, ma sempre nel formato 'Testo', con la cifra allineata a sinistra.
Esiste un modo automatico di riprodurre nella colonna 'C' i codici inseriti in colonna 'A' permettendone la visualizzazione in formato diverso da quello di 'Testo' (Generale o Numero)?
Allego file di esempio
Ringrazio anticipatamente per una risposta.
Windows 10 - Excel LTSC |
|
| | Post: 3.128 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Master | 365 | | ONLINE |
|
| 09/11/2023 06:55 | |
ciao
=SOSTITUISCI(A3;"'";"")
e in automatico dovrebbe formattarsi
C.A.P
al limite formatta prima la colonna C
speciale
C.A.P |
| | Post: 1.227 | Registrato il: 15/01/2016
| Città: ROMA | Età: 51 | Utente Veteran | 2016 | | ONLINE |
|
| 09/11/2023 07:04 | |
Buongiorno
Excel nelle celle gestisce o numeri o testi quindi se la cella di origine è un testo e devi togliere l apostrofo sempre un testo avrai ora se devi usare questi dati per fare calcoli basta avere l accortezza nelle formule che userai di moltiplicare per 1 le celle in questione.... altrimenti allega un file con una ventina di righe di questi dati con i risultati che vuoi ottenere scritto a mano |
| | Post: 7.413 | Registrato il: 14/11/2004
| Utente Master | Office 2019 | | OFFLINE |
|
| 09/11/2023 07:11 | |
Ciao Antony, la soluzione esiste inserendo degli zero davanti al numero con il formato personalizzato, devi sapere a priori quanti zero devi anteporre alla cifra effettiva, oppure quanti caratteri sono esempio vedo che la tua cifra più lunga è 0111900, quindi 7 caratteri, se in formato Personalizzato, imposti "0000000", tutti i numeri esempio 0011 diventa 0000011, mantenendo il formato numero cioè puoi fare operazioni ed altro perche sono numeri.
non so se il tuo dato deve avere la lunghezza che occupa attualmente con gli zero, altrimenti si deve creare una funzione ad hoc per la trasformazione mantenendo lunghezza e zero attuali.
Ciao By Sal (8-D [Modificato da by sal 09/11/2023 07:12] se ti piace la soluzione sostienici con una DONAZIONE a piacere. Grazie clicca qui |
| | Post: 139 | Registrato il: 24/07/2013
| Città: SALERNO | Età: 52 | Utente Junior | Excel LTSC 2021 | | OFFLINE | |
|
| 09/11/2023 07:17 | |
Buongiorno, Federico
Grazie per la tua risposta
La tua soluzione è ottima,
in quanto mi risparmia la fatica di formattare una ad una le singole celle (in formato speciale con uno, due, tre, fino a sette zeri...),
ma non completamente risolutiva,
in quanto pur avendo formattato precedentemente le celle come CAP,
Excel continua a considerarle testo, stante l'allineamento a sinistra del valore (Giusto? O mi sbaglio?)
Tale circostanza non genera problemi in questo file, in cui i valori in colonna 'A' sono 'nati' in formato .xlsx
Ma temo che le cose sarebbero diverse quando, in futuro, li importerò in Excel da file .pdf che mi invieranno
(dovendo associare a quei valori praticamente tutte le formule del foglio che ho in mente)
Secondo la (mia) logica il problema non è risolvibile, ma so di non saperne abbastanza per avere certezze al riguardo
federico460, 09/11/2023 06:55:
ciao
=SOSTITUISCI(A3;"'";"")
e in automatico dovrebbe formattarsi
C.A.P
al limite formatta prima la colonna C
speciale
C.A.P
[Modificato da Anthony D'Acunto 09/11/2023 07:19] Windows 10 - Excel LTSC |
| | Post: 1.228 | Registrato il: 15/01/2016
| Città: ROMA | Età: 51 | Utente Veteran | 2016 | | ONLINE |
|
| 09/11/2023 07:24 | |
Ciao
Se alleghi un piccolo file di esempio con i risultati che vuoi ottenere con quella base di dati si può vedere di trovare una soluzione |
| | Post: 140 | Registrato il: 24/07/2013
| Città: SALERNO | Età: 52 | Utente Junior | Excel LTSC 2021 | | OFFLINE | |
|
| 09/11/2023 08:21 | |
Buongiorno, by sal
Grazie per il tuo riscontro
Come ho indicato nel mio primo messaggio della presente discussione, ero giunto alla conclusione che la soluzione di ottenere nella colonna 'C' i dati visualizzati esattamente come nella colonna 'A', ma senza apostrofo iniziale e allineati a destra (quindi non come testo) - in base alle mie conoscenze - era il formato personalizzato in colonna 'C' con un numero di zeri associato - PER CIASCUNA CELLA - al numero di caratteri di ciascuna cella presente in colonna 'A'.
Ma questo significa impostare manualmente il formato di ciascuna cella della colonna 'C' per tutte le 2500 righe del file.
Cosa che vorrei evitare con una funzione - se esiste - che bypassi il problema
by sal, 09/11/2023 07:11:
Ciao Antony, la soluzione esiste inserendo degli zero davanti al numero con il formato personalizzato, devi sapere a priori quanti zero devi anteporre alla cifra effettiva, oppure quanti caratteri sono esempio vedo che la tua cifra più lunga è 0111900, quindi 7 caratteri, se in formato Personalizzato, imposti "0000000", tutti i numeri esempio 0011 diventa 0000011, mantenendo il formato numero cioè puoi fare operazioni ed altro perche sono numeri.
non so se il tuo dato deve avere la lunghezza che occupa attualmente con gli zero, altrimenti si deve creare una funzione ad hoc per la trasformazione mantenendo lunghezza e zero attuali.
Ciao By Sal (8-D
Windows 10 - Excel LTSC |
| | Post: 141 | Registrato il: 24/07/2013
| Città: SALERNO | Età: 52 | Utente Junior | Excel LTSC 2021 | | OFFLINE | |
|
| 09/11/2023 08:23 | |
OK, DANILOFIORINI, grazie per il tuo impegno rivolto alla soluzione del mio problema:
allego un file, come mi hai richiesto, con la descrizione delle soluzioni fin qui attuate tra cui, quella del moltiplicare per 1 i valori.
Ottenendo in un caso valori senza gli zero iniziali e nell'altro valori con un numero di zeri maggiore o minore di quelli presenti nelle celle di Colonna 'A'
Precisando che ciò che vorrei ottenere in colonna 'C' sono i valori esattamente come visualizzati in colonna 'A' (stesso numero di caratteri di ciascun valore); senza l'apostrofo e non nel formato 'Testo'
Difficile, lo so, spero non impossibile
DANILOFIORINI, 09/11/2023 07:24:
Ciao
Se alleghi un piccolo file di esempio con i risultati che vuoi ottenere con quella base di dati si può vedere di trovare una soluzione
Windows 10 - Excel LTSC |
| | Post: 1.229 | Registrato il: 15/01/2016
| Città: ROMA | Età: 51 | Utente Veteran | 2016 | | ONLINE |
|
| 09/11/2023 08:49 | |
Ciao
Excel è progettato così....i testi si allineano a sinistra e i numeri a destra senza gli zero davanti....ora dicevi che questi dati li devi usare in altre formule ed era l esempio di questo utilizzo che ti chiedevo
Ora quello che chiedi e cioè di replicare la colonna A non in formato testo con le formule non si può fare l unica strada è il VBA che analizza cella per cella e ti imposta un formato personalizzato per ogni cella....ma qui mi fermo in quanto è risaputo non scrivo codice
Ora ti ripeto che se vuoi mostrare che utilizzo devi fare con questi dati iniziali si può provare a trovare un modo con le formule
Saluti |
| | Post: 7.414 | Registrato il: 14/11/2004
| Utente Master | Office 2019 | | OFFLINE |
|
| 09/11/2023 09:01 | |
Ciao senza perderti in formule o altro, lancia questa macro e risolvi il problema, la devi lanciare una sola volta oppure quando aggiungi nuovi codici.
quando la lanci ti chiede di inserire la colonna di partenza e la colonna di arrivo, nel tuo caso "A" e diciamo "D" quindi scrivi A,D con la virgola tra le due colonne e nella colonna D avrai tutti i valori con gli zero davanti, in effetti la macro converte il formato celle in base agli zero presenti davanti al numero.
questa la macro
Sub valsal()
Dim d, n, k, x, y, Zero, cc, c1, c2, risp
risp = InputBox("Inserire Colonna di Partenza e Colonna si Arrivo, separate da virgola")
If risp Like "*" & "," & "*" Then Else MsgBox "Attenzione le colonne non sono separate da virgola", vbCritical, "Controllo dati": Exit Sub
cc = Split(risp, ",")
c1 = Range(cc(0) & 1).Column
c2 = Range(cc(1) & 1).Column
Zero = "000000000"
For x = 3 To Cells(Rows.Count, c1).End(xlUp).Row
d = Replace(Cells(x, c1), "'", "")
n = Len(d)
For y = 1 To n
If Mid(d, y, 1) <> "0" Then d = Mid(d, y): Exit For
Next y
k = Mid(Zero, 1, n)
Cells(x, c2) = Val(d)
Cells(x, c2).NumberFormat = k
Next x
MsgBox "Fatto", vbInformation, "Conversione dati"
End Sub la colonna di arrivo puoi sceglierla a piacere, per lanciare la macro clicca sulla ballerina, ti allego il file
Ciao By Sal (8-D
ti allego an se ti piace la soluzione sostienici con una DONAZIONE a piacere. Grazie clicca qui |
| | Post: 142 | Registrato il: 24/07/2013
| Città: SALERNO | Età: 52 | Utente Junior | Excel LTSC 2021 | | OFFLINE | |
|
| 09/11/2023 09:13 | |
Sapevo che le cose stanno come dici tu, ma speravo di sbagliarmi.
Non mi resta che confidare in qualcuno che mi suggerisca qui una soluzione con VBA o che ci smentisca con una formula (ma la vedo dura)
Altrimenti dovrò impostare le singole celle in 'C' in formato Personalizzato
Lavoro seccante, ma non proibitivo; essendo le righe sì 2500 ma le variabili una decina
Speravo in una soluzione automatica per il futuro
dato che l'utilizzo che devo fare di questi dati, essendo codici associati a prodotti, è aggiungere dati (descrizione e valori) nelle colonne successive e ricercarli poi da altri file collegati (principalmente tramite CERCA.VERT, ma anche altre formule).
Tutto parte dal renderli leggibili in tal senso
Cosa che, ripeto, in questo file in cui i dati in 'A', nati in formato .xlsx, risultano leggibili per formule collegate pur con l'apostrofo iniziale, ma per il futuro - importati da file di testo - le cose potrebbero essere diverse e bypassabili solo nella modalità manuale del formato personalizzato per ciascuna cella.
Grazie di tutto
DANILOFIORINI, 09/11/2023 08:49:
Ciao
Excel è progettato così....i testi si allineano a sinistra e i numeri a destra senza gli zero davanti....ora dicevi che questi dati li devi usare in altre formule ed era l esempio di questo utilizzo che ti chiedevo
Ora quello che chiedi e cioè di replicare la colonna A non in formato testo con le formule non si può fare l unica strada è il VBA che analizza cella per cella e ti imposta un formato personalizzato per ogni cella....ma qui mi fermo in quanto è risaputo non scrivo codice
Ora ti ripeto che se vuoi mostrare che utilizzo devi fare con questi dati iniziali si può provare a trovare un modo con le formule
Saluti
Windows 10 - Excel LTSC |
| | Post: 143 | Registrato il: 24/07/2013
| Città: SALERNO | Età: 52 | Utente Junior | Excel LTSC 2021 | | OFFLINE | |
|
| 09/11/2023 09:42 | |
SANTO VBA e SANTO TU, by sal,
la tua soluzione funziona perfettamente!
Non è stato necessario il file .xlsm che mi hai gentilmente allegato.
Ho copiato il tuo codice in un nuovo modulo del Visual Basic della mia cartella macro personale (PERSONAL.XLSB) ed è andato alla grande.
Grazie mille!
Colgo l'occasione per fare i complimenti a te e agli altri amministratori di questo Forum che, in base alla mia esperienza ultraventennale in rete, è di gran lunga il migliore; per la qualità delle soluzioni fornite, la disponibilità e cortesia degli utenti tutti e, non ultima, la pazienza verso noi profani della materia.
Colgo l'occasione anche di salutare gli utenti 'Giuseppe MN'. 'Scossa' e 'Davide Turbo Neofita' che non vedo più qui, ma che ricordo sempre con gratitudine e simpatia.
Vedo dal tuo profilo che è possibile fare una donazione spontanea
Speravo da tempo nelle indicazioni al riguardo e provvedo testè, in quanto sono in debito con voi da tempo per il grande aiuto ricevuto nelle diverse problematiche qui sottoposte.
Un abbraccio
by sal, 09/11/2023 09:01:
Ciao senza perderti in formule o altro, lancia questa macro e risolvi il problema, la devi lanciare una sola volta oppure quando aggiungi nuovi codici.
quando la lanci ti chiede di inserire la colonna di partenza e la colonna di arrivo, nel tuo caso "A" e diciamo "D" quindi scrivi A,D con la virgola tra le due colonne e nella colonna D avrai tutti i valori con gli zero davanti, in effetti la macro converte il formato celle in base agli zero presenti davanti al numero.
questa la macro
Sub valsal()
Dim d, n, k, x, y, Zero, cc, c1, c2, risp
risp = InputBox("Inserire Colonna di Partenza e Colonna si Arrivo, separate da virgola")
If risp Like "*" & "," & "*" Then Else MsgBox "Attenzione le colonne non sono separate da virgola", vbCritical, "Controllo dati": Exit Sub
cc = Split(risp, ",")
c1 = Range(cc(0) & 1).Column
c2 = Range(cc(1) & 1).Column
Zero = "000000000"
For x = 3 To Cells(Rows.Count, c1).End(xlUp).Row
d = Replace(Cells(x, c1), "'", "")
n = Len(d)
For y = 1 To n
If Mid(d, y, 1) <> "0" Then d = Mid(d, y): Exit For
Next y
k = Mid(Zero, 1, n)
Cells(x, c2) = Val(d)
Cells(x, c2).NumberFormat = k
Next x
MsgBox "Fatto", vbInformation, "Conversione dati"
End Sub la colonna di arrivo puoi sceglierla a piacere, per lanciare la macro clicca sulla ballerina, ti allego il file
Ciao By Sal (8-D
ti allego an
Windows 10 - Excel LTSC |
| | Post: 1.230 | Registrato il: 15/01/2016
| Città: ROMA | Età: 51 | Utente Veteran | 2016 | | ONLINE |
|
| 09/11/2023 09:46 | |
Ciao
Solo per correttezza....in CERCA.VERT o INDICEeCONFRONTA funzionerebbe anche con il formato testo.... apposta la richiesta di un file di esempio
Saluti |
| | Post: 3.129 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Master | 365 | | ONLINE |
|
| 09/11/2023 11:00 | |
ciao
io mi ero fatta questa
Public Sub sostituisci()
Dim Lista As Range
Set Lista = Range(Cells(3, 1), Cells(3, 1).End(xlDown))
For Each cl In Lista
If cl <> "" Then
cl.Offset(0, 1) = Replace(cl, "'", "")
cl.Offset(0, 1).NumberFormat = "0000"
End If
Next
End Sub
me quel "0000"
volevo sostituirlo con
RIPETI(0;Len(cl)-1)
ma non trovo il modo di scriverlo
se lo aggiungo con =REPT(.........
mi da solo il primo dato
By sal
se hai due minuti mi spieghi il perché
nb
trovo assurda la faccenda comunque
la colonna A sembra una numerazione
ergo
cercherò di unificare la lunghezza dei testi per portarli tutti uguali
con le formule
basterebbe
=SOSTITUISCI(A3;"'";"")*1
e personalizzare la formattazione con
0000000
o quante cifre servono
ecco che rimangono numeri [Modificato da federico460 09/11/2023 11:13] |
| | Post: 144 | Registrato il: 24/07/2013
| Città: SALERNO | Età: 52 | Utente Junior | Excel LTSC 2021 | | OFFLINE | |
|
| 09/11/2023 11:10 | |
Hai ragione, CERCA.VERT funziona anche con il formato testo.
Non ricordavo in quale circostanza l'apostrofo davanti ai valori mi aveva dato problemi.
Adesso ho fatto mente locale:
avveniva quando lo apponevo in una cella (es. '12)
poi, quando cancellavo il dato nella stessa e ne inserivo un altro senza volerlo apporre (es. 14) mi restituiva comunque ('14)
non riuscendo più a toglierlo
Era perchè la cella era in formato Testo
Non me ne accorgevo in quanto la larghezza colonna era lo stretto indispensabile e non notavo l'allineamento a sinistra del valore
Scusami, abbi pazienza, ma ho fatto confusione e, probabilmente, continuo a farla; non ricordando il problema che si generava in quella circostanza con le celle collegate a quel valore. Ma si generava e, nel caso di oggi, non volevo più quell'apostrofo.
L'importante è che adesso funzioni tutto (per ora 😀 )
Grazie di tutto
DANILOFIORINI, 09/11/2023 09:46:
Ciao
Solo per correttezza....in CERCA.VERT o INDICEeCONFRONTA funzionerebbe anche con il formato testo.... apposta la richiesta di un file di esempio
Saluti
Windows 10 - Excel LTSC |
|
|
|
|