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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
>
>
>

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

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

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

--


Gary


"Delboy" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>> >
>>
>>
>>

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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.
> >> >
> >> >
> >>
> >>
> >>
>
>
>