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.

 

[xlbo]

Need more info here - how are you running the functions and how are you testing the results? These are very basic functions and to be honest, if there was missing links etc, you'd more likely get an error message than a function just "not working"

Last point is - are you SURE that they are spaces ?? Try the CLEAN function on them...

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question

 

[Loomah]

In addition to xlbo has said there may be a problem with your syntax if you have used the functions as you have posted - but if that were the case then errors would be generated (as xlbo has already said)

The syntax you have posted for replace is a little wrong but trim should work (if there are spaces).

try
activecell.replace " ", ""
activecell= trim(activecell)


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[fiep]

OK more info.

I just want to remove preceding spaces from a collumn. So i use this code:

while activecell.value <> ""
activecell.value = rtrim(activecell.value)
activecell.offset(1,0).select
wend

But the values do not change in the collumn. If I check with the debug window:
? "-" & trim(activecell.value) & "-"
it retruns:
- BN009843 -
So the spaces are still in the string. Tried the same with:

? "-" & replace(activecell.value, " ", "") & "-"
To no avail.

I do not get any error, the code compiles and runs but just does not do what is it supposed to do.

W.

 

[PHV]

What is the result of this in the debug window ?
? asc(left(activecell.value,1))

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

 

[fiep]

Loomah,

Thanks for you input. Did not know the .replace function.
Unfortunatteluy is does not work. The value in the cell stays the same including spaces.

W.

 

[fiep]

PHV,

The function ? asc(left(activecell.value,1)) returns 160.
So it looks like it is not a space.

Any ideas how I can remove this and why it shows as a space in Excel?

W.

 

[Loomah]

You could try this
activecell.replace chr(160), ""

But did you try the clean function as xlbo suggested?


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

I just want to remove preceding spaces from a collumn

activecell.value = rtrim(activecell.value)

Well, as a starter for 10, Rtrim only removes TRAILING spaces and you are trying to remove PRECEDING spaces. To do that, you would need Ltrim or simply Trim. May be worth starting with that and then moving on to how to clean other characters if that doesn't work

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question

 

[fiep]

Geoff,

Thank But I tried, Rtirm, Ltrim and Trim and none of the functions remove the charachters (shown as spaces in excel) from the activecell.

If I copy the collumn to word and do a search and replace on spaces (^w) if removes al whitespace?

So I am still stuck. The whitespace in my cells are still not removed.

W.

 

[dhulbert]

fiep.

I've tried all sorts of version of this and mine all work fine, can i ask a really silly question but are you sure the first active cell selected is the first cell of your range and that the code isn't running through on the wrong column or some such.

 

[fiep]

Dhulbert,

This is my code:

While ActiveCell.Value <> ""
ActiveCell.Value = Clean(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Wend
I tried Trim, LTrim, RTrim and Replace and nothing works. I do not see how I can change a different collumn when all I use is the "activecell". I see the values I want to change in my debug window they just do not get changed.

As Loomah can see I tried the clean function. Bu now my code does not compile. The clean function can not be found. So missing a library after all?

What library is the clean function part of or in what order should my references be placed? I have the following references:
Visual Basic for Applications
Microsoft Excel 10.0 OL
OLE Automation
MS Office 10.0 OL
MS Forms 2.0 OL

Thanks for all the answers so far.

W.

 

[dhulbert]

fiep.

I agree the clean funtion will not work in my VBA either have you tried uing clean as a worksheet function as a 1 off to see what happens.

 

[PHV]

And this ?
ActiveCell.Value = Replace(ActiveCell.Value, Chr(160), "")

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

 

[Loomah]

I think you need to qualify the clean function
ie
worksheetfunction.clean(activecell)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[fiep]

All,

Dhulbert. I tried the clean function in my sheet too but it does not remove this charachter(s).

I will try the Replace(ActiveCell.Value, Chr(160), "")
But of course this is not a generic solution because next time it will be chr(161) or a real space.

Thanks so far.

W.

 

[PHV]

A more general way:
ActiveCell.Value = Trim(Replace(ActiveCell.Value, Chr(160), ""))

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

 

[fiep]

PHV,

Indeed a more general way. But I do not know where the character 160 comes from and if there will be other values in the future (161, 162, 163, etc).

The replace(string, chr(160), "") works by the way.
I will implement the:
ActiveCell.Value = Trim(Replace(ActiveCell.Value, Chr(160), ""))
And hope for the best in the future ;-)

thanks all.

W.

 

[PHV]

Chr(160) is the "Non Break Space" character.

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

 

[dhulbert]

fiep

Out of interest where does the data come from initially? Canb anythign be done at the source to clean up the data before you get it.