Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default Postal code to Area translation

I have some postal codes all in one column.
eg EN1 7IH, and N1 M33

I want to create a column where excel reads the first characters BEFORE the space, since those characters represent an Area.

I want to be able to add the name of the postal code area in the new column. For example EN1 = Area1 and N1 = Area2

Last edited by idxearo : October 25th 12 at 04:57 AM
  #2   Report Post  
Member
 
Posts: 93
Default

You will need to have a table set up in your work sheet with the post codes listed and a area allocated to each code, As in the UK there are over a hundred. Then you could use a LOOKUP or INDEX & MATCH etc.
EG: =VLOOKUP(LEFT(Cell/Ref,2),Your_Range,Column,0)

Quote:
Originally Posted by idxearo View Post
I have some postal codes all in one column.
eg EN1 7IH, and N1 M33

I want to create a column where excel reads the first characters BEFORE the space, since those characters represent an Area.

I want to be able to add the name of the postal code area in the new column. For example EN1 = Area1 and N1 = Area2
  #3   Report Post  
Junior Member
 
Posts: 2
Default

Quote:
Originally Posted by Kevin@Radstock View Post
You will need to have a table set up in your work sheet with the post codes listed and a area allocated to each code, As in the UK there are over a hundred. Then you could use a LOOKUP or INDEX & MATCH etc.
EG: =VLOOKUP(LEFT(Cell/Ref,2),Your_Range,Column,0)
Thank you very much I will try and let you know how this goes.
  #4   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by idxearo View Post
Thank you very much I will try and let you know how this goes.
The formula suggested above will only look up the left 2 characters in the post code cells. That means it will look for EN in the lookup table rather than EN1.

The below will look for all characters before the first space.

=VLOOKUP(LEFT(CellRef,FIND(" ",CellRef)-1),Range,Column,0)
  #5   Report Post  
Member
 
Posts: 93
Default

EG = "EXAMPLE" !!

Quote:
Originally Posted by Spencer101 View Post
The formula suggested above will only look up the left 2 characters in the post code cells. That means it will look for EN in the lookup table rather than EN1.

The below will look for all characters before the first space.

=VLOOKUP(LEFT(CellRef,FIND(" ",CellRef)-1),Range,Column,0)


  #6   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Kevin@Radstock View Post
EG = "EXAMPLE" !!

Yes, Kevin, I know what it means.

But your EG was not going to do what the OP needed, so I stepped in with a better EG.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Verify Canadian Postal Code ~ make the code work jat Excel Programming 2 February 27th 09 10:12 PM
Cdn Postal Code Christine Excel Discussion (Misc queries) 3 January 16th 08 07:01 PM
Canadian Postal Code format? Fritz Excel Worksheet Functions 9 July 23rd 07 09:57 PM
Validation of Postal Code Veronika Excel Worksheet Functions 1 December 16th 05 10:42 PM
Canadian Postal Code Tim Excel Programming 3 February 2nd 04 07:08 PM


All times are GMT +1. The time now is 11:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"