Formula assistance required!!!

[Guest]

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-

 

[RagDyer]

Try this:

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

HTH,

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


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

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

 

[Guest]

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

 

[Chip Pearson]

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