Dan a une colonne de cellules et chaque cellule contient trois éléments: ville, état et code postal. (Tous les trois sont dans une seule cellule, tout comme vous le voyez dans une ligne d’adresse.) Certains codes postaux comportent cinq chiffres et d’autres neuf. Dan doit extraire à la fois l’état à deux caractères et le code postal à cinq chiffres dans leurs propres cellules, à droite des données actuelles. Dan sait qu’il peut utiliser l’outil Texte dans les colonnes, mais estime que cela implique beaucoup de travail car il aurait besoin de traiter des noms de villes à plusieurs mots et des virgules. Dan ne peut s’empêcher de penser qu’il pourrait y avoir une approche stéréotypée qui serait plus facile.

Il faut faire quelques hypothèses sur les données afin de faire des recommandations. Supposons, par exemple, que toutes les données soient dans ce format:

My Town, CA 98765-4321

La partie à partir du tiret (la partie arrière du code postal) est facultative, mais la position de la virgule est statique – elle suit toujours le nom de la ville – et l’état se compose toujours de deux caractères. Dans ce cas, il est facile de concevoir deux formules qui extraient l’abréviation de l’état et les cinq premiers chiffres du code postal:

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

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

Les deux formules entrent dans la virgule; il sert de délimiteur entre la ville et les deux éléments vraiment voulus. S’il n’y a pas de virgule dans les données ou s’il y a plusieurs virgules, les formules ne renverront pas les informations souhaitées.

Les formules supposent également qu’il n’y a pas d’espaces supplémentaires dans vos données; au plus, il y a un seul espace après la virgule et entre l’état et le code postal. C’est, bien sûr, assez facile à appliquer: utilisez simplement Rechercher et remplacer pour remplacer deux espaces par un seul espace n’importe où dans votre feuille de calcul.

Bien sûr, si vos données sont aussi structurées, vous pouvez toujours compter sur l’outil Texte en colonnes pour faire votre travail. Tout ce que vous avez à faire est d’exécuter l’outil et de diviser vos données en fonction de la virgule. Cela laissera la ville dans une cellule et regroupera l’état et le code postal dans la cellule suivante. Ensuite, vous pouvez à nouveau utiliser Text to Columns, cette fois sur la deuxième cellule (pas le nom de la ville) et diviser le contenu en fonction de l’espace.

Si vos données ne sont pas aussi structurées – peut-être qu’elles comportent plusieurs virgules dans l’adresse ou des espaces supplémentaires – alors une approche entièrement différente est nécessaire. Pour résoudre ce problème, la technique de base consiste à rogner les données pour supprimer les espaces superflus (de début, de fin et internes), puis de déterminer l’emplacement du dernier espace et de l’avant-dernier espace.

Vous pouvez extraire les cinq chiffres du code postal, qui est défini comme suivant immédiatement le dernier espace des données, en utilisant cette formule:

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

L’abréviation d’état à deux caractères peut être retournée en retirant les deux caractères immédiatement après l’avant-dernier espace:

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

Si vos données sont encore moins structurées – peut-être qu’elles incluent des adresses qui n’ont pas toutes des abréviations d’état à deux caractères (N.J. au lieu de NJ) – alors il vaut mieux vous servir d’une macro pour diviser les données.

La raison en est que VBA dispose d’un ensemble de fonctions de gestion de texte beaucoup plus riche que ce que vous pouvez faire à l’aide de formules Excel. La macro suivante crée une fonction définie par l’utilisateur qui peut renvoyer l’état ou le code postal:

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

Pour utiliser cette fonction, il vous suffit de fournir une référence de cellule et soit 1 (si vous voulez l’état) ou 2 (si vous voulez le code postal). Voici un exemple de demande du code postal pour n’importe quelle adresse se trouvant dans la cellule A1:

=GetStateZIP(A1,2)

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (9598) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Extracting_a_State_and_a_ZIP_Code [Extraction d’un état et d’un code postal].