| | Post: 1 | Registrato il: 28/12/2022
| Età: 49 | Utente Junior | Microsoft® Excel® per Microsoft 365 MSO | | OFFLINE | |
|
  28/12/2022 08:55 | |
Buongiorno a tutti gli utenti,
non riesco a trovare una formula che mi permetta di estrarre solo il cap postale (vedi esempi sotto)
Purtroppo il CAP non si trova sempre nella stessa posizione pertanto funzioni come estrai destra/sinistra etc non sono utilizzabili.
Qualcuno mi potrebbe aiutare ?
Via Del Ponte, 31 Ravina Di, Trento, 38040, Italy
Via Reiss Romoli, 44, 10148 Torino TO, Italy
Via Gino Lorenzetti, 69, 61022 Colbordolo PU, Italy
Via Bassanese, 31050 Vedelago TV, Italy
Via Bresciani, 16, 46040 Gazoldo degli Ippoliti MN, Italy
Viale Europa, 151, 36075 Montecchio Maggiore-Alte Ceccato VI, Italy
VIA FALCINETO 36 C FANO 610232 IT
Via Abbondanza, 11, 22070 Carbonate CO, Italy
Via Colderove, 2, Valdobbiadene, Treviso, 31049, Italy
Grazie a tutti in anticipo.
|
|
| | Post: 1.046 | Registrato il: 24/06/2015
| Città: CATANIA | Età: 80 | Utente Veteran | Excel2019 | | OFFLINE |
|
| 28/12/2022 10:04 | |
Ciao
Non credo sia semplice farlo con una formula ma con una macro sì.
Inserisci questo codice in un Modulo e associalo ad un pulsante (non ActiveX) oppure ad una forma
Option Explicit
Sub CAP()
Dim ur As Long, i As Long, j As Long, cod As String
ur = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To ur
For j = 1 To Len(Cells(i, 1))
If IsNumeric(Mid(Cells(i, 1), j, 1)) Then
cod = cod & Mid(Cells(i, 1), j, 1)
Cells(i, 8) = "'" & CStr(cod)
Else
cod = ""
End If
Next j
Next i
Scrive il CAP in col.H
Fai sapere. Ciao,
Mario
PS - Attenzione: in riga 7 dei tuoi dati vi è un errore (il cap è formato da 5 cifre e non 6) [Modificato da Marius44 28/12/2022 10:12] |
| | Post: 4.872 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | ONLINE |
|
| 28/12/2022 11:14 | |
Giusto per dare un approccio diverso questa è una function da inserire in un modulo standard
vb Function CAP(rng As Range)
Dim i As Integer
Dim str As String
Dim myArr() As String
str = Replace(rng.Value, ",", "")
myArr = Split(str)
For i = LBound(myArr) To UBound(myArr)
If Len(myArr(i)) = 5 And IsNumeric(myArr(i)) Then
CAP = myArr(i)
End If
Next i
End Function Sul foglio, posto l'indirizzo in A1 scrivere in B1
=CAP(A1)
Alfredo |
| | Post: 2.758 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Veteran | 365 | | OFFLINE |
|
| 28/12/2022 11:42 | |
ciao
tanto per partecipare con una formula
=STRINGA.ESTRAI(A1;MAX(SE(VAL.NUMERO(VALORE(STRINGA.ESTRAI(A1;RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)));1)));RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)))))-4;5)
oppure
=DESTRA(SINISTRA(A1;MAX(SE(VAL.NUMERO(VALORE(STRINGA.ESTRAI(A1;RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)));1)));RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1))))));5)
se non hai il 365
da confermare matriciale CTRL MAIUSCOLO INVIO [Modificato da federico460 28/12/2022 11:43] |
| | Post: 413 | Registrato il: 02/04/2018
| Città: PESCARA | Età: 75 | Utente Senior | EXCEL 2016 - SPREAD32 | | OFFLINE |
|
| 28/12/2022 12:11 | |
Ciao federico
puoi provare tu stesso questa stringa ?
123er 345 65100 acd 12cv
a me pare non funzionare, dà come risultato cd 12
Leo
LEO
https://t.me/LordBrum |
| | Post: 2.759 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Veteran | 365 | | OFFLINE |
|
| 28/12/2022 12:16 | |
ciao
certo che no
io mi sono basato sulle stringhe in esempio.
il CAP è sempre l'ultimo numero.
perciò trovo la posizione dell'ultimo numero della stringa
= MAX(SE(VAL.NUMERO(VALORE(STRINGA.ESTRAI(A1;RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)));1)));RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)))) )
e da quella posizione estraggo 5 valori
[Modificato da federico460 28/12/2022 12:17] |
| | Post: 414 | Registrato il: 02/04/2018
| Città: PESCARA | Età: 75 | Utente Senior | EXCEL 2016 - SPREAD32 | | OFFLINE |
|
| 28/12/2022 12:23 | |
ok, capisco, forse non ho seguito bene
allora ho trovato in Extendoffice questa formula, che forse generalizza il problema, ma non so tradurla
potresti tradurla e provarla ?
https://it.extendoffice.com/documents/excel/1660-excel-extract-postcode.html#a1
=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1)) (
in realtà è scritta in modo anche sbagliato
potrebbe essere per te una traccia per un tentativo ?
Leo [Modificato da L2018 28/12/2022 12:24]
LEO
https://t.me/LordBrum |
| | Post: 1 | Registrato il: 28/12/2022
| Età: 49 | Utente Junior | Microsoft® Excel® per Microsoft 365 MSO | | OFFLINE | |
|
| 28/12/2022 12:30 | |
Ciao Federico si negli esempi il CAP risulta essere bene o male sempre prima del nome della città ma ne ho tanti altri dove si trova anche dopo la citta . Via S.Ambrogio 18, Macherio, Milan, 20050, Italy, Via Radici Nord, 112, Castellarano, Reggio Emilia, 42014, Italy.
|
| | Post: 2.760 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Veteran | 365 | | OFFLINE |
|
| 28/12/2022 12:31 | |
ciao
guarda che le stringhe in esame sono un caos assoluto
non ci sono riferimenti fissi
neanche le due macro funzionano in tutti i casi
quella di AL
non intercetta
VIA FALCINETO 36 C FANO 610232 IT
perchè non ci sono virgole ma spazi
quella di Marius
Ha lo stesso problema mio...deve essere l'ultimo numero
per curiosità ora provo la tua
|
| | Post: 2 | Registrato il: 28/12/2022
| Età: 49 | Utente Junior | Microsoft® Excel® per Microsoft 365 MSO | | OFFLINE | |
|
| 28/12/2022 12:32 | |
@alfrimpa grazie 1000 ci provo e ti faccio sapere se funziona. Ho un lavoro immane da fare con questi figli excel e se non trovo un sistema per estrapolare questo benedetto CAP sono fritto |
| | Post: 2.761 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Veteran | 365 | | OFFLINE |
|
| 28/12/2022 12:33 | |
Simo
l'importante è che il CAP sia l'ultimo
numero della stringa il resto alla formula non interessa
l2018
=STRINGA.ESTRAI(A1;TROVA("zzz";SOSTITUISCI(A1;" ";"zzz";MATR.SOMMA.PRODOTTO(1*((STRINGA.ESTRAI(A1;RIF.RIGA(INDIRETTO("1:"&LUNGHEZZA(A1)));1))=" "))-1))+1;LUNGHEZZA(A1))
provala non funziona proprio eh!
[Modificato da federico460 28/12/2022 12:36] |
| | Post: 4.873 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | ONLINE |
|
| 28/12/2022 12:34 | |
Simo hai provato la soluzione mia e di Marius44 in VBA?
La mia si usa come una normale funzione di Excel quella di Marius44 è una macro che va eseguita.
La mia estrae il CAP indipendentemente dalla sua posizione [Modificato da alfrimpa 28/12/2022 12:42]
Alfredo |
| | Post: 2.762 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Veteran | 365 | | OFFLINE |
|
| 28/12/2022 12:39 | |
Al
come detto
la tua sbaglia in un caso....non ci sono virgole
VIA FALCINETO 36 C FANO 610232 IT
quella di Marius
si comporta come la mia
123er 345 65100 acd 12cv
rende 12
il CAP deve essere l'ultimo numero
pari pari la formula
[Modificato da federico460 28/12/2022 12:41] |
| | Post: 415 | Registrato il: 02/04/2018
| Città: PESCARA | Età: 75 | Utente Senior | EXCEL 2016 - SPREAD32 | | OFFLINE |
|
| 28/12/2022 12:43 | |
grazie, che casino, avrei immaginato che fosse meno complicato
a me personalmente resterebbe di studiare qualcosa di REGEX da applicare ad una formula
Leo
LEO
https://t.me/LordBrum |
| | Post: 4.874 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | ONLINE |
|
| 28/12/2022 12:43 | |
Gianfranco pure io mi sono basato sull'esempio dove le virgole ci sono.
In ogni caso anche togliendo le virgole funziona lo stesso.
Nell'esempio che hai fatto il CAP ha 6 cifre non 5 ed è per questo che non funziona. [Modificato da alfrimpa 28/12/2022 12:47]
Alfredo |
| | Post: 4.875 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | ONLINE |
|
| 28/12/2022 12:48 | |
L2018, 28/12/2022 12:43:
a me personalmente resterebbe di studiare qualcosa di REGEX da applicare ad una formula
Leo
Non credo che ce ne sia bisogno
Alfredo |
| | Post: 2.763 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Veteran | 365 | | OFFLINE |
|
| 28/12/2022 12:50 | |
trovato ci sono 6 cifre non 5 le virgole non centrano
svista mia😉
la tua funziona bene [Modificato da federico460 28/12/2022 13:05] |
| | Post: 3 | Registrato il: 28/12/2022
| Età: 49 | Utente Junior | Microsoft® Excel® per Microsoft 365 MSO | | OFFLINE | |
|
| 28/12/2022 12:52 | |
@Marius44 provata funziona alla grande... grazie 1000 |
| | Post: 4.876 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | ONLINE |
|
| 28/12/2022 12:55 | |
Gianfranco se talmente ossessionato da Terio che lo hai messo pure nella tabella al posto mio.
Comunque l'unico 0 che compare si deve al fatto che il CAP ha 6 cifre e non cinque.
Simo vuoi provare anche la mia?
Alfredo |
| | Post: 416 | Registrato il: 02/04/2018
| Città: PESCARA | Età: 75 | Utente Senior | EXCEL 2016 - SPREAD32 | | OFFLINE |
|
| 28/12/2022 12:56 | |
oh cavolo, e dov'è il codice dell'ottimo Terio ? ma Terio è un infiltrato da quell'altro forum ? ![[SM=g27828]](https://im0.freeforumzone.it/up/0/28/9572832.gif)
Leo
LEO
https://t.me/LordBrum |
| | Post: 2.764 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Veteran | 365 | | OFFLINE |
|
| 28/12/2022 12:56 | |
simo
beh! onestamente
la più esatta senza togliere niente
a Marius e alla mia formula
è quella di AL
le nostre sfalsano se il CAP non è l'ultimo numero
quella di Al funziona sempre
L2018
guarda il mio avatar e vedi l'altro forum
secondo te ?
siamo tutti e tre dei
freelance dei forum excel😆
Al è il mio nemico amatissimo
lui ama il VBA e io le formule
Gianfranco se talmente ossessionato da Terio che lo hai messo pure nella tabella al posto mio.
no proprio è che stavo leggendo su microsoft
le risposte di Terio e mi sono imbalsamato sui nomi [Modificato da federico460 28/12/2022 13:10] |
| | Post: 417 | Registrato il: 02/04/2018
| Città: PESCARA | Età: 75 | Utente Senior | EXCEL 2016 - SPREAD32 | | OFFLINE |
|
| 28/12/2022 13:05 | |
alfrimpa, 28/12/2022 12:48:
Non credo che ce ne sia bisogno
se ho ben capito Federico ha messo il nome Terio invece di Alfredo
se è così ok, quindi vorrei provare la function di Alfredo,
che senz'altro è ottima, anche se io cerco sempre di usare formule, le trovo una cosa piu semplice, quando è possibile
Leo
LEO
https://t.me/LordBrum |
| | Post: 2.765 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Veteran | 365 | | OFFLINE |
|
| 28/12/2022 13:07 | |
che senz'altro è ottima, anche se io cerco sempre di usare formule, le trovo una cosa piu semplice, quando è possibile
bravissimo😅
stessa mia idea
nb
la macro di Al
non è ottima in questo caso è perfetta eh! [Modificato da federico460 28/12/2022 13:08] |
| | Post: 4.877 | Registrato il: 21/06/2013
| Città: NAPOLI | Età: 70 | Utente Master | Excel 365 | | ONLINE |
|
| 28/12/2022 13:10 | |
L2018, 28/12/2022 13:05:
le trovo una cosa piu semplice, quando è possibile
Leo
Questa affermazione è tutta da dimostrare
no proprio è che stavo leggendo su microsoft le risposte di Terio e mi sono imbalsamato
Però non mischiamo la lana con la seta
Alfredo |
| | Post: 418 | Registrato il: 02/04/2018
| Città: PESCARA | Età: 75 | Utente Senior | EXCEL 2016 - SPREAD32 | | OFFLINE |
|
| 28/12/2022 13:21 | |
@Alfrimpa
voglio chiarire meglio
la maggior semplicità che attribuisco alle formule si spiega presto, per me,
Normalmente da anni uso un programma da 1,5 Mb chiamato SPREAD32 che poco fa si è risvegliato aggiornando le sue formule fino a Excel 365,
prima non supportava VBA, ora sì ma in modo per me indecifrabile
Quindi finche si tratta di sole formule l'apertura di un file è istantanea e la gestione fluidissima, per tutto il resto, invece di MasterCard uso Excel , ahimè 2007
Leo
LEO
https://t.me/LordBrum |
| | Post: 2.766 | Registrato il: 10/10/2013
| Città: VICENZA | Età: 69 | Utente Veteran | 365 | | OFFLINE |
|
| 28/12/2022 13:39 | |
ciao
L2018
carino
SPREAD32
l'ho provato tempo fa quando ho letto un tuo post.
ma come il 365 e ahimè Google fogli
non ce ne sono |
| | Post: 419 | Registrato il: 02/04/2018
| Città: PESCARA | Età: 75 | Utente Senior | EXCEL 2016 - SPREAD32 | | OFFLINE |
|
| 28/12/2022 13:46 | |
federico460, 28/12/2022 13:39:
ciao
L2018
carino
SPREAD32
l'ho provato tempo fa quando ho letto un tuo post.
ma come il 365 e ahimè Google fogli
non ce ne sono
e ci mancherebbe altro, spread è semplice veloce fluido, VBA di fatto non utilizzabile da me, ed è meno di 2 mega, chi si sognerebbe mai di paragonarlo a Excel ? ma per tutti i giorni è ottimo
Leo
LEO
https://t.me/LordBrum |
| | Post: 1.047 | Registrato il: 24/06/2015
| Città: CATANIA | Età: 80 | Utente Veteran | Excel2019 | | OFFLINE |
|
| 28/12/2022 14:37 | |
Ciao a tutti
Visto l'ultimo inserimento (un po' strano come indirizzo 😁) ho modificato la mia macro così
Option Explicit
Sub CAP()
Dim ur As Long, i As Long, j As Long, cod As String
ur = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To ur
For j = 1 To Len(Cells(i, 1))
If IsNumeric(Mid(Cells(i, 1), j, 1)) Then
cod = cod & Mid(Cells(i, 1), j, 1)
If Len(cod) = 5 Then Cells(i, 8) = "'" & CStr(cod)
Else
cod = ""
End If
Next j
Next i
End Sub
Mi pare che riporti TUTTI i CAP correttamente.
Ciao,
Mario |
|
|
|
|