STXTB : La formule Excel pour extraire du texte en fonction des octets
=STXTB(texte; no_depart; no_octets)La formule STXTB est une fonction avancée d'Excel permettant d'extraire une portion de texte en se basant sur le nombre d'octets plutôt que sur le nombre de caractères. Cette distinction est cruciale lorsque vous travaillez avec des caractères multi-octets, notamment des caractères accentués, des symboles spéciaux ou des textes en langues asiatiques (chinois, japonais, coréen). Contrairement à MID qui compte les caractères, STXTB offre un contrôle précis au niveau des octets, ce qui en fait un outil indispensable pour les professionnels manipulant des données internationales ou des encodages complexes. Cette fonction est particulièrement utile dans les domaines de l'importation de données, du traitement de fichiers CSV multilangues, ou de la manipulation de codes barre et identifiants spécialisés. En maîtrisant STXTB, vous gagnerez en précision et éviterez les erreurs d'extraction courantes liées à l'incompatibilité entre la longueur en caractères et la longueur en octets. Elle s'intègre parfaitement dans les workflows Excel avancés et complète efficacement les autres fonctions texte comme MID, LEFTB et RIGHTB.
Syntaxe et paramètres
La syntaxe de STXTB est structurée en trois paramètres essentiels : =STXTB(texte; no_depart; no_octets). Le premier paramètre 'texte' est obligatoire et représente la chaîne de caractères source à partir de laquelle vous souhaitez extraire une portion. Le deuxième paramètre 'no_depart' (start_num) définit la position de départ en octets, en commençant à 1 pour le premier octet. C'est ici que réside la principale différence avec MID : vous comptez en octets et non en caractères. Le troisième paramètre 'no_octets' (num_bytes) spécifie le nombre d'octets à extraire à partir de la position de départ. Pour bien comprendre cette distinction, sachez qu'un caractère ASCII standard occupe 1 octet, tandis qu'un caractère accentué ou un caractère asiatique peut occuper 2, 3 ou même 4 octets selon l'encodage utilisé. Par exemple, le caractère 'é' en UTF-8 occupe 2 octets. Si vous spécifiez une position de départ qui tombe au milieu d'un caractère multi-octet, Excel ajustera automatiquement l'extraction. Il est recommandé de tester vos formules avec des données réelles et d'utiliser la fonction LENB pour connaître la longueur exacte en octets de vos textes. Attention : si le nombre d'octets dépasse la longueur disponible, la fonction retourne simplement le texte restant sans erreur.
textstart_numnum_bytesExemples pratiques
Extraction d'un code produit avec caractères accentués
=STXTB("Café-Premium-2024"; 1; 15)Cette formule extrait 15 octets à partir du début de la chaîne. Le mot 'Café' contient 5 octets (C=1, a=1, f=1, é=2), donc l'extraction capturera 'Café-Premium-20' en tenant compte de l'octet supplémentaire du 'é'.
Extraction d'une portion de texte multilangue (Chinois)
=STXTB(A1; 1; 9)Si A1 contient '北京市朝阳区', chaque caractère chinois occupe 3 octets. En spécifiant 9 octets, vous obtenez exactement les 3 premiers caractères '北京市'. Cette approche garantit une extraction précise indépendamment du nombre de caractères.
Extraction d'une portion intermédiaire avec position de départ variable
=STXTB(B2; 16; 7)Cette formule commence l'extraction au 16ème octet et en extrait 7. En comptant les octets de 'INV-2024-001-', vous atteignez exactement la position de 'DETAILS'. Cette technique est utile pour parser des formats de données fixes.
Points clés à retenir
- STXTB compte les octets et non les caractères, ce qui la rend indispensable pour les données multilangues et les caractères spéciaux
- Utilisez LENB pour connaître la longueur exacte en octets et FINDB pour localiser les positions en octets dans vos textes
- La position de départ doit toujours être ≥ 1 et le nombre d'octets doit être un entier positif pour éviter les erreurs #VALUE!
- Testez toujours vos formules avec des données réelles incluant des caractères accentués ou asiatiques pour valider le comportement
- Combinez STXTB avec IFERROR pour une meilleure gestion des erreurs et une robustesse accrue dans les workflows de production
Astuces de pro
Utilisez LENB systématiquement pour vérifier la longueur en octets avant de construire vos formules STXTB. Créez une colonne auxiliaire avec =LENB(A1) pour déboguer et valider vos calculs de positions.
Impact : Réduit les erreurs de calcul et vous fait gagner du temps lors du développement. Vous évitez les extractions partielles ou incorrectes liées à des positions mal calculées.
Testez toujours vos formules STXTB avec des données réelles incluant des caractères accentués ou multilangues. Les résultats peuvent surprendre si votre texte de test était uniquement ASCII.
Impact : Garantit que vos formules fonctionnent correctement en production. Évite les déploiements de formules qui échouent silencieusement avec certains types de données.
Combinez STXTB avec des fonctions de validation comme COUNTIF pour vérifier que vous extrayez bien les bonnes portions. Créez des assertions simples dans des colonnes adjacentes.
Impact : Permet une validation croisée rapide et augmente la confiance dans vos résultats. Facilite le débogage en cas de résultat inattendu.
Documentez vos formules STXTB complexes avec des commentaires Excel. Notez le nombre d'octets attendus et la raison du calcul de position pour faciliter la maintenance future.
Impact : Améliore la maintenabilité du classeur et aide les autres utilisateurs à comprendre votre logique. Réduit les risques de modifications incorrectes ultérieures.
Combinaisons utiles
STXTB + FINDB pour extraction conditionnelle
=STXTB(A1; FINDB("|"; A1) + 1; 10)Cette combinaison trouve d'abord la position en octets du caractère '|' avec FINDB, puis extrait 10 octets à partir de la position suivante. Parfait pour parser des formats délimités où vous devez extraire après un séparateur spécifique.
STXTB + LENB pour extraction jusqu'à la fin
=STXTB(A1; FINDB("-"; A1); LENB(A1) - FINDB("-"; A1) + 1)Extrait tout le texte après le premier tiret jusqu'à la fin. LENB calcule la longueur totale, FINDB localise le tiret, et STXTB extrait tout ce qui suit. Utile pour diviser des identifiants composés.
STXTB + IFERROR pour gestion d'erreurs robuste
=IFERROR(STXTB(A1; B1; C1); "Extraction échouée")Encapsule STXTB dans IFERROR pour afficher un message personnalisé si l'extraction échoue. Améliore la robustesse et la lisibilité des rapports en cas de données invalides ou de positions hors limites.
Erreurs courantes
Cause : Le paramètre 'no_depart' ou 'no_octets' est négatif, zéro, ou n'est pas un nombre. Excel ne peut pas traiter des positions ou des longueurs invalides.
Solution : Vérifiez que vos paramètres numériques sont des entiers positifs. Utilisez MAX(1; votre_valeur) pour vous assurer que la position démarre à 1 minimum. Testez : =STXTB(A1; 1; 5) au lieu de =STXTB(A1; -1; 5).
Cause : La cellule référencée (texte source) n'existe pas ou a été supprimée. Cela se produit souvent lors de la suppression de colonnes ou de la modification de la structure du classeur.
Solution : Vérifiez que la cellule source existe et contient des données. Utilisez des références nommées ou absolues ($A$1) pour plus de stabilité. Recalculez le classeur avec Ctrl+Maj+F9.
Cause : La position de départ spécifiée dépasse la longueur totale en octets du texte source. Par exemple, demander 50 octets à partir de la position 100 dans un texte de 80 octets.
Solution : Utilisez LENB pour connaître la longueur exacte : =IF(no_depart > LENB(texte); "Position invalide"; STXTB(texte; no_depart; no_octets)). Validez vos positions avant d'appeler STXTB.
Checklist de dépannage
- 1.Vérifiez que le paramètre 'texte' n'est pas vide et contient réellement des données avec =ISBLANK(A1)
- 2.Confirmez que 'no_depart' est un nombre entier positif ≥ 1 en utilisant =ISNUMBER(B1) et =B1>=1
- 3.Validez que 'no_octets' est un nombre positif et ne dépasse pas LENB(texte) - no_depart + 1
- 4.Utilisez LENB pour comparer la longueur attendue en octets avec la longueur réelle du résultat
- 5.Testez la formule avec des valeurs en dur (littérales) avant d'utiliser des références de cellules pour isoler les problèmes
- 6.Vérifiez l'encodage du fichier Excel (UTF-8 vs ANSI) qui peut affecter le calcul des octets pour les caractères spéciaux
Cas particuliers
Texte contenant des caractères combinés (accents diacritiques séparés)
Comportement : Certains caractères accentués peuvent être représentés comme un caractère de base + un accent combiné, modifiant le calcul d'octets. STXTB compte tous les octets y compris les diacritiques séparés.
Solution : Utilisez la normalisation Unicode (UNICODENORM en VBA) avant d'appliquer STXTB pour harmoniser la représentation des caractères accentués.
Cet edge case est rare mais peut causer des résultats inattendus avec certains textes en langues latines étendues.
Position de départ tombant au milieu d'un caractère multi-octet
Comportement : Si vous spécifiez une position qui coupe un caractère multi-octet (ex: position 2 pour un caractère de 3 octets), Excel ajuste automatiquement pour commencer au caractère suivant complet.
Solution : Utilisez FINDB pour localiser des positions exactes plutôt que de calculer manuellement. Validez toujours vos résultats avec LENB.
Ce comportement d'ajustement automatique peut masquer des erreurs de logique. Testez vos formules attentivement.
Classeur avec encodage ANSI au lieu de UTF-8
Comportement : Les calculs d'octets varient selon l'encodage. Un caractère accentué en ANSI peut occuper 1 octet tandis qu'en UTF-8 il en occupe 2, produisant des résultats différents.
Solution : Vérifiez l'encodage de votre fichier (Fichier > Options > Avancé > Encodage par défaut). Convertissez en UTF-8 si vous travaillez avec du texte multilangue pour cohérence.
L'encodage est une source fréquente de confusion lors du partage de classeurs entre systèmes différents.
Limitations
- •STXTB n'est disponible que dans Excel (2007+) et LibreOffice, pas dans Google Sheets. Les utilisateurs de Google Sheets doivent utiliser MID ou des alternatives.
- •La fonction ne fonctionne qu'avec du texte et ne peut pas traiter directement les nombres. Convertissez les nombres en texte avec TEXT() si nécessaire : =STXTB(TEXT(A1;"0"); 1; 5)
- •Les performances peuvent se dégrader avec des formules STXTB complexes appliquées à des millions de lignes. Utilisez des colonnes auxiliaires ou le calcul en arrière-plan pour optimiser.
- •STXTB n'offre pas de support natif pour les expressions régulières. Pour des extractions complexes basées sur des motifs, vous devrez combiner plusieurs fonctions ou utiliser VBA/Power Query.
Alternatives
Plus simple et plus couramment utilisée, compte les caractères plutôt que les octets. Interface plus intuitive pour les textes ASCII standards.
Quand : Idéale pour extraire du texte en anglais ou sans caractères spéciaux. Moins précise pour les données multilangues mais suffisante pour la plupart des cas simples.
Compatibilité
✓ Excel
Depuis 2007
=STXTB(texte; no_depart; no_octets)✗Google Sheets
Non disponible
✓LibreOffice
=STXTB(texte; no_depart; no_octets)