Clean function

[AccessWater]

hello, I am trying to get rid of spaces from a imported data in a macro. I used
clean(trim(Emp_name)). Emp_name is a string. However, when I run the macro, it says: Sub or function not defined. If I delete CLean (), it works fine. But there are spaces for some names. Could anyone tell me what to do? I thought Clean funtion is a default one in excel.

Thank you very mcuh.

 

[shades]

CLEAN removes only non-printing characters.

Also, TRIM will remove only leading and trailing not spaces between words.

Try this formula:

=SUBSTITUTE(A37," ","")
________
BMW M21 HISTORY

 

[AccessWater]

hi, Shades, Thank you for your help. I tried substitute function. It gives the same message: sub or function not defined. Do I need to add a library?

 

[shades]

You can enter it as an Excel function, not a VBA function. One way to see the difference, is to record a macro, using this function in Excel. You will see how to reference it in VBA.
________
Volcano classic

 

[AccessWater]

Shades, could you please give me more detailed info. on how to get it work? I am not familar with VBA. The worse thing is that my boss insists that the maro worked fine before. But every time I run it now, it gives the error message. Please help!

 

[shades]

Can you post the entire code?
________
INTREPID ESX

 

[AccessWater]

Hi, Sahdes,
The code is very very long. Someone helped me with it. The following works in VBA. But still not sure why .Cells(rr, "J") = clean(Trim(Emp_name)) does not work. ( Was told it woked before: ). Thanks for your help.

.Cells(rr, "J") = "=trim(clean(""" & Emp_name & """))"

 

[shades]

Glad you got it to work.
________
CBR250