Postcode Extraction (=Right, =Left, = Middle not possible)
Hi there,
I have about 2000 address details from which I want to extract just the UK postcode.
The number of characters (and spaces) before and after the postcode differs for each address so Left, Right, Mid or Len functions will not help.
I imagine a formula that says the following would work however I am too much of a novice to know if this is possible!
"If you find 2 consecutive capital letters followed by 1 number and a space extract the 7 characters from and including the 1st capital letter, if you find 2 consecutive capital letters followed by 2 numbers and a space extract the 8 characters from and including the 1st capital letter"
Some example addresses are below
Hope someone can help!
Truro - Truro TR1 3XL; Treyew Road ; 01872 260881; 01872 260881; 1872 260881 ; ;
Bodmin - Bodmin PL31 2SS; Dennison Road ; 01208 269 743; 01208 269 743; 1208 269 743 ; ;
Plymouth - Plymouth PL1 1LE; The Armada Centre Armada Centre ; 01752 674767; 01752 674767; 1752 674767 ; ;
Marsh Mills - Plymouth PL3 6RL; Plymouth Road Crabtree ; 01752 222748; 01752 222748; 1752 222748 ; ;
Swansea - Swansea SA1 8JA; Quay Parade ; 01792 653985; 01792 653985; ;
Barnstaple - Barnstaple EX31 3NH; Gratton Way Roundswell Buisness Park ; 01271 325498; 01271 325498; 1271 325498 ; ;
One possibility if we assume post code always followed by ; (first instance) and that the post code will commence at point of 2nd space prior to ; then:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(";",A1)-1)," ",REPT(" ",LEN(A1))),2*LEN(A1))) copied down where A1 holds stringyou could use RegExp patterns etc but I think the formula above should suffice (based on variety of examples provided)Originally Posted by TheZman
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Assuming the postcode is always 7 or 8 characters (with the space) try
=TRIM(MID(A1,FIND(";",A1)-8,8))
Audere est facere
Both methods work - fantastic! Thank you both - much appreciated!
And a variation on one Don posted earlier
=TRIM(MID(A2,SEARCH("????????;",A2,1),8))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks