How can you make a postcode have a gap in the middle all down a co

I

Ianwal

Help please as daily we produce a list of addresses and post codes and some
clients put a space between the numbers and some do not. We need a space
always
 
S

Sandy

Try this in an empty column - it assumes the "raw" postcode is in A1

=IF(MID(A1,LEN(A1)-3,1)=" ",A1,LEFT(A1,LEN(A1)-3)&" "&RIGHT(A1,3))

and if by chance you want them all upper case use:

=UPPER(IF(MID(A1,LEN(A1)-3,1)=" ",A1,LEFT(A1,LEN(A1)-3)&" "&RIGHT(A1,3)))

Sandy
 
D

David Biddulph

What is the definition of where you want the space? What format are your
postcodes, how many numbers, are there letters as well? Perhaps if you said
which country we could see what the format is in that country. In some
countries (such as the UK) there are a few non-standard codes which might
take a fair amount of additional effort to include, if you wished to do so.

Note that it isn't advisable to try to put too long a description of your
problem in the subject line, as it will probably be truncated, as in your:
"How can you make a postcode have a gap in the middle all down a co"
The message body is the place for the detailed question.
If you do literally want "a gap in the middle", you'll need to define how to
deal with an odd number of characters, if that is a permitted format.
 
G

Gord Dibben

Canadian postal code?

To change existing entries.

=UPPER(LEFT(G1,3)&" "&RIGHT(G1,3))

For new entries you could use event code to change them as you entered them.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 7 Then Exit Sub 'adjust column to suit
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3))
End With
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code. Select the sheet tab and "View Code".

Copy/paste the above into that module.

As written operates only on column G........Target.Column <> 7


Gord Dibben MS Excel MVP
 
J

jatman

I like the code, but the code will not be applied to a column, but only cell
D13. how could the code change from

If Target.Column <> 7 Then Exit Sub

to just a target cell?
 
Top