| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I seperate numbers from text (in one cell) into 2 cells? | Excel Discussion (Misc queries) | |||
| Post Code | Excel Worksheet Functions | |||
| Does anyone know how I can seperate a post code in my data sheet? | Excel Discussion (Misc queries) | |||
| Post Code search | New Users to Excel | |||
| Post code & Suburbs | Excel Discussion (Misc queries) | |||
Linear Mode
