Need to remove spaces from cells

[Holley]

I am having difficulty using the TRIM formula and think there may be a better way to accomplish what I am trying to do. I have 4 columns (C,D,E,F) that contain data that is imported from a text file. Each field is will accommodate up to 35 characters. As this imports into Excel, it converts these to spaces. Since there is no real data, the cells appear empty. Some cells may only contain 5 characters, but there are 30 spaces trailing. Some cells contain multiple words, so I do need to keep the space between them. I thought about find and replace, but since the number of trailing spaces will vary, I wasn't sure how to accomplish this.

Any suggestions and thanks in advance!

 

[Micron]

Pick a cell with at least one leading space then in the immediate window of the vb editor, type
?asc(left(sheets("Sheet23").range("D9"),1)) and hit return. Change your sheet and range references to suit. If your result is not 32, then your problem might be that the space character is from another character set (e.g. Unicode) in which case your number is probably 160. Trim only works on character 32.

EDIT - if you find that the spaces are 160 then I think the approach would be to substitute 32 for 160 inside of Trim.

 

[ranman256]

i occasionally have troublesome columns so I
run this to trim the data.
usage: place cursor in column, then run macro.

'place cursor in the column to trim, then run:TrimMyCol
Public Sub TrimMyCol()
Dim vVal
Dim iRows As Long, iStartCol As Long

iStartCol = ActiveCell.Column
'Range("A1").Select
Range("A1").CurrentRegion.Select
iRows = Range("A1").CurrentRegion.Rows.Count

Cells(1, iStartCol).Select

While ActiveCell.Row <= iRows
   ActiveCell.Value = Trim(ActiveCell.Value)
   ActiveCell.Offset(1, 0).Select  'next row
Wend
'msgbox "Done"
End Sub

 

[Fluff]

If they are normal spaces try

Sub Holley()
   With Range("C1:F" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate("Trim(" & .Address & ")")
   End With
End Sub

 

[Holley]

If they are normal spaces try

Sub Holley()
   With Range("C1:F" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate("Trim(" & .Address & ")")
   End With
End Sub

This worked perfectly! Much appreciated!!

Thanks as always!!!

 

[Fluff]

Glad we could help & thanks for the feedback.