Remote extra commas from Column

**satputenandkumar0** · 07-21-2014, 08:24 AM

Hi members,

I am looking for VBA code to remove extra commas from d column.

Missing

, , , 1617614,

, , , 1676652,

, , , , 1676664, 1676665, 1676666, , 1676668, , 1676670

, , 1591813,,,,

, , , 1591113,

your help may appreciated.

Regards,
Nandkumar S.

**nathansav** · 07-21-2014, 08:31 AM

Please Login or Register  to view this content.

Not tested as not in Excel.

**martindwilson** · 07-21-2014, 08:35 AM

its easy enough with just a formula
=SUBSTITUTE(TRIM(SUBSTITUTE(d2,","," "))," ",", ")

**nathansav** · 07-21-2014, 08:36 AM

Nice one Marin

**satputenandkumar0** · 07-21-2014, 08:36 AM

Dear nathansav,

Thanks for reply. It is showing 'Syntax error' on below line

Please Login or Register  to view this content.

Regards,
Nandkumar S.

**nathansav** · 07-21-2014, 08:39 AM

strOP = strOP & "," & a(i)

You can have a go at fixes yourself

**satputenandkumar0** · 07-21-2014, 08:41 AM

Dear Martin,

It works perfect. But I am looking for VBA code because I am getting these values (in D column) with macro.

**martindwilson** · 07-21-2014, 08:46 AM

or use the worksheet function

Please Login or Register  to view this content.

**satputenandkumar0** · 07-21-2014, 08:48 AM

Dear nathansav,

It is not showing error now, but clearing all contain.

Please find attached file

Regards,
Nandkumar S.

**satputenandkumar0** · 07-21-2014, 08:54 AM

Thanks Martin,

Your solution works like a charm.
Thanks nathansav

Regards,
Nandkumar S.

Re: Remote extra commas from Column