Concatenation si cellules non vides

[antiphot]

Bonjour à toutes et à tous

Ma question du jour s'addresse aux formulistes. J'ai dans une colonne (disons A) des valeurs de A1 à A5. Je désire concatener ces valeurs en B1. Jusque là pas de pb. Mon souçi est que dans ces 5 cellules, certaines peuvent être vides.
Je ne désire concatener bien sûr que celles qui contiennent une valeur.

J'ai essayé avec la condition Si mais vu le nombre de cas c'est la galère.

Je pense qu'il doit y avoir moyen de faire plus simple peut-être avec une fonction matricielle ?

Je sais que la solution serait simple en vba, mais dans ce cas préçis, il me faut une formule

merci par avance à tous les formulistes qui voudront bien se pencher sur ma question

 

[nat54]

Re : Concatenation si cellules non vides

bonjour,

peut-être en s'appuyant sur quelque chose de similaire
(fonction Excel à créer)

Code :

Function ConcatPlage(plage As Range, séparateur As String, Optional contenant As String) As String
Dim rep As String, c As Range
For Each c In plage
If InStr(c.Value, contenant) > 0 Then
rep = rep & c.Value & séparateur
End If
Next c
ConcatPlage = Left(rep, Len(rep) - Len(séparateur))
End Function


Mise en œuvre : ALT+F11, Insertion/Module, copier la fonction ConcatPlage ci-dessus, ALT+Q

Mise en application :
=ConcatPlage(la_plage;"le_symbole_séparateur","condition_optionnelle)

Exemples

* plage [A1:A20] = 1 à 20, concatener les expressions contenant "2"
=ConcatPlage(A1:A20;"#";2)
Résultat : 2#12#20
en adaptant avec nval=0

* plage [A1:A20] = 1 à 20, concatener toutes les expressions
=ConcatPlage(A1:A20;"#")
Résultat: 1#2#3#4#5#6#7#8#9#10#11#12#13#14#15#16#17#18#19#20

 

[pierrejean]

Re : Concatenation si cellules non vides

bonjour voisin

A priori si les cellules sont vides il n'y a pas probleme
=A1&A2&A3&A4&A5 devrait convenir
Si par contre il y a des espaces utiliser SUPPRESPACE
Si rien de ceci ne convient un petit fichier avec un exemple serait judicieux

Salut nat54

 

[antiphot]

Re : Concatenation si cellules non vides

Bonjour Nat54

J'ai testé ta solution et ce compromis formule vba me convient tout à fait, Merci !

Bonjour voisin

Le pb c'est que si les cellules sont vides il m'affiche le symbole séparateur "-" car la formule dans ce cas est A1&"-"&A2&"-"&A3&"-"&A4&"-"A5. Mais il est vrai que j'ai omis de le mentionner dans mon premier post

 

[pierrejean]

Re : Concatenation si cellules non vides

Re

Pour le fun

=SI(DROITE(SI(A1="";"";A1&"-")&SI(A2="";"";A2&"-")&SI(A3="";"";A3&"-")&SI(A4="";"";A4&"-")&SI(A5="";"";A5&"-");1)="-";GAUCHE(SI(A1="";"";A1&"-")&SI(A2="";"";A2&"-")&SI(A3="";"";A3&"-")&SI(A4="";"";A4&"-")&SI(A5="";"";A5&"-");NBCAR(SI(A1="";"";A1&"-")&SI(A2="";"";A2&"-")&SI(A3="";"";A3&"-")&SI(A4="";"";A4&"-")&SI(A5="";"";A5&"-"))-1);SI(A1="";"";A1&"-")&SI(A2="";"";A2&"-")&SI(A3="";"";A3&"-")&SI(A4="";"";A4&"-")&SI(A5="";"";A5&"-"))

 

[nat54]

Re : Concatenation si cellules non vides

tu t'es amusé là Pierrejean

@ antiphot : tu as utilisé quelle solution finalement ?
car ma proposition était à adapter

 

[antiphot]

Re : Concatenation si cellules non vides

Alors là chapeau pierrejean ! moi je n'ai pas eu le patience.
J'ai donc effectivement choisi la solution de Nat54 que j'ai adaptée.
Merci à vous deux
Philippe

 

[hoerwind]

Re : Concatenation si cellules non vides

Bonjour, salut les autres,

Une variante un tantinet plus courte :
=SUBSTITUE(SUPPRESPACE(A1&" "&A2&" "&A3&" "&A4&" "&A5);" ";"-")

Ou bien en faisant appel à une fonction de Morefunc :
=MCONCAT(A1:A5;"-")

 

[Jocelyn]

Re : Concatenation si cellules non vides

Bonjour le Forum,
Bonjour antiphot, nat54, l'ami pierrejean,

alors en formule une plus courte pour pierrejean,

=SUBSTITUE(SUPPRESPACE(A1&" "&A2&" "&A3&" "&A4&" "&A5);" ";"-")

Jocelyn

Oup's Bonjour hoerwind, désolé pour la collision, et je n'ais pas copié

 

[pierrejean]

Re : Concatenation si cellules non vides

Re

toujours pour le fun (et un peu plus court)

=SI(DROITE(SUBSTITUE(A1& "-" &A2& "-" & A3 &"-"&A4 &"-"&A5;"--";"-");1)="-";GAUCHE(SUBSTITUE(A1& "-" &A2& "-" & A3 &"-"&A4 &"-"&A5;"--";"-");NBCAR(SUBSTITUE(A1& "-" &A2& "-" & A3 &"-"&A4 &"-"&A5;"--";"-"))-1);SUBSTITUE(A1& "-" &A2& "-" & A3 &"-"&A4 &"-"&A5;"--";"-"))

arf !!!
les formulistes arrivent
Salut Jocelyn
Salut hoerwind

 

[nat54]

Re : Concatenation si cellules non vides

Bonjour le Forum,
Bonjour antiphot, nat54, l'ami pierrejean,

alors en formule une plus courte pour pierrejean,

=SUBSTITUE(SUPPRESPACE(A1&" "&A2&" "&A3&" "&A4&" "&A5);" ";"-")

Jocelyn

Oup's Bonjour hoerwind, désolé pour la collision, et je n'ais pas copié

comment elle marche cette formule ? je ne la comprends pas .. or je retiens qu'en comprenant

 

[Jocelyn]

Re : Concatenation si cellules non vides

Coucou Nat54,

Dans un premier temps on fait la concaténation de A1:A5 en intercalant un espace entre chaque valeur

Si une cellule est vide on se trouve avec 2 espaces qui se suive donc la fonction SUPPRESPACE() va enlever les espaces en trop

ensuite grace a la fonction SUBSTITUE() on remplace les espaces par des tirets

Voila j'espère avoir été clair autrement n'hésite pas

Jocelyn

 

[nat54]

Re : Concatenation si cellules non vides

ok merci

 

[antiphot]

Re : Concatenation si cellules non vides

Bonjour à toutes et à tous et meilleurs voeux 2010.

Je fais remonter un vieux post de 2009 concernant un problème de concaténation. La formule de Jocelyn est intérressante mais peux-t'on l'adapter de la façon suivante ?

=SUBSTITUE(SUPPRESPACE(A1&" "&A2&" "&A3&" "&A4&" "&A5);" ";"-")

sachant que A1 = 1 x 2 L Flacon et A2 = 1 x 5 L Bouteille et ainsi de suite. La formule ci-dessus va donner:

1-x-2-L-Flacon-1-x-5-L-Bouteille hors moi je désire garder les espaces des textes des cellules A1 A2... etc de façon à avoir (si les cellules non vides):

1 x 2 L Flacon-1 x 5 L Bouteille-etc....

PS: J'ignore si cela se fait de deterrer des aussi vieux sujets auquel cas je relançerai un nouveau post

Philippe

 

[Jocelyn]

Re : Concatenation si cellules non vides

Bonjour antiphot et meilleurs voeux 2010

un petit bricolage

=SUBSTITUE(SUBSTITUE(SUPPRESPACE(SUBSTITUE(A1;" ";"/")&" "&SUBSTITUE(B1;" ";"/")&" "&SUBSTITUE(C1;" ";"/")&" "&SUBSTITUE(D1;" ";"/"));" ";" - ");"/";" ")

ici on commence par concaténer A11 en remplacant les espace par des / ensuite on supprime les espace en trop si il y a des cellule vide puis on subsitue les espaces par " - " et pour finir on substitue les / par des espaces

voir le fichier joint

Jocelyn