Excel — Retirer les « vides » d’un tableau déversé (spilled array) : TRIM, NBSP, FILTER & TOCOL

Les « vides » dans un tableau déversé Excel ne sont pas toujours de vrais blancs : ils cachent souvent des espaces (classiques ou insécables). Voici un guide complet, pratique et prêt à copier-coller, pour les supprimer proprement dans toutes les situations courantes.

Sommaire

Vue d’ensemble

Objectif : retirer les entrées vides d’un spilled array sans casser le déversement ni l’ordre des données.

Le réflexe =FILTER(t, t<>"") échoue dès que vos « vides » contiennent un espace visible (" ") ou invisible (NBSP CHAR(160)) hérité d’une scission de texte, d’un collage Web ou d’un import. Pour un nettoyage fiable, on normalise d’abord le contenu (suppression des espaces superflus et des NBSP), puis on applique le filtre.

Solutions immédiates

Cas A — Vrais vides (cellules réellement vides ou renvoyant `""`)

=FILTER(G5:G17, G5:G17&lt;&gt;"")

Cette formule conserve toutes les cellules non vides. Elle suffit si vos blancs sont de « vrais vides » (cellule vide ou formule renvoyant "").

Cas B — Faux vides dus aux espaces (y compris NBSP)

Normalisez avant de tester :

=FILTER(G5:G17, LEN(TRIM(SUBSTITUTE(G5:G17, CHAR(160), ""))) &gt; 0)

TRIM supprime les espaces redondants (en tête, en queue et multiples internes).
SUBSTITUTE(…, CHAR(160), "") enlève l’espace insécable (NBSP) souvent présent dans les textes copiés.
LEN(…) > 0 élimine ce qui devient vide après nettoyage.

Variante générique pour un tableau déversé quelconque t :

=LET(t, formule_qui_renvoie_le_tableau,
     FILTER(t, LEN(TRIM(SUBSTITUTE(t, CHAR(160), ""))) &gt; 0))

Cas C — Vous scindez une chaîne en caractères et voulez supprimer les espaces

Filtrer après extraction des caractères :

=LET(s, F3,
     FILTER(MID(s, SEQUENCE(LEN(s)), 1),
            MID(s, SEQUENCE(LEN(s)), 1) &lt;&gt; " "))

Supprimer les espaces avant l’extraction :

=LET(t, SUBSTITUTE(F3, " ", ""),
     MID(t, SEQUENCE(LEN(t)), 1))

Cas D — Vous scindez un texte en tokens (mots/valeurs) avec des délimiteurs

Utilisez TEXTSPLIT et ignorez les vides créés par des délimiteurs contigus :

=TEXTSPLIT(A1, ",", , TRUE)

(Le 4ᵉ argument TRUE = ignore_empty.)

Besoin de nettoyage (espaces, NBSP) avant la scission ?

=LET(x, SUBSTITUTE(A1, CHAR(160), ""),
     TEXTSPLIT(TRIM(x), ",", , TRUE))

Cas E — Tableau 2D à aplatir en une colonne sans vides

Si vos vides sont de vrais blancs :

=TOCOL(G5:J17, 1)

Si des « vides » contiennent des espaces :

=LET(x, TRIM(SUBSTITUTE(G5:J17, CHAR(160), "")),
     TOCOL(x, 1))

Tableau de synthèse

Situation	Formule recommandée	Pourquoi
Vrais vides	`=FILTER(t, t<>"")`	Simple et rapide ; pas de nettoyage nécessaire.
Faux vides (espaces, NBSP)	`=FILTER(t, LEN(TRIM(SUBSTITUTE(t, CHAR(160), "")))>0)`	Normalise avant de filtrer, cover NBSP.
Split en caractères sans espaces	`=FILTER(MID(s, SEQUENCE(LEN(s)), 1), MID(s, SEQUENCE(LEN(s)), 1)<>" ")`	Écarte les espaces au niveau du caractère.
Split en tokens (CSV, etc.)	`=TEXTSPLIT(A1, ",", , TRUE)`	Ignorer automatiquement les champs vides.
Aplatir 2D & ignorer vides	`=TOCOL(t, 1)`	Compacte en colonne en sautant les vides.
Aplatir 2D & faux vides	`=LET(x, TRIM(SUBSTITUTE(t, CHAR(160), "")), TOCOL(x, 1))`	Nettoie NBSP puis compactage.

Nettoyage avancé (Unicode, NBSP, tabulations, retours)

Le NBSP (CHAR(160)) n’est pas le seul « espace ». D’autres caractères blancs peuvent polluer vos données : tabulation (CHAR(9)), retours (CHAR(10), CHAR(13)), espace fine insécable (UNICHAR(8239)), etc. Créez une fonction réutilisable qui uniformise tout cela.

Créer une fonction LAMBDA de nettoyage

Dans le gestionnaire de noms, créez CLEANSPACE avec :

=LAMBDA(x, 
  LET(
    s, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
         x, CHAR(160), " "), UNICHAR(8239), " "), CHAR(9), " "), CHAR(10), " "), CHAR(13), " "),
    TRIM(s)
  )
)

Ensuite, appliquez-la à votre tableau t avant filtrage :

=LET(u, CLEANSPACE(t),
     FILTER(u, LEN(u)&gt;0))

Et pour aplatir un 2D en colonne propre :

=LET(u, CLEANSPACE(G5:J17),
     FILTER(TOCOL(u, 0), LEN(TOCOL(u, 0))&gt;0))

Remarque : si votre version propose MAP, vous pouvez écrire =MAP(t, CLEANSPACE) pour nettoyer cellule par cellule.

Filtrage par lignes ou colonnes dans un tableau 2D

Besoin de supprimer les lignes entièrement vides (après nettoyage), tout en conservant les autres ? Utilisez BYROW pour générer un masque logique ligne par ligne.

=LET(
  u, TRIM(SUBSTITUTE(G5:J17, CHAR(160), "")),
  FILTER(G5:J17, BYROW(u, LAMBDA(r, SUM(LEN(r))&gt;0)))
)

Explication : on nettoie en amont (u). Pour chaque ligne r, on somme les longueurs LEN. Si la somme > 0, la ligne n’est pas vide et on la garde.

Pour filtrer les colonnes vides, remplacez BYROW par BYCOL :

=LET(
  u, TRIM(SUBSTITUTE(G5:J17, CHAR(160), "")),
  FILTER(G5:J17, BYCOL(u, LAMBDA(c, SUM(LEN(c))&gt;0)))
)

Conserver l’ordre, trier, dédupliquer

Conserver l’ordre d’origine : toutes les formules ci-dessus préservent l’ordre de t.
Trier après nettoyage :

=LET(u, TRIM(SUBSTITUTE(G5:G17, CHAR(160), "")),
     SORT(FILTER(u, LEN(u)&gt;0)))

Supprimer les doublons après nettoyage :

=LET(u, TRIM(SUBSTITUTE(G5:G17, CHAR(160), "")),
     UNIQUE(FILTER(u, LEN(u)&gt;0)))

Split multi-délimiteurs et vides contigus

TEXTSPLIT accepte plusieurs délimiteurs :

=LET(s, TRIM(SUBSTITUTE(A1, CHAR(160), "")),
     TEXTSPLIT(s, {",",";","|"}, , TRUE))

Ici on nettoie d’abord les NBSP, puis on scinde par virgule, point-virgule ou barre verticale, en ignorant les éléments vides (TRUE).

Cas pratiques de bout en bout

Nettoyer et empiler plusieurs colonnes

Vous avez trois colonnes A:C avec des lignes partiellement vides et des NBSP, et vous voulez une liste unique propre :

=LET(
  t, A2:C500,
  u, TRIM(SUBSTITUTE(t, CHAR(160), "")),
  l, TOCOL(u, 0),
  FILTER(UNIQUE(l), LEN(l)&gt;0)
)

Nettoyer, trier décroissant, ignorer erreurs

=LET(
  u, TRIM(SUBSTITUTE(G5:G17, CHAR(160), "")),
  v, FILTER(u, LEN(u)&gt;0),
  w, IFERROR(v, ""),
  SORT(w, 1, -1)
)

Compacter une plage 2D en respectant une condition

Par exemple : « garder seulement les valeurs > 0 après nettoyage » :

=LET(
  u, TRIM(SUBSTITUTE(G5:J17, CHAR(160), "")),
  a, TOCOL(u, 0),
  FILTER(a, (LEN(a)&gt;0)*(--a&gt;0))
)

Astuce : --a force une conversion numérique ; utilisez-la uniquement si a contient bien des nombres après nettoyage.

Diagnostic : pourquoi `ISBLANK` « ne voit pas » mes vides ?

ISBLANK(A1) ne détecte pas une cellule où la formule renvoie "".
A1="" fonctionne pour capter ces chaînes vides, mais pas les NBSP.
LEN(TRIM(SUBSTITUTE(A1, CHAR(160), "")))>0 couvre le cas général.

Compatibilité & alternatives (sans fonctions dynamiques)

Les fonctions FILTER, LET, SEQUENCE, TEXTSPLIT, TOCOL, BYROW/BYCOL, MAP exigent Microsoft 365 / Excel 2021+. Pour des versions antérieures, une approche « indexée » est possible :

Dans une colonne auxiliaire, nettoyez : =TRIM(SUBSTITUTE(A2, CHAR(160), "")).
Dans la liste résultat (par ex. F2), utilisez :

=IFERROR(
  INDEX($A$2:$A$100,
    AGGREGATE(15, 6,
      ROW($A$2:$A$100)-ROW($A$2)+1 /
      (LEN(TRIM(SUBSTITUTE($A$2:$A$100, CHAR(160), "")))&gt;0),
      ROWS($F$2:F2)
    )
  ),
"")

Recopiez vers le bas jusqu’à obtenir des vides. Cette formule sélectionne la k-ième position où la condition est vraie (longueur > 0 après nettoyage).

Performance & bonnes pratiques

Nettoyez au plus tôt : normalisez dès la source (Power Query, TEXTSPLIT, etc.) pour éviter des chaînes « bizarres » en aval.
LET factorise les calculs et accélère les grands modèles.
Évitez les calculs répétés comme TRIM(SUBSTITUTE(...)) plusieurs fois ; stockez une fois dans une variable u.
Contrôlez les types : si vous combinez texte et nombres, explicitez vos conversions (--val ou VALUE).
Colonnes auxiliaires : quand les plages sont massives, un nettoyage « matérialisé » peut être plus lisible et performant qu’un tout-en-un.

Erreurs fréquentes… et comment les éviter

Confondre vide et espace : un espace visible «  » n’est pas un vide. Passez toujours par TRIM + SUBSTITUTE (et, si nécessaire, UNICHAR).
Filtrer cellule par cellule avec FILTER en 2D : FILTER opère par lignes ou colonnes, pas par cellule individuelle. Pour retirer des vides partout, TOCOL puis FILTER ou LET + BYROW/BYCOL.
Perdre les zéros : un 0 est une valeur utile ; nos conditions basées sur LEN conservent correctement "0" ou 0.
Paramètres régionaux : si votre Excel utilise le point-virgule, remplacez , par ; dans toutes les formules.

Exemples supplémentaires « copier-coller »

Masquer les lignes totalement vides après split

=LET(
  s, TRIM(SUBSTITUTE(A2:A50, CHAR(160), "")),
  x, TEXTSPLIT(TEXTJOIN(",", TRUE, s), ",", , TRUE),
  FILTER(x, LEN(x)&gt;0)
)

Ici, on concatène, on scinde, puis on filtre les vides : idéal pour normaliser une colonne contenant des listes séparées par virgule.

Nettoyer, déverser et ré-ordonner comme dans une liste « compactée »

=LET(
  t, B2:D200,
  u, TRIM(SUBSTITUTE(t, CHAR(160), "")),
  l, TOCOL(u, 0),
  r, FILTER(l, LEN(l)&gt;0),
  SORT(UNIQUE(r))
)

Ne garder que les cellules non vides d’une colonne structurée (Tables)

Avec une Table nommée Ventes et sa colonne Client :

=LET(u, TRIM(SUBSTITUTE(Ventes[Client], CHAR(160), "")),
     FILTER(u, LEN(u)&gt;0))

FAQ express

Q : Pourquoi TRIM ne supprime-t-il pas tout ?
R : TRIM retire les espaces classiques, pas le NBSP (CHAR(160)) ni certaines variantes Unicode ; d’où l’intérêt de SUBSTITUTE + éventuellement UNICHAR.

Q : Puis-je utiliser CLEAN ?
R : CLEAN supprime les caractères non imprimables (ASCII 0–31), mais pas les espaces insécables. Combinez avec TRIM/SUBSTITUTE.

Q : Et si je veux garder un espace interne unique (ex. « Jean Dupont »), mais retirer les valeurs vides ?
R : TRIM réduit les espaces multiples à un seul ; en général, c’est souhaitable. Si ce n’est pas le cas, remplacez seulement les NBSP (SUBSTITUTE(…,CHAR(160),"")) sans TRIM, puis testez LEN.

Checklist de mise en œuvre

Identifier la nature du « vide » (vrai blanc, espace, NBSP, retours…)
Nettoyer : SUBSTITUTE (NBSP) → TRIM (espaces) → optionnel CLEAN (contrôle non imprimables)
Filtrer via LEN(…)>0 (standard)
Aplatir avec TOCOL si nécessaire
Optionnel : SORT, UNIQUE, regroupements

Rappels utiles

ISBLANK() ne détecte pas les cellules dont la formule renvoie "".
Les fonctions dynamiques (FILTER, LET, SEQUENCE, TEXTSPLIT, TOCOL) exigent Excel Microsoft 365 / 2021+.
Selon vos paramètres régionaux, remplacez la virgule par le point-virgule dans les formules.

Exemples finalisés par cas (résumé)

Cas A — Vrais vides

=FILTER(G5:G17, G5:G17&lt;&gt;"")

Cas B — Faux vides (espaces & NBSP)

=FILTER(G5:G17, LEN(TRIM(SUBSTITUTE(G5:G17, CHAR(160), ""))) &gt; 0)

=LET(t, formule_qui_renvoie_le_tableau,
     FILTER(t, LEN(TRIM(SUBSTITUTE(t, CHAR(160), ""))) &gt; 0))

Cas C — Scission en caractères

=LET(s, F3,
     FILTER(MID(s, SEQUENCE(LEN(s)), 1),
            MID(s, SEQUENCE(LEN(s)), 1) &lt;&gt; " "))

=LET(t, SUBSTITUTE(F3, " ", ""),
     MID(t, SEQUENCE(LEN(t)), 1))

Cas D — Scission en tokens

=TEXTSPLIT(A1, ",", , TRUE)

=LET(x, SUBSTITUTE(A1, CHAR(160), ""),
     TEXTSPLIT(TRIM(x), ",", , TRUE))

Cas E — Aplatir 2D sans vides

=TOCOL(G5:J17, 1)

=LET(x, TRIM(SUBSTITUTE(G5:J17, CHAR(160), "")),
     TOCOL(x, 1))

Aller plus loin : préparer des « briques » réutilisables

Créez deux noms définis pratiques :

CLEANSPACE(x) : la LAMBDA plus haut, pour normaliser un texte.
NONVIDE(x) : =LAMBDA(x, LEN(x)>0) — après nettoyage, à utiliser comme critère.

Exemple :

=LET(u, CLEANSPACE(G5:G17),
     FILTER(u, NONVIDE(u)))

Exigences régionales

Dans de nombreux Excel francophones, remplacez les virgules , par des points-virgules ;. Par exemple :

=FILTER(G5:G17; LEN(TRIM(SUBSTITUTE(G5:G17; CHAR(160); ""))) &gt; 0)

Avec ces patrons, vous disposez d’un arsenal complet pour assainir vos tableaux déversés dans Excel : nettoyage (NBSP, tabulations, retours), filtrage robuste, aplatissage 2D, tri et déduplication — le tout en conservant la logique « spilled ». Copiez, adaptez, et gagnez en fiabilité.