Hi,

I have a list of postcodes (zip codes) that I need to 'extract' part of
and allocate to a new cell.

eg:

AL1 2TQ
AL1 5RD
AL22 9IP

I need to take the first characters before the space however it could
be 3 or 4 characters before each space. I need a formula that will
start from the left and take anything before a space or one that starts
from the right and takes anything before the righthand 3 characters and
the space. I've tried left and right but can't quite get it to work.

Any ideas? Thanks in advance.


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=396875

Assume the value is in cell A1:

To extract to the left of the space:
=LEFT(A1,SEARCH(" ",A1))

To extract to the right of the space:
=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))
--
Regards,

Dave


"giantwolf" wrote:


Hi,

I have a list of postcodes (zip codes) that I need to 'extract' part of
and allocate to a new cell.

eg:

AL1 2TQ
AL1 5RD
AL22 9IP

I need to take the first characters before the space however it could
be 3 or 4 characters before each space. I need a formula that will
start from the left and take anything before a space or one that starts
from the right and takes anything before the righthand 3 characters and
the space. I've tried left and right but can't quite get it to work.

Any ideas? Thanks in advance.


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=396875

giantwolf Wrote:
Hi,

I have a list of postcodes (zip codes) that I need to 'extract' part of
and allocate to a new cell.

eg:

AL1 2TQ
AL1 5RD
AL22 9IP

I need to take the first characters before the space however it could
be 3 or 4 characters before each space. I need a formula that will
start from the left and take anything before a space or one that starts
from the right and takes anything before the righthand 3 characters and
the space. I've tried left and right but can't quite get it to work.

Any ideas? Thanks in advance.

Try this ...

=LEFT(A1,FIND(\" \",A1)-1)

and copy down as required.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=396875

Assuming your sample data is in cells A1:A3, you can use the FIND()
function to locate the space, and use it as an argument in the MID()
function. MID() is similar to LEFT() and RIGHT() but allows you to
select the start and stop columns.

In cell B1 I used the formula
=MID(A1,1,FIND(" ",A1,1)-1)

In cell C1 I used
=MID(A1,FIND(" ",A1,1)+1,LEN(A1))

Thanks to all 3 of you for your help, they all worked great. Much
appreciated.

GW


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=396875

FWIW,

Your suggested formula is being distorted when displaying in my O.E. reader.

I see your formula in upper case, like it was copied directly from the XL
formula bar, and the <space is depicted as
\" \"
backslash,dbl quote,space,backslash,dbl quote.

I had to go into excelforum to see your actual formula,
which was in lower case, as you might have typed it directly into the post,
and the space was properly displayed between 2 dbl quotes.

Does anyone know why ???
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"BenjieLop" wrote
in message ...

giantwolf Wrote:
Hi,

I have a list of postcodes (zip codes) that I need to 'extract' part of
and allocate to a new cell.

eg:

AL1 2TQ
AL1 5RD
AL22 9IP

I need to take the first characters before the space however it could
be 3 or 4 characters before each space. I need a formula that will
start from the left and take anything before a space or one that starts
from the right and takes anything before the righthand 3 characters and
the space. I've tried left and right but can't quite get it to work.

Any ideas? Thanks in advance.

Try this ...

=LEFT(A1,FIND(\" \",A1)-1)

and copy down as required.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile:
http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=396875

RD,

I really have no idea why this is happening. I have asked around to
check if they are experiencing the same thing as you are and,
unfortunately, no one has. I guess this is one of the things that we
have to put up with in this high tech world that we live in right now.



RagDyer Wrote:
FWIW,

Your suggested formula is being distorted when displaying in my O.E.
reader.

I see your formula in upper case, like it was copied directly from the
XL
formula bar, and the <space is depicted as
\" \"
backslash,dbl quote,space,backslash,dbl quote.

I had to go into excelforum to see your actual formula,
which was in lower case, as you might have typed it directly into the
post,
and the space was properly displayed between 2 dbl quotes.

Does anyone know why ???
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

"BenjieLop"
wrote
in message
...

giantwolf Wrote:
Hi,

I have a list of postcodes (zip codes) that I need to 'extract'
part of
and allocate to a new cell.

eg:

AL1 2TQ
AL1 5RD
AL22 9IP

I need to take the first characters before the space however it
could
be 3 or 4 characters before each space. I need a formula that
will
start from the left and take anything before a space or one that
starts
from the right and takes anything before the righthand 3 characters
and
the space. I've tried left and right but can't quite get it to
work.

Any ideas? Thanks in advance.

Try this ...

=LEFT(A1,FIND(\" \",A1)-1)

and copy down as required.

Regards.


--
BenjieLop



------------------------------------------------------------------------
BenjieLop's Profile:
http://www.excelforum.com/member.php...o&userid=11019
View this thread:
http://www.excelforum.com/showthread...hreadid=396875



--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=396875

Here's where Biff saw the same thing and commented on it just a few days
ago:

http://tinyurl.com/dtr8t


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"BenjieLop" wrote
in message ...

RD,

I really have no idea why this is happening. I have asked around to
check if they are experiencing the same thing as you are and,
unfortunately, no one has. I guess this is one of the things that we
have to put up with in this high tech world that we live in right now.



RagDyer Wrote:
FWIW,

Your suggested formula is being distorted when displaying in my O.E.
reader.

I see your formula in upper case, like it was copied directly from the
XL
formula bar, and the <space is depicted as
\" \"
backslash,dbl quote,space,backslash,dbl quote.

I had to go into excelforum to see your actual formula,
which was in lower case, as you might have typed it directly into the
post,
and the space was properly displayed between 2 dbl quotes.

Does anyone know why ???
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"BenjieLop"
wrote
in message
...

giantwolf Wrote:
Hi,

I have a list of postcodes (zip codes) that I need to 'extract'
part of
and allocate to a new cell.

eg:

AL1 2TQ
AL1 5RD
AL22 9IP

I need to take the first characters before the space however it
could
be 3 or 4 characters before each space. I need a formula that
will
start from the left and take anything before a space or one that
starts
from the right and takes anything before the righthand 3 characters
and
the space. I've tried left and right but can't quite get it to
work.

Any ideas? Thanks in advance.

Try this ...

=LEFT(A1,FIND(\" \",A1)-1)

and copy down as required.

Regards.


--
BenjieLop



------------------------------------------------------------------------
BenjieLop's Profile:
http://www.excelforum.com/member.php...o&userid=11019
View this thread:
http://www.excelforum.com/showthread...hreadid=396875



--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile:
http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=396875