Formula assistance required!!!

Hi,
I am preparing a Pivot table from a s/s with multiple columns ..One of the
columns is 'Post code'. In each post code cell is identified a unique post
code which is a two part code in the format AABB CAA (where AA=alphabet
characters, B=numeric 0-99, C= numeric 0-9. There is always a space between B
and C.
Is there a formula I can define that will simply import the first part of
the code into a new column? So for example for RG7 4TY I will capture RG7 in
the new column, but equally it will accomodate scenarios where the first
component is 4 characters ... So for example with post code RG12 IBP, I will
simply capture RG12?

I am guessing there is a way to do this? Would appreciate assistance!

Don-

Select all your codes and paste into a new column at the end of your data.
Select all these codes and do Data / text to Columns / delimited / space as
delimiter.

If you want a formula then

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Don" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I am preparing a Pivot table from a s/s with multiple columns ..One of the
> columns is 'Post code'. In each post code cell is identified a unique post
> code which is a two part code in the format AABB CAA (where AA=alphabet
> characters, B=numeric 0-99, C= numeric 0-9. There is always a space
between B
> and C.
> Is there a formula I can define that will simply import the first part of
> the code into a new column? So for example for RG7 4TY I will capture RG7
in
> the new column, but equally it will accomodate scenarios where the first
> component is 4 characters ... So for example with post code RG12 IBP, I
will
> simply capture RG12?
>
> I am guessing there is a way to do this? Would appreciate assistance!
>
> Don-

Try this:

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

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Don" <[email protected]> wrote in message
news:[email protected]...
Hi,
I am preparing a Pivot table from a s/s with multiple columns ..One of the
columns is 'Post code'. In each post code cell is identified a unique post
code which is a two part code in the format AABB CAA (where AA=alphabet
characters, B=numeric 0-99, C= numeric 0-9. There is always a space between
B
and C.
Is there a formula I can define that will simply import the first part of
the code into a new column? So for example for RG7 4TY I will capture RG7 in
the new column, but equally it will accomodate scenarios where the first
component is 4 characters ... So for example with post code RG12 IBP, I will
simply capture RG12?

I am guessing there is a way to do this? Would appreciate assistance!

Don-

Ken,

many thanks for the prompt reply! Both approaches worked a treat! Just out
of curiousity (and for an excel novice) can you briefly explain the
significance of the '-1' in the formula? Briefly how does it work ..?

Thnaks again,

Don

"Ken Wright" wrote:

> Select all your codes and paste into a new column at the end of your data.
> Select all these codes and do Data / text to Columns / delimited / space as
> delimiter.
>
> If you want a formula then
>
> =LEFT(A1,(FIND(" ",A1)-1))
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Don" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > I am preparing a Pivot table from a s/s with multiple columns ..One of the
> > columns is 'Post code'. In each post code cell is identified a unique post
> > code which is a two part code in the format AABB CAA (where AA=alphabet
> > characters, B=numeric 0-99, C= numeric 0-9. There is always a space
> between B
> > and C.
> > Is there a formula I can define that will simply import the first part of
> > the code into a new column? So for example for RG7 4TY I will capture RG7
> in
> > the new column, but equally it will accomodate scenarios where the first
> > component is 4 characters ... So for example with post code RG12 IBP, I
> will
> > simply capture RG12?
> >
> > I am guessing there is a way to do this? Would appreciate assistance!
> >
> > Don-
>
>
>

Don,

The FIND function returns the location in the string of the space
character. Subtracting one from that number returns the position
of the character before the space. This value is then used by
LEFT to return that many characters.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Don" <[email protected]> wrote in message
news:[email protected]...
> Ken,
>
> many thanks for the prompt reply! Both approaches worked a
> treat! Just out
> of curiousity (and for an excel novice) can you briefly explain
> the
> significance of the '-1' in the formula? Briefly how does it
> work ..?
>
> Thnaks again,
>
> Don
>
> "Ken Wright" wrote:
>
>> Select all your codes and paste into a new column at the end
>> of your data.
>> Select all these codes and do Data / text to Columns /
>> delimited / space as
>> delimiter.
>>
>> If you want a formula then
>>
>> =LEFT(A1,(FIND(" ",A1)-1))
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ----------------------------------------------------------------------------
>> It's easier to beg forgiveness than ask
>> permission :-)
>> ----------------------------------------------------------------------------
>>
>> "Don" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi,
>> > I am preparing a Pivot table from a s/s with multiple
>> > columns ..One of the
>> > columns is 'Post code'. In each post code cell is identified
>> > a unique post
>> > code which is a two part code in the format AABB CAA (where
>> > AA=alphabet
>> > characters, B=numeric 0-99, C= numeric 0-9. There is always
>> > a space
>> between B
>> > and C.
>> > Is there a formula I can define that will simply import the
>> > first part of
>> > the code into a new column? So for example for RG7 4TY I
>> > will capture RG7
>> in
>> > the new column, but equally it will accomodate scenarios
>> > where the first
>> > component is 4 characters ... So for example with post code
>> > RG12 IBP, I
>> will
>> > simply capture RG12?
>> >
>> > I am guessing there is a way to do this? Would appreciate
>> > assistance!
>> >
>> > Don-
>>
>>
>>