How to include a ‘range’ in a Substitute formula?

[aussitino]

Hi all,
Looking for some help on the following:

1-How can I include a ‘range’ in a Substitute formula?
the following doesn't seem to work (=SUBSTITUTE(A1:A4;"chien";"");"chat";"");"poule";"");", ";"")
Thus the solution found so far is based on copying the cells downwards (see below example)

2-how to list the results in a table such that there are no empty cells between the answers,
for long ranges this is would be especially helpful.

thnks
Tino

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]formula[/TD]
[TD]result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]chien, well done![/TD]
[TD]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"chien";"");"chat";"");"poule";"");", ";"")[/TD]
[TD]well done![/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]chien, chat, poule[/TD]
[TD]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2;"chien";"");"chat";"");"poule";"");", ";"")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]

chat, poule, super course

<tbody>
</tbody>

[/TD]
[TD]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3;"chien";"");"chat";"");"poule";"");", ";"")[/TD]
[TD]

super course

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]chien, poule[/TD]
[TD]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4;"chien";"");"chat";"");"poule";"");", ";"")[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Roderick_E]

Looks like you'll need some IF statements since you are trying to reference more than one cell

 

[steve the fish]

You would just use:

=SUBSTITUTE(A1;"chien";"");"chat";"");"poule";"");", ";"")

then drag the formula down

 

[aussitino]

hi Steve,
thnks for your input,
tried it, but seems however formula is only working when including the word 'substitute' for each of the words 'chien, chat, poule & 'comma'. thus 5x
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"chien";"");"chat";"");"poule";"");", ";"")
then dragging the formula down provides the results indeed.
in view of long lists with answers, I'm looking however for a formula without draggging.
& preferably for a formula which does not include the word substitute for each of the other words
thnks
Tino

 

[aussitino]

hi Roderick,
Could you help me with an example of such an IF statement?
thnks
Tino