Solved: Trimming "Leading" spaces from cells in VBA?

[Simon Lloyd]

Hi all, im using:

For x = SelEndR To SelStart Step -1
Trim (Cells(x, 1))
Next x
to try to trim spaces from text in a cell, but trim only removes trailing spaces, how can i remove both preceeding and trailing spaces if there are any?. The length of the text in the cells may vary!

SelEndR is the last row of data SelStart is the first.

Regards,
Simon

[Charlize]

For x = SelEndR To SelStart Step -1
Cells(x, 1).Value = Trim(Cells(x, 1).Value)
Next xorFor x = SelEndR To SelStart Step -1
Cells(x, 1).Value = LTrim(RTrim(Cells(x, 1).Value))
Next xis the same.

Charlize

[Simon Lloyd]

Thanks Charlize worked a treat!

Regards,
SImon

[JKwan]

Thanks Charlize worked a treat!

Regards,
SImon

How about the built in function itself?

LTrim
RTrim

[Simon Lloyd]

JKwan it has to be done in VBA as the worksheet is formatted and built on the fly and the list of data which could be any length gets copied in to an area that would contain the formula!

Regards,
Simon

[JKwan]

How about building your own function so you can use it in your SS?

Function MyLTrim(LeftChop As String)
MyLTrim = LTrim(LeftChop)
End Function

[Simon Lloyd]

JKwan thanks, i will consider that in future.

Regards,
Simon

[Charlize]

How about building your own function so you can use it in your SS?

Function MyLTrim(LeftChop As String)
MyLTrim = LTrim(LeftChop)
End Function
No need to do that because there is such a function=Trim(A1)placed in B1 will give A1 without trailing and end spaces.

and you can use this toCells(x, 1).Value = Application.WorksheetFunction.Trim(Cells(x, 1).Value)

Charlize

[johnske]

Refer to the VBA Help files... LTrim removes leading spaces, RTrim removes trailing spaces, and Trim removes both leading and trailing spaces :)