Left(), Right(), Trim()

[Daxton A.]

Ok the question is there another way to do these. B/c I can do it but I'm going to be going to school pretty soon and the people I work with can't do it in case they upgrade their Office or get a new PC. So i want this to not be a problem in the future. So does anyone know of another way of how else to do this so it's not a problem just waiting to happen?

[shades]

Not sure, but possibly

TRIM() = CLEAN()

[Zack Barresse]

...upgrade their Office or get a new PC....

I don't think that MS is going to drop these functions. Are you looking for UDF's? Or just other workarounds?

[Daxton A.]

Ok, instead of learning another way to write the code down. I figured I would just write a note down for telling how to do it. But I'm not sure of how to do it. I know you have to go to the References Menu in VBA but I'm not sure which one's to tell them to checkmark. Any help on which one's to tell them to checkmark?

[NateO]

I'm curious as to why anyone could not use these. In Excel, these guys are here to stay. They're staying with .Net as well:

http://msdn.microsoft.com/library/en.../vafctLeft.asp

If you want strings, use the string variety, e.g.,

left$()

These are string functions, located in the Visual Basic for Applications library.

[Daxton A.]

They haven't worked for me when I upgraded a few PC's to 2000. But i used Left(), instead of Left$() i'll see if that works.

[Daxton A.]

Nope I tried that and It didn't work either. Let me try uninstalling office and reinstalling it b/c I've messed w/them and I want to see if it was like that from the get go.

[NateO]

They haven't worked for me when I upgraded a few PC's to 2000. But i used Left(), instead of Left$() i'll see if that works.

That's probably not the issue. I suspect you have a missing reference. Remove all references that have the text Missing with them, they cripple left() and are of no use to you at this point.

[NateO]

Nope I tried that and It didn't work either. Let me try uninstalling office and reinstalling it b/c I've messed w/them and I want to see if it was like that from the get go.

If you have a valid reference to the VBA library, you're probably wasting your time. Remove missing references as mentioned here:

http://support.microsoft.com:80/supp.../q160/8/70.asp

It's an Access kb, but I suspect it's relevent here.

[shades]

shades wrote:
Not sure, but possibly

TRIM() = CLEAN()

Guess I missed the boat completely on this - maybe even the life raft!

:rofl :rofl

[Daxton A.]

I reinstalled Office and it still gave me that error. Here's a pic of what references are selected.

[Daxton A.]

I just wanted to say TY for the clean(). But the other 2 are the problem now.

Tommy,
U say that Left() and Right() work when u install Office? Was that on a later version of Office or something? B/c I am using Office 2000 and it won't work on mine.

[Daxton A.]

I took off the one that's is shown as it is Missing & IT WORKED! So maybe in the future they won't have a problem with this after all. Now i just have to make a note that if it isn't working to make sure there isn't any References that are Missing. I'm so happy.

I just want to say THANK YOU Shades and NateO!

[NateO]

There's a couple of issues here Shades. First being that Clean() <> Trim(). Second, Trim() is available in Excel VBA either as a VBA String function or can be accessed in the Excel Class as well. Not true for Clean(), it can only be accessed via the Excel Class, different object libraries. E.g., the following errors out:

Sub test()
  Dim y As String
  'error
  Let y = Clean("tester  2")
  End Sub

And the following demonstrates the difference:

Sub test2()
  MsgBox Len(Trim$("Tester 1 "))
  MsgBox Len(WorksheetFunction.Clean("Tester 1 "))
  End Sub

To get a better understanding of the forces at play here, I recommend reading about both in the Excel help file (not VBE help).

Daxton A., glad to hear your quandary is sorted.