POSTCODE FORMAT HELP!!!

[tucker]

Does anyone no how to write a macro to reformat postcodes to be the
same arrangement. The final postcodes must be no longer than 8
characters. There must always be a space in between the outward and
inward code, for example
• B4_7ET
• OX8_5KQ
• OX28_5KQ

The problem is, the list of postcodes which have been collected have
mixed formats, e.g,

• B_4_7ET
• B4_ _7ET

Can anyone help me please ASAP x

 

[Paul Herber]

Does anyone no how to write a macro to reformat postcodes to be the
same arrangement. The final postcodes must be no longer than 8
characters. There must always be a space in between the outward and
inward code, for example
• B4_7ET
• OX8_5KQ
• OX28_5KQ

The problem is, the list of postcodes which have been collected have
mixed formats, e.g,

• B_4_7ET
• B4_ _7ET

Can anyone help me please ASAP x

here's a very quick algorithm assuming all else is correct apart from
this spacing:
substitute every <space> with <nothing>
x = string length
insert space at x-3

In real life I wouls also convert the whole string to upper case, go
though each character in the string and remove any character not in
[A..Z, 0..9], check that ths string is either 6 or 7 characters, then
insert the space. You might also like to do a regular expression check
on the final result as well as check the correctness of the initial
one/two letters
(P.S. this is for UK format postcodes)

 

[G-Money]

You could also do you checking when the user inputs the codes. E.g.,
have two separate fields for inward and outward.

The rule to insert a space at character three does not seem
appropriate considering your example B4 does not have three
characters.

Instead of removing all spaces you could replace double spaces with a
single space, then go through and remove spaces between a letter and a
number (I am assuming the inward and outward codes each consist of a
letter prefix and a number suffix).

Also, you could do:

MyCode = UCase(MyCode)

to make the entire code uppercase.

I have not tried this, but the following is a best for the whole
thing:

MyCode = UCase(MyCode) ' all uppercase
MyCode = Replace(MyCode, " ", " ") 'two spaces to one
For i = 1 To Len(MyCode)
If MyCode(i) = " " And MyCode(i-1) Like ("[A-Z]") Then
'do something to remove this particular space
EndIf
Next i

 

[G-Money]

Should have been "best guess"

 

[John Marshall, MVP]

Paul's code threw me the first time I saw it, but he is right. He is not
adding a space at the third position, but adding a space before the last
three characters.

Of course, knowing the country these postal codes apply to would help.

John... Visio MVP

Need stencils or ideas? http://visio.mvps.org/3rdparty.htm
Need VBA examples? http://visio.mvps.org/VBA.htm
Visio Wishlist http://visio.mvps.org/wish_list.htm

 

[Paul Herber]

Paul's code threw me the first time I saw it, but he is right. He is not
adding a space at the third position, but adding a space before the last
three characters.

Of course, knowing the country these postal codes apply to would help.

UK format, John.

 

[G-Money]

Wikipedia has a nice discussion of UK postal codes.

http://en.wikipedia.org/wiki/UK_postcodes#Validation

They are not simple. Wikipedia gives some examples of regular
expressions that could be used. The only consistent feature appears
to be that the the second part always has three characters, so one
could remove all spaces and then add a space three characters from the
end.

MyCode = Replace( MyCode, " ", "") 'remove all spaces
MyCode = Left(MyCode,Len(MyCode)-3) & " " & Right(MyCode, 3) 'add
space back in at three characters from the end

Hope this helps,
Garrett