Seperate Text and numbers in Post code
 

Hi
I have a data sheet and need to get just the area from a post code e.g BS1
1JN I need just the BS in a column. I would use left,2 etc but problem is
some codes such as Birmingham only have 1 letter.

Any ideas please.
Thanks
Chris

Seperate Text and numbers in Post code
 

Chriso --

What does Birmingham's code look like?

"chriso" wrote:

Hi
I have a data sheet and need to get just the area from a post code e.g BS1
1JN I need just the BS in a column. I would use left,2 etc but problem is
some codes such as Birmingham only have 1 letter.

Any ideas please.
Thanks
Chris

Seperate Text and numbers in Post code
 

I think the maximum is two letters at the beginning of a postcode, so
you could do this:

=IF(AND(MID(A1,2,1)="0",MID(A1,2,1)<="9"),LEFT(A1 ,1),LEFT(A1,2))

assuming the postcode is in A1.

Hope this helps.

Pete


On Apr 7, 7:19*pm, chriso wrote:
Hi
I have a data sheet and need to get just the area from a post code e.g BS1
1JN I need just the BS in a column. *I would use left,2 etc but problem is
some codes such as Birmingham only have 1 letter.

Any ideas please.
Thanks
Chris

Seperate Text and numbers in Post code
 

It is "B", as in B15 3SX (where I used to live).

Pete

On Apr 7, 7:25*pm, pdberger
wrote:
Chriso --

What does Birmingham's code look like?



"chriso" wrote:
Hi
I have a data sheet and need to get just the area from a post code e.g BS1
1JN I need just the BS in a column. *I would use left,2 etc but problem is
some codes such as Birmingham only have 1 letter.

Any ideas please.
Thanks
Chris- Hide quoted text -

- Show quoted text -

Seperate Text and numbers in Post code
 

Hi it would be B43 5JJ for instance

"pdberger" wrote:

Chriso --

What does Birmingham's code look like?

"chriso" wrote:

Hi
I have a data sheet and need to get just the area from a post code e.g BS1
1JN I need just the BS in a column. I would use left,2 etc but problem is
some codes such as Birmingham only have 1 letter.

Any ideas please.
Thanks
Chris

Seperate Text and numbers in Post code
 

Thats brilliant Pete, thanks very much it works great!

"Pete_UK" wrote:

I think the maximum is two letters at the beginning of a postcode, so
you could do this:

=IF(AND(MID(A1,2,1)="0",MID(A1,2,1)<="9"),LEFT(A1 ,1),LEFT(A1,2))

assuming the postcode is in A1.

Hope this helps.

Pete


On Apr 7, 7:19 pm, chriso wrote:
Hi
I have a data sheet and need to get just the area from a post code e.g BS1
1JN I need just the BS in a column. I would use left,2 etc but problem is
some codes such as Birmingham only have 1 letter.

Any ideas please.
Thanks
Chris

Seperate Text and numbers in Post code
 

Here is another possibility...

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1)

Rick


"chriso" wrote in message
...
Hi
I have a data sheet and need to get just the area from a post code e.g BS1
1JN I need just the BS in a column. I would use left,2 etc but problem is
some codes such as Birmingham only have 1 letter.

Any ideas please.
Thanks
Chris

Seperate Text and numbers in Post code
 

You're welcome - thanks for feeding back.

Pete

On Apr 7, 7:45*pm, chriso wrote:
Thats brilliant Pete, thanks very much it works great!



"Pete_UK" wrote:
I think the maximum is two letters at the beginning of a postcode, so
you could do this:

=IF(AND(MID(A1,2,1)="0",MID(A1,2,1)<="9"),LEFT(A1 ,1),LEFT(A1,2))

assuming the postcode is in A1.

Hope this helps.

Pete

On Apr 7, 7:19 pm, chriso wrote:
Hi
I have a data sheet and need to get just the area from a post code e.g BS1
1JN I need just the BS in a column. *I would use left,2 etc but problem is
some codes such as Birmingham only have 1 letter.

Any ideas please.
Thanks
Chris- Hide quoted text -

- Show quoted text -