Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can you make a postcode have a gap in the middle all down a co
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can you make a postcode have a gap in the middle all down a co
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 "Ianwal" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can you make a postcode have a gap in the middle all down a co
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. -- David Biddulph "Ianwal" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can you make a postcode have a gap in the middle all down a co
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 On Tue, 3 Feb 2009 03:35:01 -0800, Ianwal wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can you make a postcode have a gap in the middle all down
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? "Gord Dibben" wrote: 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 On Tue, 3 Feb 2009 03:35:01 -0800, Ianwal wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Postcode distances | Excel Worksheet Functions | |||
M25 postcode | Excel Worksheet Functions | |||
postcode | Excel Discussion (Misc queries) | |||
UK Postcode formula | Excel Worksheet Functions | |||
HELP WITH POSTCODE FORMATTING | Excel Worksheet Functions |