Dan ha una colonna di celle e ogni cella contiene tre elementi: città, stato e CAP. (Tutti e tre sono in una singola cella, proprio come si vede nella riga di un indirizzo.) Alcuni dei codici postali sono cinque cifre e alcuni sono nove. Dan deve inserire sia lo stato a due caratteri che il codice postale a cinque cifre nelle proprie celle, a destra dei dati correnti. Dan sa di poter utilizzare lo strumento Testo in colonne, ma ritiene che richieda molto lavoro poiché avrebbe bisogno di gestire nomi di città composti da più parole e virgole. Dan non può fare a meno di pensare che potrebbe esserci un approccio stereotipato che sarebbe più facile.

È necessario fare alcune ipotesi sui dati per poter formulare raccomandazioni. Supponiamo, ad esempio, che tutti i dati siano in questo formato:

My Town, CA 98765-4321

La parte dal trattino in poi (la parte finale del CAP) è facoltativa, ma la posizione della virgola è statica, segue sempre il nome della città, e lo stato è sempre composto da due caratteri. In questo caso è facile escogitare due formule che estraggano l’abbreviazione dello stato e le prime cinque cifre del CAP:

=MID(A1,FIND(",",A1)+2,2)

=MID(A1,FIND(",",A1)+5,5)

Entrambe le formule chiave sulla virgola; funge da delimitatore tra la città e le due voci realmente desiderate. Se non è presente una virgola nei dati o se sono presenti più virgole, le formule non restituiranno le informazioni desiderate.

Le formule presumono inoltre che non vi siano spazi aggiuntivi nei dati; al massimo c’è un solo spazio dopo la virgola e tra lo stato e il CAP. Questo è, ovviamente, abbastanza facile da applicare: basta usare Trova e sostituisci per sostituire due spazi con un unico spazio in qualsiasi punto del foglio di lavoro.

Naturalmente, se i tuoi dati sono strutturati in questo modo, puoi comunque fare affidamento sullo strumento Testo in colonne per svolgere il tuo lavoro. Tutto quello che devi fare è eseguire lo strumento e dividere i dati in base alla virgola. Questo lascerà la città in una cella e metterà insieme lo stato e il CAP nella cella successiva. Quindi puoi usare di nuovo Testo in colonne, questa volta sulla seconda cella (non il nome della città) e dividere il contenuto in base allo spazio.

Se i tuoi dati non sono così strutturati, forse contengono più virgole nell’indirizzo o spazi aggiuntivi, è necessario un approccio completamente diverso. Per affrontare questo problema, la tecnica di base consiste nel tagliare i dati per rimuovere gli spazi estranei (iniziale, finale e interno), quindi determinare la posizione dell’ultimo spazio e del penultimo spazio.

È possibile estrarre le cinque cifre del CAP, che è definito come immediatamente successive all’ultimo spazio nei dati, utilizzando questa formula:

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ", CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)

L’abbreviazione di stato di due caratteri può essere restituita estraendo i due caratteri immediatamente dopo il penultimo spazio:

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1), LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,2)

Se i tuoi dati sono ancora meno strutturati, forse includono indirizzi che non hanno tutti abbreviazioni di stato di due caratteri (N.J. invece di NJ), allora ti sarebbe meglio utilizzare una macro per suddividere i dati.

La ragione di ciò è che VBA ha un set molto più ricco di funzioni di gestione del testo rispetto a quello che puoi fare usando le formule di Excel. La seguente macro crea una funzione definita dall’utente che può restituire lo stato o il CAP:

Function GetStateZIP(rstrAddress As String, iAction As Integer) As String     Dim arr As Variant     Dim sState As String     Dim sZIP As String     Dim J As Integer     Dim K As Integer

Application.Volatile     rstrAddress = Trim(rstrAddress)

If Len(rstrAddress) = 0 Then Exit Function

sState = "?"

sZIP = "?"

For J = Len(rstrAddress) To 1 Step -1         If Mid(rstrAddress, J, 1) = " " And sZIP = "?" Then             sZIP = Mid(rstrAddress, J + 1, 5)

rstrAddress = Trim(Left(rstrAddress, J))

For K = Len(rstrAddress) To 1 Step -1                 If Mid(rstrAddress, K, 1) = " " And sState = "?" Then                     sState = Mid(rstrAddress, K + 1, 20)

rstrAddress = Trim(Left(rstrAddress, K))

End If             Next K         End If     Next J     If iAction = 1 Then         GetStateZIP = sState     End If     If iAction = 2 Then         GetStateZIP = sZIP     End If End Function

Per utilizzare questa funzione, fornisci semplicemente un riferimento di cella e 1 (se vuoi lo stato) o 2 (se vuoi il CAP). Ecco un esempio di richiesta del codice postale per qualsiasi indirizzo si trovi nella cella A1:

=GetStateZIP(A1,2)

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (9598) si applica a Microsoft Excel 97, 2000, 2002 e 2003. Puoi trovare una versione di questo suggerimento per l’interfaccia a nastro di Excel (Excel 2007 e versioni successive) qui:

Estrazione di uno stato e di un codice postale.