Formule générique
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Sommaire
Pour fractionner du texte à un délimiteur arbitraire (virgule, espace, tube, etc.), vous pouvez utiliser une formule basée sur les fonctions TRIM, MID, SUBSTITUTE, REPT et LEN. Dans l'exemple illustré, la formule en C5 est:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Remarque: les références à B5 et C4 sont des références mixtes pour permettre à la formule d'être copiée à travers et vers le bas.
Explication
L'essentiel de cette formule est de remplacer un délimiteur donné par un grand nombre d'espaces en utilisant SUBSTITUTE et REPT, puis utiliser la fonction MID pour extraire le texte lié à la "nième occurrence" et la fonction TRIM pour se débarrasser de l'espace supplémentaire.
Dans cet extrait de code, le délimiteur (délimiteur) est remplacé par un nombre d'espaces égal à la longueur totale de la chaîne:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Ensuite, la formule utilise la fonction MID pour extraire la nième sous-chaîne. Le point de départ est calculé avec le code ci-dessous, où N représente "nième":
(N-1)*LEN(A1)+1
Le nombre total de caractères extraits est égal à la longueur de la chaîne de texte intégral. La fonction TRIM supprime ensuite tous les espaces supplémentaires et renvoie uniquement la nième chaîne.
Extraire une seule instance
Bien que l'exemple soit configuré pour extraire 5 sous-chaînes du texte de la colonne B, vous pouvez facilement extraire une seule instance. Par exemple, pour extraire uniquement le 4ème élément (ville), vous pouvez utiliser:
=TRIM(MID(SUBSTITUTE(B5,"|",REPT(" ",LEN(B5))),(4-1)*LEN(B5)+1,LEN(B5)))
Fonction de texte en colonnes
Pour les conversions manuelles ponctuelles, Excel dispose d'une fonctionnalité intégrée appelée "Texte en colonnes" qui peut diviser le texte en cellules avec un délimiteur de votre choix. Vous trouverez cette fonctionnalité dans l'onglet Données du ruban dans la section Outils de données.
