Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / August 2006

Tip: Looking for answers? Try searching our database.

Format National Insurance Number

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
M-Hop - 03 Aug 2006 13:43 GMT
I am using an excel record for mailmerge and would like to format a national
insurance record.   The text field is for example AA112233B, and I would like
to merge the field divided into separated segments eg.   AA   11  22  33  B.
Dates and numerical data are fine, but I'm not sure exactly what is needed
for alpha-numeric?
Graham Mayor - 03 Aug 2006 15:06 GMT
As far as I am aware, there is no simple way to do this.

The options are:
1. Format it correctly in the Excel data file
or.
2. Derive three additional fields from the NI field using LEFT RIGHT and MID
functions in Excel to separate the three parts of the number i.e. 2 letters
6 numbers 1 letter and merge those fields - wherein you can format the
number field in Word with a switch
http://www.gmayor.com/formatting_word_fields.htm
or
3. Merge to a new document and run a macro to format the number:

Sub FormatNI()
   Selection.Find.ClearFormatting
   Selection.Find.Replacement.ClearFormatting
   With Selection.Find
       .Text = "([A-Z]{2})([0-9]{2})([0-9]{2})([0-9]{2})([A-Z])"
       .Replacement.Text = "\1 \2 \3 \4 \5"
       .Forward = True
       .Wrap = wdFindContinue
       .Format = False
       .MatchCase = False
       .MatchWholeWord = False
       .MatchAllWordForms = False
       .MatchSoundsLike = False
       .MatchWildcards = True
   End With
   Selection.Find.Execute replace:=wdReplaceAll
End Sub

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> I am using an excel record for mailmerge and would like to format a
> national insurance record.   The text field is for example AA112233B,
> and I would like to merge the field divided into separated segments
> eg.   AA   11  22  33  B. Dates and numerical data are fine, but I'm
> not sure exactly what is needed for alpha-numeric?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.