I would like to remove trailing zeros from excel. But when i try to use substitute function i get function not defined error -

if use trim replace function mycell.value = trim(replace(mycell.value, chr(160), chr(32))) removes leading zeros.

sub trimtext()     dim mycell range     on error resume next         selection.cells.specialcells(xlcelltypeconstants, 23).select         each mycell in selection.cells             mycell.value = trim(substitute(g7, char(160), " "))         next     on error goto 0 end sub 

sub trimtext()     dim mycell range     on error resume next         selection.cells.specialcells(xlcelltypeconstants, 23).select         each mycell in selection.cells             'range("k:k").select             selection.numberformat = "@"             mycell.value = trim(replace(mycell.value, chr(160), chr(32)))         next     on error goto 0 end sub