Excel - remove duplicate commas in a cell

[WingandaPrayer]

Hi all,

I'm looking to take out the commas from a cell except for the first instance? I couldn't find anything via the keyword search.
e.g.
10, Downing Street, London, SW1A 2AA
to
10, Downing Street London SW1A 2AA

Many thanks.

 

[BlueHorizon]

Hi,
One way to try is to use Text to Columns to separate at each comma, which results in the following:

10 Downing Street London SW1A 2AA
11 Downing Street London SW1A 2AA
12 Downing Street London SW1A 2AA
13 Downing Street London SW1A 2AA
14 Downing Street London SW1A 2AA


Then use Concatenate to re-join. Assuming a start in cell A1:

=CONCATENATE(A1,",",B1," ",C1,D1)

Results in: 10, Downing Street London SW1A 2AA

Hope this helps,


Best,
Blue Horizon

 

[xlhelp]

If your text is in A1 then
[blue]=LEFT(A1,FIND(",",A1))&SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(",",A1)),",","")[/blue]


I don't need Google, my family knows everything

 

[WingandaPrayer]

Thanks both for the different solutions.