Remove blanks from text

[Guest]

Hi,

I have a column which contains postcodes to the first 4 characters, these
are then trimed to get rid of any padding:

np7
LS2
S1 4
etc

I'm then using this column to lookup from another list of the first parts of
postcodes i.e. could be first 3 or 4 characters etc. My problem appears with
the last one in my example list eg S1 4.

The problem is that the list that I'm comparing sees S1 4 as S1 and hence
doesn't return the correct value. How can I remove the space and any
characters that appear after it i.e. make S1 4 into S1 etc? There are others
like this in a long list.

 

[Gary Keramidas]

try this, you can shorten the range if you like

For Each cell In Range("a:a")
cell.Value = Replace(cell.Value, " ", "")
Next

 

[Guest]

Thanks, but doesn't this just remove the space? I need it to remove the
space and any character(s) that appear after the space eg. S1 4 needs to be
S1 etc.

 

[Guest]

Give this a try. It will only change constants and not formulas...

Sub TrimAfterSpace()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range

Set wks = Sheets("Sheet1")
On Error Resume Next
Set rngToSearch = wks.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rngToSearch Is Nothing Then
For Each rngCurrent In rngToSearch
If InStr(1, rngCurrent.Value, " ") > 0 Then _
rngCurrent.Value = Left(rngCurrent.Value, _
InStr(1, rngCurrent.Value, " ") - 1)
Next rngCurrent
End If
End Sub

 

[Gary Keramidas]

sorry, i guess i misunderstood. you can use this formula in a cell

=LEFT(A1,SEARCH(" ",A1,1)-1)

 

[Guest]

Thanks, this works too.

Give this a try. It will only change constants and not formulas...

Sub TrimAfterSpace()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range

Set wks = Sheets("Sheet1")
On Error Resume Next
Set rngToSearch = wks.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rngToSearch Is Nothing Then
For Each rngCurrent In rngToSearch
If InStr(1, rngCurrent.Value, " ") > 0 Then _
rngCurrent.Value = Left(rngCurrent.Value, _
InStr(1, rngCurrent.Value, " ") - 1)
Next rngCurrent
End If
End Sub

 

[Guest]

Thanks, that sorted it.

sorry, i guess i misunderstood. you can use this formula in a cell

=LEFT(A1,SEARCH(" ",A1,1)-1)