Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-13-2013, 11:26 AM
Chayes Chayes is offline Identifying Postcodes. Windows XP Identifying Postcodes. Office 2003
Advanced Beginner
Identifying Postcodes.
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default Identifying Postcodes.

Hi All



I'm trying to create a search and replace which will identify postcodes from an MSword document and move each to the line below.

It's proving problematic because of the various formats the codes can take :
  • LN NLL
  • LLN NLL
  • LNN NLL
  • LLNN NLL
  • LLNL NLL
  • LNL NLL
Where L represents a letter, and N a number. Those in my pages can be in any of these forms , or have the space missing.

Can someone help with some search and replace code which will identify them , and move each to the line below , please?

Thanks
Reply With Quote
  #2  
Old 09-16-2013, 04:26 AM
OTPM OTPM is offline Identifying Postcodes. Windows 7 32bit Identifying Postcodes. Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Chayes
You are not explaining the criteria to check for.
Can you explain with an example what you are looking to achieve.
Tony
Reply With Quote
  #3  
Old 09-17-2013, 06:49 AM
Chayes Chayes is offline Identifying Postcodes. Windows XP Identifying Postcodes. Office 2003
Advanced Beginner
Identifying Postcodes.
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default

Hi Tony

Ok thanks for getting back.

I have pages of addresses which need to have the postcode identified in each and moved down a line.

The postcodes are in the format outlined in my original post. I've used 'L' to signify a letter and 'N' for a number.

So for example

LN NLL could be M6 7TY , or E3 4GH

A search and replace or some VBA to find the various combinations or Numbers and Letters would be the solution. I assume wildcards would be used.

Hope you can help.

Reply With Quote
  #4  
Old 09-17-2013, 08:44 AM
OTPM OTPM is offline Identifying Postcodes. Windows 7 32bit Identifying Postcodes. Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
The solution will depend on the layout of your data. Can you post a sample of your data.
Thanks
Tony
Reply With Quote
  #5  
Old 09-17-2013, 11:00 AM
Chayes Chayes is offline Identifying Postcodes. Windows XP Identifying Postcodes. Office 2003
Advanced Beginner
Identifying Postcodes.
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default

Hi Tony

The format is a simple plain text. No elaborate formatting or tables involved.

The addreses are one per page typically in this style :

Stephen Jones
44 , Allen Avenue
Stevenage
Herts LL27 3QP

Having run the search it would hopefully be returned in this format :

Stephen Jones
44 , Allen Avenue
Stevenage
Herts
LL27 3QP

As I said preciously the format of the postcode does vary , and sometimes the space is missing. This provides the complexity of the issue.

Grateful for your help.
Reply With Quote
  #6  
Old 09-18-2013, 03:51 AM
OTPM OTPM is offline Identifying Postcodes. Windows 7 32bit Identifying Postcodes. Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
Cut and paste this code into a module in your document and this should do the trick for you:
Sub Macro1()
'
' Macro1 Macro
'
'
Selection.HomeKey Unit:=wdStory
pCount = ActiveDocument.Paragraphs.Count
For Count = 1 To pCount
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "([A-Z]{1,2})([0-9]{1,2})"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeParagraph
Selection.MoveDown Unit:=wdLine, Count:=1
Next Count
End Sub

Good luck.
Tony
Reply With Quote
  #7  
Old 09-18-2013, 06:02 AM
Chayes Chayes is offline Identifying Postcodes. Windows XP Identifying Postcodes. Office 2003
Advanced Beginner
Identifying Postcodes.
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default

Hi Tony

Ok thanks very much for this. I'm impressed!

It's finding the postcodes with no issue , but is placing them 4 lines below the rest of the address. For example :

Stephen Jones
44 , Allen Avenue
Stevenage
Herts LL27 3QP


is becoming

Stephen Jones
44 , Allen Avenue
Stevenage
Herts



LL27 3QP


Is there a way to fix this so the postcode goes to the line below?

Thanks again.

Last edited by Chayes; 09-18-2013 at 06:03 AM. Reason: Spelling
Reply With Quote
  #8  
Old 09-18-2013, 07:04 AM
OTPM OTPM is offline Identifying Postcodes. Windows 7 32bit Identifying Postcodes. Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Quote:
Originally Posted by Chayes View Post
Hi Tony

Ok thanks very much for this. I'm impressed!

It's finding the postcodes with no issue , but is placing them 4 lines below the rest of the address. For example :

Stephen Jones
44 , Allen Avenue
Stevenage
Herts LL27 3QP


is becoming

Stephen Jones
44 , Allen Avenue
Stevenage
Herts



LL27 3QP


Is there a way to fix this so the postcode goes to the line below?

Thanks again.
This is to do with the line spacing setting in your document i believe nit the coding.
Change the line spacing.
Tony
Reply With Quote
  #9  
Old 09-18-2013, 11:53 AM
Chayes Chayes is offline Identifying Postcodes. Windows XP Identifying Postcodes. Office 2003
Advanced Beginner
Identifying Postcodes.
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default

Hi Tony

Ok I had another go with it. I'm just using plain text with no line spacing. It does seem to be putting 4 returns. Strange one. I've found with some experimentation that if I rem the Count and pCount lines that it does only move it down the one line as required.

At any rate thanks again for your time and expertise.

Best Wishes
Reply With Quote
  #10  
Old 09-19-2013, 02:21 AM
OTPM OTPM is offline Identifying Postcodes. Windows 7 32bit Identifying Postcodes. Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Chayes
Thanks for the feedback. I have modified the code to take into account the formatting/line spacing issues you are experiencing. Replace the code I gave you yesterday with this one and it should resolve your issues:

Sub Macro1()
'
' Macro1 Macro
'
'
Selection.HomeKey Unit:=wdStory
pCount = ActiveDocument.Paragraphs.Count
For Count = 1 To pCount
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "([A-Z]{1,2})([0-9]{1,2})"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeParagraph
Selection.MoveDown Unit:=wdLine, Count:=1
Next Count
Selection.WholeStory
With Selection.ParagraphFormat
.SpaceBefore = 0
.SpaceAfter = 0
End With
Selection.HomeKey Unit:=wdStory
End Sub

Good luck.
Tony
Reply With Quote
  #11  
Old 09-19-2013, 06:45 AM
Chayes Chayes is offline Identifying Postcodes. Windows XP Identifying Postcodes. Office 2003
Advanced Beginner
Identifying Postcodes.
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default

Hi Tony

Ok that's great. Thanks again for your help.

Best wsihes
Reply With Quote
  #12  
Old 09-20-2013, 04:37 PM
Chayes Chayes is offline Identifying Postcodes. Windows XP Identifying Postcodes. Office 2003
Advanced Beginner
Identifying Postcodes.
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default

Hi Tony

I just found a small anomally. My fault.

I sometimes have codes on the same page as the addresses. They always involve the letters 'BX'.

For example

12571BX175

I'm finding the coding is picking this up in error , and identifying as a postcode.

Could the code be made to ignore anything with the letters BX in? Hope you don't mind.
Reply With Quote
  #13  
Old 09-21-2013, 02:57 AM
OTPM OTPM is offline Identifying Postcodes. Windows 7 32bit Identifying Postcodes. Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
As a quick fix because I am not at home follow these steps:
Search for ^pBX
Replace with BX
This will resolve your issue.
Good luck.
Tony
Reply With Quote
  #14  
Old 09-21-2013, 06:29 AM
Chayes Chayes is offline Identifying Postcodes. Windows XP Identifying Postcodes. Office 2003
Advanced Beginner
Identifying Postcodes.
 
Join Date: May 2012
Posts: 79
Chayes is on a distinguished road
Default

HI Tony

Ok I managed a fix by making the BX lower case , and making the postcode coding only look for upper case. Not for the purist I agree , but it's working.

Best Wishes
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Identifying Postcodes. Identifying Irregular Characters d4okeefe Word VBA 2 05-09-2013 03:14 PM
Identifying Postcodes. Identifying and counting words monique Word 1 08-30-2012 05:33 AM
Identifying the Dynamic Connector cyberbhai Misc 0 12-14-2005 03:55 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:32 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft