String functions do not work in Excel

[fiep]

Hi,

I am trying to use string functions in excel like:
trim(activecell.value)
or
replace(activecell.value, " ","")
But these functions always return the same strings as the values in the activecell. So the spaces are not removed from the strings. It looks like none of the string functions work in excel. Do I have to link a library or what am I missing here?

Thanks.

 

[fiep]

Dhulbert,

The source comes from a UNIX server. And it probably can be cleaned before it goes into excel. I actually asked the provider (of the data) if he can remove non-printable charachters in the future.

Thanks for the interest.

W.

 

[PHV]

Chr(160) is a printable character, in fact the 1st 8bit one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

fiep - you have the answer to your question in what has been posted already. PHV gave you this to find out the character:

asc(left(activecell.value,1))

This could then be used in the loop to trim the cells:

ActiveCell.Value = Trim(Replace(ActiveCell.Value, Chr(160), ""))

becomes

ActiveCell.Value = Trim(Replace(ActiveCell.Value, Chr(asc(left(activecell.value,1)) ), ""))

This would get around any "changing characters" issue as it uses the character found at the end of the cell in the replace function

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question