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.