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-

Try this:

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

HTH,

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


"Don" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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-