| | Post: 31 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
11/07/2017 17:42 | |
Buongiorno.
Ho il seguente problema: devo acquisire da un foglio di lavoro dei numeri di telefono.
Il foglio di lavoro di origine, a priori, non riporta lo zero davanti ai numeri, dunque l'importazione dei numeri fissi è errata.
Avrei dunque necessità di importare così come sono i numeri che iniziano con 3 (in quanto è altamente probabile che siano riconducibili a cellulare) mentre, per tutti i numeri che non iniziano con 3, è necessario anteporre lo zero, anche variando la formattazione in testo.
Vi allego l'esempio (in C2 l'esempio di importazione errata).
Grazie.
Nigel [Modificato da Nigel78 11/07/2017 18:02] |
|
| | Post: 3.347 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | OFFLINE |
|
11/07/2017 17:45 | |
E come fai a discriminare i fissi dai cellulari?
Allega un file di esempio (con più casistiche) ed inserisci a mano il risultato desiderato.
Alfredo |
| | Post: 31 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
11/07/2017 17:55 | |
Do per scontato che i numeri che iniziano con 3 sono cellulari, tutti i restanti sono fissi.
In C2 vorrei trovare 01000000 [Modificato da Nigel78 11/07/2017 18:15] |
| | Post: 3.913 | Registrato il: 13/03/2012
| Città: LIVORNO | Età: 78 | Utente Master | 2010 | | OFFLINE | |
|
11/07/2017 18:18 | |
Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To LR
If Left(Cells(r, 1), 1) = "3" Then
Cells(r, 3) = Cells(r, 1)
Else
Cells(r, 3) = "0" & Str(Cells(r, 1))
End If
Next
End Sub
----------
Win 10 - Excel 2010
allega un file di esempio, guadagnerai tempo tu e lo farai risparmiare a chi ti aiuta |
| | Post: 3.349 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | OFFLINE |
|
11/07/2017 18:31 | |
Ciao a tutti
Poiché la macro di Patel (che saluto) interpone uno spazio tra lo zero ed il numero mi permetto di proporre questa piccola modifica
vb Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To LR
If Left(Cells(r, 1), 1) = "3" Then
Cells(r, 3) = Cells(r, 1)
Else
Cells(r, 3) = "0" & Trim(Str(Cells(r, 1)))
End If
Next
End Sub
La colonna C va formattata come "TESTO" [Modificato da alfrimpa 11/07/2017 18:58]
Alfredo |
| | Post: 3.914 | Registrato il: 13/03/2012
| Città: LIVORNO | Età: 78 | Utente Master | 2010 | | OFFLINE | |
|
11/07/2017 19:04 | |
grazie alfredo, non mi ero accorto.
----------
Win 10 - Excel 2010
allega un file di esempio, guadagnerai tempo tu e lo farai risparmiare a chi ti aiuta |
| | Post: 3.351 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | OFFLINE |
|
11/07/2017 19:05 | |
Ci mancherebbe Andrea.
Solo che non sono riuscito a capire dove si origina lo spazio. [Modificato da alfrimpa 11/07/2017 19:05]
Alfredo |
| | Post: 3.352 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | OFFLINE |
|
11/07/2017 19:09 | |
La cosa strana è che se inserisco nella tua macro l'istruzione
vb MsgBox Len(Str(Cells(r, 1)))
per il numero 1000000 dovrebbe dare 7 e invece restituisce 8; mah.
Alfredo |
| | Post: 508 | Registrato il: 16/08/2015
| Città: CORDENONS | Età: 67 | Utente Senior | Excel 2016-32bit Win11 | | OFFLINE |
|
11/07/2017 20:03 | |
Un saluto a tutti.
Anche una formattazione "Personalizzata" delle celle con questo "Tipo" dovrebbe funzionare:
0######### [Modificato da rollis13 11/07/2017 22:04]
______________________________________________________________
C'è chi fa le COSE a CASO e chi fa CASO alle COSE (Ignoto) |
| | Post: 5.208 | Registrato il: 14/11/2004
| Utente Master | Office 2019 | | OFFLINE |
|
12/07/2017 06:31 | |
Ciao a tutti, il metodo più idoneo è quello di Rollis che saluto, senza vba.
in quanto in Italia tutti i numeri telefonici, ad oggi, sono composti da 10 cifre prefisso compreso, anche i cellulari hanno 10 cifre.
con il suo formato quando trova il numero 3 in prima posizione lo lascia, invece quando trova anche 3 ma con un numero minore di 10 caratteri aggiunge lo 0 davanti.
credo che il numero in seconda riga non sia in numero telefonico, in quanto compreso di prefisso dovrebbe avere 9 cifre.
Alfredo Ciao, lo spazio viene inserito dalla funzione Str(), sembra strano ma è cosi, anche io ci sono capitato spesso, anche se a video parte da inizio cella.
Ciao By Sal
[Modificato da by sal 12/07/2017 06:59] se ti piace la soluzione sostienici con una DONAZIONE a piacere. Grazie clicca qui |
| | Post: 399 | Registrato il: 24/06/2015
| Città: CATANIA | Età: 80 | Utente Senior | Excel2019 | | OFFLINE |
|
12/07/2017 06:49 | |
Salve a tutti
@alfrimpa
riporto quel che recita la Guida di VBA
"Quando i numeri vengono convertiti in stringhe, è sempre riservato uno spazio iniziale il segno di numero. Se è positivo, la stringa restituita contiene uno spazio iniziale e il segno di addizione è implicito."
Ecco perchè il milione ti dà 8 caratteri e non 7
Ciao,
Mario |
| | Post: 3.355 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | OFFLINE |
|
12/07/2017 09:34 | |
Sal e Mario grazie dell'informazione che non conoscevo.
Alfredo |
| | Post: 32 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
12/07/2017 10:13 | |
rollis13, 11/07/2017 20.03:
Un saluto a tutti.
Anche una formattazione "Personalizzata" delle celle con questo "Tipo" dovrebbe funzionare:
0#########
Buongiorno e grazie per la risposta.
Ma non mi è chiaro ciò che devo fare.
Grazie.
Nigel.
|
| | Post: 3.356 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | OFFLINE |
|
12/07/2017 10:37 | |
Seleziona la zona di tuo interesse in colonna C, tasto dx del mouse Formato Celle, nelle Categorie scegli Personalizzato e nella casella Tipo inserisci 0#########
Alfredo |
| | Post: 45 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
01/08/2017 17:46 | |
alfrimpa, 11/07/2017 18.31:
Ciao a tutti
Poiché la macro di Patel (che saluto) interpone uno spazio tra lo zero ed il numero mi permetto di proporre questa piccola modifica
vb Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To LR
If Left(Cells(r, 1), 1) = "3" Then
Cells(r, 3) = Cells(r, 1)
Else
Cells(r, 3) = "0" & Trim(Str(Cells(r, 1)))
End If
Next
End Sub
La colonna C va formattata come "TESTO"
Ho provato ad applicare il suggerimento a un lavoro leggermente più complesso.
Stavolta è necessario lavorare sui numeri di telefono delle colonne F e G, sovrascrivendo il numero nella stessa cella come da criterio precedente (invariato se inizia con "3", in tutti gli altri casi aggiungendo uno "0" davanti).
La colonna J è certamente sempre popolata, pertanto è quella che fornisce il numero di righe su cui applicare l'operazione.
Grazie.
[Modificato da Nigel78 01/08/2017 17:54] |
| | Post: 48 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
04/08/2017 12:54 | |
Nigel78, 01/08/2017 17.46:
Ho provato ad applicare il suggerimento a un lavoro leggermente più complesso.
Stavolta è necessario lavorare sui numeri di telefono delle colonne F e G, sovrascrivendo il numero nella stessa cella come da criterio precedente (invariato se inizia con "3", in tutti gli altri casi aggiungendo uno "0" davanti).
La colonna J è certamente sempre popolata, pertanto è quella che fornisce il numero di righe su cui applicare l'operazione.
Grazie.
Up! (scusate se mi permetto). |
| | Post: 3.925 | Registrato il: 13/03/2012
| Città: LIVORNO | Età: 78 | Utente Master | 2010 | | OFFLINE | |
|
05/08/2017 05:11 | |
devi iniziare dalla riga 2, non dalla 1 che non contiene numeri
----------
Win 10 - Excel 2010
allega un file di esempio, guadagnerai tempo tu e lo farai risparmiare a chi ti aiuta |
| | Post: 49 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
07/08/2017 16:36 | |
patel45, 05/08/2017 05.11:
devi iniziare dalla riga 2, non dalla 1 che non contiene numeri
Ho rettificato, però adesso risulta un errore nella riga in grassetto.
Sub zero_davanti_tel()
Columns("F:G").Select
Selection.NumberFormat = "@"
LR = Cells(Rows.Count, "J").End(xlUp).Row
For r = 2 To LR
If Left(Cells(r, 6), 1) = "3" Then
Cells(r, 6) = Cells(r, 6)
Else
Cells(r, 6) = "0" & Trim(Str(Cells(r, 6)))
End If
Next
LR = Cells(Rows.Count, "J").End(xlUp).Row
For r = 2 To LR
If Left(Cells(r, 7), 1) = "3" Then
Cells(r, 7) = Cells(r, 7)
Else
Cells(r, 7) = "0" & Trim(Str(Cells(r, 7)))
End If
Next
End Sub
|
| | Post: 3.927 | Registrato il: 13/03/2012
| Città: LIVORNO | Età: 78 | Utente Master | 2010 | | OFFLINE | |
|
07/08/2017 18:13 | |
allega il file aggiornato
----------
Win 10 - Excel 2010
allega un file di esempio, guadagnerai tempo tu e lo farai risparmiare a chi ti aiuta |
| | Post: 50 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
08/08/2017 10:26 | |
patel45, 07/08/2017 18.13:
allega il file aggiornato
Eccolo.
[Modificato da Nigel78 08/08/2017 11:12] |
| | Post: 3.928 | Registrato il: 13/03/2012
| Città: LIVORNO | Età: 78 | Utente Master | 2010 | | OFFLINE | |
|
08/08/2017 11:11 | |
l'hai fatta un po' complicata, l'errore dipende dalle celle vuote ed occorre tenerne conto, prova questa
Sub zero_davanti_tel()
Columns("F:G").NumberFormat = "@"
LR = Cells(Rows.Count, "J").End(xlUp).Row
For Each Cell In Range("F2:G" & LR)
If Cell.Value <> "" And Left(Cell.Value, 1) <> "3" Then
Cell.Value = "0" & Trim(Str(Cell))
End If
Next
End Sub
----------
Win 10 - Excel 2010
allega un file di esempio, guadagnerai tempo tu e lo farai risparmiare a chi ti aiuta |
| | Post: 51 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
08/08/2017 11:14 | |
patel45, 08/08/2017 11.11:
l'hai fatta un po' complicata, l'errore dipende dalle celle vuote ed occorre tenerne conto, prova questa
Sub zero_davanti_tel()
Columns("F:G").NumberFormat = "@"
LR = Cells(Rows.Count, "J").End(xlUp).Row
For Each Cell In Range("F2:G" & LR)
If Cell.Value <> "" And Left(Cell.Value, 1) <> "3" Then
Cell.Value = "0" & Trim(Str(Cell))
End If
Next
End Sub
Sì, in effetti l'operazione va in errore quando incrocia una cella vuota.
Comunque devo ragionare anche sui tempi: potrei avere 8000 record e in quel caso l'operazione diventa davvero interminabile.
Il problema potrebbe essere di formattazione delle celle, come mi è capitato in altri casi analoghi: manualmente, copiando i numeri e incollandoli in un Blocco Note e ricopiandoli e incollandoli sul file, l'operazione potrebbe velocizzarsi.
Appena finisce di elaborare, provo. [Modificato da Nigel78 08/08/2017 11:16] |
| | Post: 954 | Registrato il: 04/02/2009
| Città: MILANO | Età: 64 | Utente Senior | 2000 - 2007 | | OFFLINE | |
|
08/08/2017 21:52 | |
Ma se stai copiando dei numeri basta che tu predefinisca il formato della colonna di destinazione come "Testo".
pietrol
____________________________________
Win 10 Pro - Excel 2000 - 2007 -2016
Il lupo ululà, il castello ululì. |
| | Post: 58 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
31/08/2017 14:47 | |
Buongiorno. Grazie al vostro aiuto ho collaudato ripetutamente l'operazione che, di per sè, è corretta. Procedendo con il concreto, si sono però verificate delle situazioni che in prima istanza non erano previste. Dunque, oltre ad aggiungere lo zero di fronte ai numeri che non iniziano per "3" (che con maggiore probabilità sono cellulari), si dovrebbero effettuare due modifiche, una precedente e una successiva. 1) Rimuovere, all'interno di ciascuna cella, tutti i caratteri che non sono numerici (dunque spazi, caratteri alfabetici e caratteri speciali); 2) procedere con l'operazione già collaudata; 3) per tutti i numeri che iniziano con "3" e che sono più lunghi di 10 cifre, rimuovere le cifre al fondo fino a ridurre il numero a 10 cifre. In questo momento sto procedendo attraverso Excel con le seguenti formule: per rimuovere gli spazi utilizzo =SOSTITUISCI per rendere i numeri che iniziano con "3" al massimo di dieci cifre, utilizzo la funzione =LUNGHEZZA dopodichè li rettifico a mano. Sto provando ad integrare le operazioni con VBA, ma con scarso successo. Potete ancora darmi qualche suggerimento? (nell'esempio: le colonne F e G sono quelle da lavorare, il risultato desiderato lo vedete nelle colonne L e M). Grazie. Nigel [Modificato da Nigel78 31/08/2017 14:48] |
| | Post: 59 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
18/09/2017 14:35 | |
Nigel78, 31/08/2017 14.47:
Buongiorno. Grazie al vostro aiuto ho collaudato ripetutamente l'operazione che, di per sè, è corretta. Procedendo con il concreto, si sono però verificate delle situazioni che in prima istanza non erano previste. Dunque, oltre ad aggiungere lo zero di fronte ai numeri che non iniziano per "3" (che con maggiore probabilità sono cellulari), si dovrebbero effettuare due modifiche, una precedente e una successiva. 1) Rimuovere, all'interno di ciascuna cella, tutti i caratteri che non sono numerici (dunque spazi, caratteri alfabetici e caratteri speciali); 2) procedere con l'operazione già collaudata; 3) per tutti i numeri che iniziano con "3" e che sono più lunghi di 10 cifre, rimuovere le cifre al fondo fino a ridurre il numero a 10 cifre. In questo momento sto procedendo attraverso Excel con le seguenti formule: per rimuovere gli spazi utilizzo =SOSTITUISCI per rendere i numeri che iniziano con "3" al massimo di dieci cifre, utilizzo la funzione =LUNGHEZZA dopodichè li rettifico a mano. Sto provando ad integrare le operazioni con VBA, ma con scarso successo. Potete ancora darmi qualche suggerimento? (nell'esempio: le colonne F e G sono quelle da lavorare, il risultato desiderato lo vedete nelle colonne L e M). Grazie. Nigel
Scusate la pausa, ma sto "studiando". Ho provato a costruire il codice che dovrebbe rispondere all'esigenza del punto 3, e cioè troncare a un massimo di 10 cifre i numeri che iniziano con "3", ma c'è qualcosa che non funziona. Potete darmi un suggerimento? Grazie. Sub cell_max10() Columns("F:G").NumberFormat = "@" LR = Cells(Rows.Count, "F").End(xlUp).Row For Each Cell In Range("F2:G" & LR) If Left(cell, 1) = 3 Then If Len(cell) > 10 Then cell = Left(cell, 10) End If Next End Sub |
| | Post: 422 | Registrato il: 24/06/2015
| Città: CATANIA | Età: 80 | Utente Senior | Excel2019 | | OFFLINE |
|
18/09/2017 15:15 | |
Ciao
poichè formatti i numeri come stringhe devi tener presente quanto ho detto ad alfrimpa (ciao Alfredo) al post del 12/07/2017 06.49
Ciao,
Mario |
| | Post: 2.013 | Registrato il: 21/03/2008
| Città: LOCATE VARESINO | Età: 76 | Utente Veteran | 2007 / 13 | | OFFLINE | |
|
18/09/2017 16:09 | |
ciao
una possibile sub(9 in base ai tuoi dati
Option Explicit
Sub esperimento_tel()
Dim i As Long
Dim VcelF As Variant
For i = 2 To Cells(Rows.Count, "F").End(xlUp).Row
VcelF = Cells(i, "F")
VcelF = Application.Substitute(Application.Substitute(VcelF, " ", ""), "/", "")
If Len(VcelF) = 10 Then
Cells(i, "E") = "'" & VcelF
ElseIf Len(VcelF) < 10 Then
Cells(i, "E") = "'" & "0" & VcelF
Else
Cells(i, "E") = "'" & Mid(VcelF, 1, 10)
End If
Next i
End Sub
Ciao da locate
excel 2007 / 13 |
| | Post: 60 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
18/09/2017 17:11 | |
Sembrerebbe funzionare così (intendo per il terzo passaggio, e cioè se il numero inizia per "3" e ha più di 10 cifre, tronca il numero alla decima cifra).
Sub cell_max10()
Columns("F:G").NumberFormat = "@"
LR = Cells(Rows.Count, "F").End(xlUp).Row
For Each cell In Range("F2:G" & LR)
If Left(cell.Value, 1) = "3" Then
If Len(cell) > 10 Then cell.Value = Left(cell, 10)
End If
Next
End Sub [Modificato da Nigel78 18/09/2017 17:14] |
| | Post: 2.014 | Registrato il: 21/03/2008
| Città: LOCATE VARESINO | Età: 76 | Utente Veteran | 2007 / 13 | | OFFLINE | |
|
18/09/2017 18:37 | |
ciao
quindi lavorando su un set di celle la mascro sarebbe questa
Option Explicit
Sub esperimento_tel()
Dim R_FG As Variant
Dim VcelF As Variant
Dim riga As Long, colon As Long
Set R_FG = Range("F2:G10")
For Each VcelF In R_FG
If Len(VcelF) > 0 Then
riga = VcelF.Row
colon = VcelF.Column
VcelF = Application.Substitute(Application.Substitute(VcelF, " ", ""), "/", "")
If Len(VcelF) = 10 Then
Cells(riga, colon) = "'" & VcelF
ElseIf Len(VcelF) < 10 Then
Cells(riga, colon) = "'" & "0" & VcelF
Else
Cells(riga, colon) = "'" & Mid(VcelF, 1, 10)
End If
End If
Next
Set R_FG = Nothing
End Sub
Ciao da locate
excel 2007 / 13 |
| | Post: 61 | Registrato il: 08/02/2016
| Città: REGGIO CALABRIA | Età: 46 | Utente Junior | 97-2003 | | OFFLINE | |
|
19/09/2017 12:24 | |
locatevaresino, 18/09/2017 18.37:
ciao
quindi lavorando su un set di celle la mascro sarebbe questa
Option Explicit
Sub esperimento_tel()
Dim R_FG As Variant
Dim VcelF As Variant
Dim riga As Long, colon As Long
Set R_FG = Range("F2:G10")
For Each VcelF In R_FG
If Len(VcelF) > 0 Then
riga = VcelF.Row
colon = VcelF.Column
VcelF = Application.Substitute(Application.Substitute(VcelF, " ", ""), "/", "")
If Len(VcelF) = 10 Then
Cells(riga, colon) = "'" & VcelF
ElseIf Len(VcelF) < 10 Then
Cells(riga, colon) = "'" & "0" & VcelF
Else
Cells(riga, colon) = "'" & Mid(VcelF, 1, 10)
End If
End If
Next
Set R_FG = Nothing
End Sub
Per rimuovere tutti i caratteri che non sono numerici ho utilizzato una regexp con pattern "\D*".
Mi pare che la tua soluzione risolva esclusivamente i casi " " e "/". O mi sbaglio? |
|
|