PDA

View Full Version : Solved: Trimming "Leading" spaces from cells in VBA?



Simon Lloyd
05-07-2007, 06:41 AM
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
05-07-2007, 06:48 AM
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
05-07-2007, 07:07 AM
Thanks Charlize worked a treat!

Regards,
SImon

JKwan
05-07-2007, 09:02 AM
Thanks Charlize worked a treat!

Regards,
SImon

How about the built in function itself?

LTrim
RTrim

Simon Lloyd
05-07-2007, 09:28 AM
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
05-07-2007, 09:42 AM
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
05-07-2007, 10:02 AM
JKwan thanks, i will consider that in future.

Regards,
Simon

Charlize
05-07-2007, 03:15 PM
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
05-07-2007, 04:10 PM
Refer to the VBA Help files... LTrim removes leading spaces, RTrim removes trailing spaces, and Trim removes both leading and trailing spaces :)