-1

I want to remove the extra spaces after the text "Denver Health " into "Denver Health" for the range in column A. input:

Column A

"Denver Health Hospital  "
"Pueblo Hospital "

output:

Column A

"Denver Health Hospital"
"Pueblo Hospital"

I have tried that code but it removes all the spaces

Sub SpaceKiller()
   Worksheets("Sheet2").Columns("A").Replace _
      what:=" ", _
      Replacement:="", _
      SearchOrder:=xlByColumns, _
      MatchCase:=True
End Sub

Another attempt was

Sub trim()
Dim r As String

r = RTrim(Range("A2:A"))
End Sub
Jake8281
  • 71
  • 6
  • 2
    Please [edit] your question with your code attempt. See [reprex]. In other words, please show *how* you tried `Trim`. – BigBen Jan 26 '21 at 19:42
  • 1
    Are you sure those "spaces" are really spaces? Copy/paste from other sources (particularly from the web) often brings over characters which look like spaces but are not. Eg. "non-breaking space" https://en.wikipedia.org/wiki/Non-breaking_space – Tim Williams Jan 26 '21 at 19:48
  • Hi Tim, its actual space after the hospital name "Denver Health ". I have also tried Rtrim, Trim Function and trim and didn't do anything. This code works but removes all the space instead of the space in the end. I am beginner in VBA thank you – Jake8281 Jan 26 '21 at 19:52
  • Try please the next way to identify what character is and then use it to replace: `Debug.Print Asc(Right("Denver Health ", 1))`. But use the exactly string downloaded from the source you use. What does it return in `Immediate Window`? If not 32, it is anther character which can be used for replacing, instead of " ". I mean `Chr(returned number)`... – FaneDuru Jan 26 '21 at 19:54
  • I added my other attempt using trim and nothing happens – Jake8281 Jan 26 '21 at 19:57
  • `r = RTrim(Range("A2:A"))` - first, `A2:A` is not a valid range. Then, that doesn't actually change the values in column A. – BigBen Jan 26 '21 at 19:57
  • Hi Ben, Do you know the right way to do it. Thank you – Jake8281 Jan 26 '21 at 19:59
  • Please, try `Application.WorksheetFunction.Clean (Trim("Denver Health "))`. – FaneDuru Jan 26 '21 at 20:06

2 Answers2

1

This will get the trailing spaces..

Sub TrimTrailingSpaces()

    Dim LR As Long 'Use Long as Integer can only handle 32,767
    Dim myRng As Range 'I am going to name the used range
    Dim ws As Worksheet 'Declare worksheet as variable
    Dim cll As Range 
    
    Set ws = ThisWorkbook.Worksheets("Sheet2") 'Want to be specific on the worksheet used
    
    LR = ws.Cells(Rows.Count, 1).End(xlUp).Row 'Find the last row in Col A.  Do not include the whole column of over 1 million cells
    
    Set myRng = ws.Range(ws.Cells(2, 1), ws.Cells(LR, 1)) 'Declare the range used
    
    For Each cll In myRng 'cll is a Cell in a Collection of Cells in a Range that we defined
        cll = RTrim(cll) 'Looping through, modify each cell
    Next cll 'Go to the next Cell
    
End Sub
Darrell H
  • 1,876
  • 1
  • 9
  • 14
0

You can write own function to delete all additional spaces from string. allow only one space

Function Remove2Spaces(text As String) As String
Dim i_Len As Integer
Dim ReturnText As String

i_Len = Len(text)
For i = 1 To i_Len
    If Mid(text, i, 1) = " " Then
        If Mid(text, i + 1, 1) = " " Then
            text = Mid(text, 1, i) & Mid(text, i + 2)
            i = i - 1
        End If
    End If
Next i
Remove2Spaces = text
End Function

to get result place inside sub like below

sub Test()
MsgBox Remove2Spaces(ActiveSheet.Range("A3"))
end sub
Tomasz
  • 426
  • 2
  • 10