Remove blanks from text
 

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.

Remove blanks from text
 

try this, you can shorten the range if you like

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

--


Gary


"Delboy" wrote in message
...
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.

Remove blanks from text
 

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.

"Gary Keramidas" wrote:

try this, you can shorten the range if you like

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

--


Gary


"Delboy" wrote in message
...
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.

Remove blanks from text
 

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
--
HTH...

Jim Thomlinson


"Delboy" wrote:

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.

Remove blanks from text
 

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

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

--


Gary


"Delboy" wrote in message
...
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.

"Gary Keramidas" wrote:

try this, you can shorten the range if you like

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

--


Gary


"Delboy" wrote in message
...
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.

Remove blanks from text
 

Thanks, this works too.

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Delboy" wrote:

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.

Remove blanks from text
 

Thanks, that sorted it.

"Gary Keramidas" wrote:

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

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

--


Gary


"Delboy" wrote in message
...
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.

"Gary Keramidas" wrote:

try this, you can shorten the range if you like

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

--


Gary


"Delboy" wrote in message
...
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.