Extract Postcode from a string

[uuccu]

Hi, I have a csv file full of contact details for many people, but there is no seperate field for the postcode (I need the postcode to search for that contact in another excel file).

So I have a string that contains all the address details i.e.
Mr and Mrs Ian Gordon #\n6 Devenish Crescent#\nEnniskillen#\nN. Ireland#\nBT74 4RB#\n

Bearing in mind that not all contacts have the same number of address fields (#/n used to denote a new line) some may only have name and postcode.

So I guess I need a script that will check for 2 capital letters followed by a 2 digit number, a space then a single digit number and 2 more capital letters.

Preferably in VBA FOR EXCEL

 

[xlbo]

Your logic will fall down - what about London postcodes ?? what about Birmingham or Manchester that only have 1 letter
Let me explain.....
There are multiple postcode formats...

A1 1XY
AB1 1XY
A12 1XY
A 1 1XY
AB 1 1XY
A 1 1XY
AB1C 1XY (Anomolous London format)

All perfectly acceptable but very tricky to provide logic for - your best bet would be to search for a space then a number, then 2 letters but can you be sure that this pattern won't happen in another part of the data - address line1 for instance - if a space was omitted, you could easily have
#\n6 4Orchard Lane
or similar - which would then match the postcode pattern....In fact, your \n6 could give you a problem as well because it looks like the start of a Newcastle postcode.....
I think you need to think about another way of doing this - especially if you cannot guarentee what fields will be returned for each contact

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[woja]

Beware not all post-codes are 2-letters/2-digits ate the start: there's W7 2TT (West London), to start with; and SW17 12QQ is also possible.

Are you certain that the post code will always be in capitals in the CSV file?

If you have the string as you show it (with newlines as [tt]Chr(13)[/tt] and the postcode is always last (i.e., the item before the last new line), then I would suggest:

   Public Function getPostcode(ByVal aString as String) As String
      Dim iNl As Long
      If (Right(aString, 1) = Chr(13)) Then
         ' Remove last new-line if it's there
         aString = Left(aString, Len(aString) - 1)
      End If
      ' Find the rightmost remaining newline
      iNl = InstrRev(aString, Chr(13))
      If (iNl > 0) Then
         getPostcode = Mid(aString, iNl + 1)
      Else
         getPostcode = ""
      End If
      Exit Function
   End Function

If you're using an Excel version prior to Excel2000, the [tt]InstrRev[/tt] function is not available and you'll have to write one yourself.

Hope this helps

________________________________________
Roger J Coult; Grimsby, UK
In the game of life the dice have an odd number of sides.

 

[woja]

Whoops "SW17 12QQ" not possible; I was thinking of "SW7A 1BB" form (which is also strange).

________________________________________
Roger J Coult; Grimsby, UK
In the game of life the dice have an odd number of sides.

 

[TonyJollans]

Hi IanGORDON,

As the others have said, checking for the format is a bit tricky, but ..

If all your addresses end in #\n[valid postcode]#\n then you could check the end of the address string something like this ..

IF Position of first #\n in the last 9 characters is position 9 THEN
Last 9 characters are an 8-character postcode followed by #\n
ELSE
Last 8 characters are EITHER #\n[6-char-postcode]#\n OR [7-char-postcode]#\n
ENDIF

You can remove #\n characters either using the CLEAN or, more explicitly, using the SUBSTITUTE function. So, if your address is in A1, then this in B1 should give you the postcode:

=IF(FIND("#\n",RIGHT(A1,9))=9,SUBSTITUTE(RIGHT(A1,9),"#\n","&quot,SUBSTITUTE(RIGHT(A1,8),"#\n","&quot)

But remember it assumes you always have a valid postcode - if not it might be possible to tweak it - depends just how clean your data are, I guess.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[uuccu]

Many thanks
Ian G

 

[Loomah]

Here's another possibility
This relies on the 'markers' #\n appearing before and after the postcode, ie you have #\nPOST CODE#\n then nothing else. I've tested this on the example pcs that xlbo posted.

Sub lime()
Dim c As Range
Dim iPos As Integer
For Each c In Range("B1:B7")
iPos = InStrRev(c.Text, "\", Len(c) - 3)
c.Offset(0, 1) = Trim(Mid(c.Text, iPos + 2, Len(c.Text) - iPos - 4))
Next
End Sub

Range("B1:B7&quot is where I put my test data. The postcodes would appear in the next column after running the code.

Hope this helps
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

Thinking about it - Tony's reponse gives me an idea

Lets say you load the file into excel or set a string variable = the whole line of text
so

Function PCode(AddStr as string)
enPos = InStrRev(AddStr, "#\n&quot - 1
stPos = InStrRev(AddStr, "#\n", enPos - 1) + 3
PCode = Mid(AddStr, stPos, enPos - stPos)
end function

Sub GetPostcodes()
'Set up loop to go thru csv
'assign address string to variable (call it myStr)
myPostcode = Pcode(myStr)
'do stuff
Loop

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[uuccu]

Hey guys thanks for all your input, ive been able to use this code for the postcode problem:


Sub PCodes()
For Each cell In Selection
pcode1 = Right(cell.Value, 7)
If Left(pcode1, 1) Like "[A-Z]" Then
cell.Offset(0, 1).Value = pcode1
Else: pcode2 = Right(pcode1, 6)
cell.Offset(0, 1).Value = pcode2
End If
Next cell
End Sub

First i delete all the spaces in the column (and the #/n ) thencount over 6 or 7 characters over from the right most character and copy the characters that make up the post code to next column

Sub Pcodes2()
For Each cell In Selection
Confirm = Right(cell.Value, 3) Like "#[A-Z][A-Z]"
If Confirm = True Then
cell.Offset(0, 1).Value = "PostCode Confirmed"
ElseIf Confirm = Flase Then
cell.Offset(0, 1).Value = "PostCode Failed"
ElseIf Confirm = Null Then
cell.Offset(0, 1).Value = "PostCode Field"
End If
Next cell
End Sub
This Sub checks the format of the copied cells (after selection)

 

[uuccu]

Now my next problem is even more annoying...

I need to get the name of the person into a seperate field and if possible the initials (and or forename) as well...

Mr and Mrs Ian Gordon #\n6 Devenish Crescent#\nEnniskillen#\nN. Ireland#\nBT74 4RB#\n

ie perhaps a script that will delete any text after the first #/n leaving just

Mr and Mrs Ian Gordon

then copy the text from the first space found on the right side, ie Gordon into a variable surname, and again for the forename copy from the 2nd right most space character.


Make any sense at all?

The data is fairly messy so i expect ill have to do a lot of manual checking anyway but any work that can be done by scripts would be great,
thanks again,
Ian G

 

[Loomah]

Ian
Not 100% sure this is what you're after but

Sub mit()
Dim strFull As String, strFirst As String, strLast As String
Dim strTemp As String

strFull = Trim(Left(Range("A12"), InStr(1, Range("A12"), "#") - 1))
strLast = Trim(Right(strFull, Len(strFull) - InStrRev(strFull, " ")))
strTemp = Trim(Left(strFull, InStrRev(strFull, " ")))
strFirst = Trim(Right(strTemp, Len(strTemp) - InStrRev(strTemp, " ")))

MsgBox strFull & vbCrLf & strFirst & vbCrLf & strLast

End Sub

I put the example line you gave into cell A12 then ran this code. It's quite messy but it should give you some idea of where you're heading.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

Hi Ian,

Getting the name is easy. In a cell ..

=LEFT(A1,FIND("#\n",A1)-1)

.. or in code ..

=LEFT(Cell.Value,Instr(Cell.Value,"#\n")-1)

Now, if you have Excel 2K, you can use the InStrRev (in VBA) to strip off the last word ..

Surname = Mid(

Name

, instrrev(

Name

, " ") + 1)

where Name is the result of the earlier LEFT function.

You use the same technique to get the first half of the name ("Mr and Mrs Ian&quot and then repeat the process to strip the last word (or the first letter of the last word of the result)

.. and on, and on, ....

Hope that all makes sense.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[uuccu]

Again many thanks Guys,

Tony thats excellent exactly what I need, but thanks to everyone!!