Steve ha numeri di telefono nella colonna A, ad esempio 3035551212, e vorrebbe poter guardare le prime tre cifre (il prefisso) e restituire nella colonna B lo stato a cui è associato quel prefisso. Si chiede quale sia il modo migliore per farlo.

Excel ha molte funzioni che semplificano la vita quando si tenta di manipolare i dati. In questo caso, è semplice utilizzare la funzione CERCA.VERT per abbinare il prefisso allo stato corrispondente.

Prima di applicare una funzione per recuperare le informazioni desiderate, è necessario creare una semplice tabella di dati che contenga i dati che si desidera recuperare. In questa tabella, è necessario disporre i prefissi e gli stati nelle rispettive colonne fianco a fianco, all’interno del foglio di lavoro, ordinati per prefisso. Ad esempio, potresti inserire i prefissi nella colonna F e gli stati per quei prefissi nella colonna G. I prefissi e gli stati possono essere trovati su un certo numero di siti web, oppure puoi creare la tua tabella se preferisci.

Una volta inseriti i dati nelle due colonne, seleziona quelle colonne e crea un nome per l’intervallo selezionato. (Il modo in cui crei un intervallo denominato è stato trattato in altri numeri di ExcelTips.) Ad esempio, potresti denominare l’intervallo qualcosa come StateCodes. Questa denominazione, sebbene non strettamente necessaria, semplifica l’utilizzo della formula di ricerca.

Supponendo che il numero di telefono sia nella cella A1 e che desideri che il nome dello stato sia restituito nella colonna accanto al numero di telefono, nella cella B1 dovresti inserire:

=VLOOKUP(VALUE(LEFT(A1,3)),StateCodes,2,FALSE)

Le funzioni VALUE e LEFT vengono utilizzate per estrarre solo i primi tre caratteri dal numero di telefono. Questo viene quindi utilizzato nella formula CERCA.VERT per trovare il prefisso nella tabella StateCodes. Excel restituisce il nome dello stato che corrisponde al prefisso.

Un altro modo per estrarre il prefisso (che è essenziale per la ricerca) è usare la funzione FLOOR, come mostrato qui:

=VLOOKUP(FLOOR(A1/10000000,1),StateCodes,2,FALSE)

Tieni presente che questo approccio richiede che il numero di telefono venga memorizzato come un numero, quindi può essere diviso per 10.000.000.

Gli approcci discussi qui funzionano alla grande, a condizione che i numeri di telefono siano sempre nel formato specificato (3035551212). Se i tuoi numeri di telefono hanno un formato diverso, magari uno che utilizza parentesi e trattini, la formula non funzionerà e dovrà essere modificata per vedere dove si trova realmente il prefisso nel numero di telefono. Se hai numeri di telefono che non sono in un unico formato, tutte le scommesse sono disattivate e il compito di fare la ricerca diventa molto, molto più difficile.

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

Questo suggerimento (8063) 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:

Determinazione di uno stato da un prefisso.