Finding UK Postcodes within Excel 4

[rafot]

I have a spreadsheet where a column cell contains a variation of values i.e. Town, County and Postcode. I want to find some way of identifing the Postcodes and copying them accross within the same row as they were sourced, to a specific column for Postcodes.

Any help greatfully received.

R

 

[DougP]

Data is Data
can you give us an example

existing cell | new cell

blah shire UK 345-234 345-234

????

DougP, MCP, A+

 

[rafot]

The data is structured as follows, column b is where I want the postcodes moved to and thus is blank, column a is the source. I want to pick out the postcodes and move them into the next column

col a | col b
1| london |
2| coventry|
3| PE34 3DA|
4| leeds |
5| CV3 3RD |

I hope this makes more sense.

 

[xlbo]

which row do you want the postcodes in ??
You have 3 address rows currently by the looks of it - do you really want 2 address rows then a postcode on 1 of the rows ??

what is the final state you want to end up with. Also, are there always 3 rows?? if not, how would you propose the postcode is identified so it can be moved ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[rafot]

Basically I downloaded from our webserver the details of our members. However, I had a problem in that the complete address was contained within a single cell. When I finally managed to delimit the address by the carriage return, it caused a problem; some address were shorter than others and thus used more cells across a row. So I am trying to find all the postcodes present in one column where the data is mixed and move them into a column of only postcodes.

Hope this clarifies things

 

[xlbo]

right - so are you saying there is no standard data layout and nothing to mark that the row contains a postcode ??

The issue is here that postcodes are non standard - you can have:

LetterLetterNumberSpace
LetterSpaceNumberSpace
LetterSpaceSpaceNumebr
LetterNumberSpaceSpace

they are very hard to test for as if you have address details, you presumably have a mixture of alpha and numeric data in the other cells as well ??

You may be outta luck here - you can't even test the length of the cell (unless the postcodes are all in a standardised format and that would be a rare thing indeed)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[rafot]

I am in luck because there does not appear to be any numeric values within the other cells of the column as the house numbers are all within other cells. Does this make it possible?

 

[xlbo]

in which case you are indeed lucky

this will loop through a set of selected cells and output the postcodes it finds to the next column

Sub Output_Postcodes()
Dim c As Range
For Each c In Selection
  If IsPostcode(c) = True Then
     c.Offset(0, 1) = c
  End If
Next c
End Sub

Function IsPostcode(rng As Range)
 For i = 1 To Len(rng)
   If IsNumeric(Mid(rng, i, 1)) Then
      IsPostcode = True
      Exit Function
   End If
 Next i
 IsPostcode = False
End Function

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[rafot]

Geoff

Thanks so much for your help and patience. The problems are almost solved.

R

 

[byundt]

Here is a user-defined function that uses RegExp to test whether a string contains a UK postcode. If so, it returns that postcode as a string. Paste the function in a regular module sheet, and use it with a worksheet formula in column B like:
=UKPostCode(A1)
The function will return either a blank or the postcode.

Function UKPostCode(str As String)
Dim RgExp As Variant, objMatches As Variant, obj As Variant
Dim i As Long
Set RgExp = CreateObject("VBScript.RegExp")
UKPostCode = ""
With RgExp
    .Pattern = "(?:[A-Z]{1,2}\d(?:\d|[A-Z])? \d[A-Z]{2})"
    If .test(str) = True Then
        Set objMatches = .Execute(str)
        If Not objMatches Is Nothing Then
            For Each obj In objMatches
                UKPostCode = obj.Value
            Next
        End If
    End If
End With
End Function

Brad

 

[KenWright]

Nice one byundt, even traps the dodgy ones I threw in to try and fool it

Have a shiny

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Davidprince]

One solution although not the most elegant is to type the following in a cell near your data ( ie data in a2-a300,b2-b300,c2-c300 for Town County Postcode ) then in column d type:
=If(c2>1000,c2,"Check")
This will put the postcode into cell c2. This is for four digits, if its more digits change the number to the lowest number with whatever number of digits are in the postcode, ie 5 digits is 10000 6 digits is 100000 etc.
Copy the formula down the column.
Regards
David

 

[KenWright]

UK postcodes are of the form PO4 8DQ, PO21 6BF, SW1 4DF etc

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

byundt - nice but no banana I'm afraid.

UK postcodes should be in the format of:

AANN NAA
A N NAA
AA N NAA
A NN NAA

your function only picks up AANN NAA correctly and identifies AN NAA as a valid postcode

The other thing to think about is that the postcodes may well not be inb a valid format - hence the difficulty - they can look very similar to address strings that contain numbers - that's the reason rafot is in luck - no numeric data other than postcodes so I defined any numerics as belonging to postcodes

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[byundt]

Geoff,
Here is a slightly more sophisticated Regular Expression pattern in a function:

Function UKPostCode(str As String)
Dim RgExp As Variant, objMatches As Variant, obj As Variant
Dim i As Long
Set RgExp = CreateObject("VBScript.RegExp")
UKPostCode = ""
With RgExp
    '.Pattern = "(?:[A-Z]{1,2}\d(?:\d|[A-Z])? \d[A-Z]{2})"
    .Pattern = "^([A-Z]{1,2}[0-9]{1,2}|[A-Z]{3}|[A-Z]{1,2}[0-9][A-Z])( |-)[0-9][A-Z]{2}"
If .test(str) = True Then
        Set objMatches = .Execute(str)
        If Not objMatches Is Nothing Then
            For Each obj In objMatches
                UKPostCode = obj.Value
            Next
        End If
    End If
End With
End Function

I commented out the original pattern, which captured all but one of the following. The new pattern works on all of the following:
AB84 9XZ
A6 7SD same as your "failed example", but with only one space
OP3 7WE same as your "failed example", but with only one space
B54 3RE same as your "failed example", but with only one space
T7 8RT
H9F 3BC
X98 3RU
XCV 1VB this one failed with original pattern, but is considered "rare"
PO12 3TW
IU4W 0QA
According to the Royal Mail, there is only one space:

http://www.royalmail.com/portal/rm/content1?catId=400044&mediaId=9200078

Brad

 

[xlbo]

I like the style and a star for the concept. I'm not saying that isn't how the RM treat postcodes but most database designers would go for a fixed length 8 digit field (or 2 4 digit fields) hence my examples

<thinks>
me needs to know more about RegExp
</thinks>

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

LOL

<thinks>
me needs to know more about RegExp
</thinks>

Ditto

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

Any good tips / websites / books Brad ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[byundt]

The regular expression (RegExp) object is a useful feature of VBScript that can be applied in Excel. Once you create the object, the rest of the syntax is the same as in the subsequent references.

Dim RgExp As Variant
Set RgExp = CreateObject("VBScript.RegExp")

Book that looked good in bookstore (but I haven't read it yet)

http://www.oreillynet.com/pub/a/network/2002/07/15/regexp.html

Web sites that discuss RegExp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting051099.asp

http://msdn.microsoft.com/library/d...tml/vtoriregularexpressionsobjectpropmeth.asp

http://authors.aspalliance.com/brettb/VBScriptRegularExpressions.asp

http://www.juicystudio.com/tutorial/asp/regexp.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vbsWhat.asp

Pattern tester

http://www.electrified.net/dev/RegexEvaluate.aspx

Sample workbook showing use of RegExp in Excel

http://www.vbaexpress.com/forum/showthread.php?t=226

 

[KenWright]

Hmmmm - Now if I could just figure out how to give you more than one damn star

Thanks Brad
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------