=trim

  • Thread starter Thread starter gary
  • Start date Start date
G

gary

How can I remove the trailing space from values with a font color without losing the font color? (I'm not familiar with VBA).
 
How can I remove the trailing space from values with a font color without losing the font color? (I'm not familiar with VBA).
Click to expand...

Hi Gary,

Assuming your data to start in A1:

(1) in another column enter the formula
= Trim(A1)
and drag the formula down as far as necessary.

(2) copy the new formula cells

(3) select A1 | Paste Special | Values
'----------------

For a VBA solution, with the workbook which contains the data to be
trimmed selected, copy the following code

'=============>>
Sub TestIt()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range

On Error Resume Next
Set Rng = Application.InputBox _
(Prompt:="Select range to be trimmed", _
Type:=8)

If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
With rCell
.Value = RTrim(.Value)
End With
Next rCell
Else
MsgBox "No cells were selected!"
End If
End Sub
'<<=============

Alt-F11 to open the Visual Basic Editor
Insert | Module Edit | Paste
Alt-Q to close the Visual Basic Editor

To run the macro:
Alt-F8 to access the macro list | select "TestIt" | Run

Remember to save your workbook before closing it!


===
Regards,
Norman
 
How can I remove the trailing space from values with a font color
without losing the font color? (I'm not familiar with VBA).
Click to expand...

Hi Gary,

Assuming your data to start in A1:

(1) in another column enter the formula
= Trim(A1)
and drag the formula down as far as necessary.

(2) copy the new formula cells

(3) select A1 | Paste Special | Values
'---------------- [cut]
Click to expand...

Hi Gary,

I should have added that, after the Copy | Paste Special | Values
operation, you can delete the Trim formulas.


===
Regards,
Norman
 
Back
Top