Using Trim function

[mjmacarty]

I am trying to trim excess spaces off a range of cell values. I am using the following code with no luck:

Option Explicit
Dim i As Integer, j As Integer, Shit As String

Sub FixIt()
For j = 1 To 12
For i = 1 To 32

Shit = Cells(i, j).Value
Trim (Shit)
Next i
Next j
End Sub

Any help would be appreciated.

 

[fredericofonseca]

Option Explicit
Dim i As Integer, j As Integer, Shit As String

Sub FixIt()
For j = 1 To 12
For i = 1 To 32

Shit = Cells(i, j).Value
Cells(i, j).Value = Trim(Shit)
Next i
Next j
End Sub



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[mjmacarty]

Thanks for your help. That doesn't seem to do the trick either. Code runs fine but doesn't do what it's supposed to.

 

[ETID]

Sub FixIt()

dim shoot as string

For j = 1 To 12
For i = 1 To 32

Shoot = trim(cstr(Cells(i, j)))
Cells(i, j)=Shoot

Next i
Next j
End Sub

 

[mjmacarty]

This works:

Sub FixIt()
For j = 4 To 15
For i = 2 To 33

Shit = Cells(i, j).Value
Cells(i, j).Value = Val(Shit)


Next i
Next j

End Sub

 

[fredericofonseca]

mjmacarty,

I don't know what data you are working with but your request was
"remove excess of spaces"
This is done using the "trim" function and my example DOES that.
from the manual
---------
Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).
--------------

Now you are returning to us and saying that this does not work and that the function that work is "val".

The "val" function will return the numbers of a string only.
so if your cells are as follows

a1="abcd "
a2=" 231 aaa"

val(a1) will return 0
and
val(a2) will return 231

so the first is not what you asked for.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[KenWright]

There is a really neat piece of code from one of the Microsoft MVPs (Dave McRitchie) that will do exactly what I think you are looking for. It may well be that what you have is non-breaking spaces etc, and there is a myriad of other garbage that can be in your data (Presumably gleaned from a website, or some other external source)

The macro is called Trimall, and I wouldn't be without it:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Regards
Ken.....................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[mjmacarty]

Sorry Frederico. I was dealing with numbers not behaving as numbers copied from a website. Thanks though.

 

[KenWright]

Thought as much. By the way, when you have run Trimall against your data, you may then want to copy an empty cell, select your data and do Edit / Paste Special / Add.

This will coerce any textual numbers back to numeric.

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------