Left() or Right()

[giantwolf]

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.

[BenjieLop]

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.

[DaveB]

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

[Dave O]

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

[giantwolf]

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

GW

[RagDyer]

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

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.

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

[RagDyer]

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