UK Formats - National Insurance Nos and Postcodes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

National Insurance numbers are in the format "AANNNNNNA
Postcodes are generally in the format "AANN NAA
Both are formatted as "Text" field
Very often they finish up in lower case and the you have to use a column to convert them to Uppercase
fix them as values and then copy them back to the original column

Is there a format which will convert the input to Uppercase in the original cell (I know this is possibl
in Word forms) and is there any way this format can be added to the "Special" category in cell formats

Thanks.
 
Sean,

I think you need VBA for this, trap the input and upshift it. This would
look like

This code will upshift any input in column E if put in the worksheet code
module

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 5 Then
Target.Value = UCase(Target.Value)
End If

ws_exit:
applicatione.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Sean Bishop said:
National Insurance numbers are in the format "AANNNNNNA"
Postcodes are generally in the format "AANN NAA"
Both are formatted as "Text" fields
Very often they finish up in lower case and the you have to use a column to convert them to Uppercase,
fix them as values and then copy them back to the original column.

Is there a format which will convert the input to Uppercase in the
Click to expand...
original cell (I know this is possible
in Word forms) and is there any way this format can be added to the
Click to expand...
"Special" category in cell formats.
 
You can use data>validation>custom

=AND(EXACT(A1,UPPER(A1)),ISTEXT(A1))

will force the user to use upper letters if he/she types

you could also use an event macro that will convert to upper when you press
enter

http://www.mvps.org/dmcritchie/excel/proper.htm#upper

look for the event macro



--

Regards,

Peo Sjoblom

Sean Bishop said:
National Insurance numbers are in the format "AANNNNNNA"
Postcodes are generally in the format "AANN NAA"
Both are formatted as "Text" fields
Very often they finish up in lower case and the you have to use a column to convert them to Uppercase,
fix them as values and then copy them back to the original column.

Is there a format which will convert the input to Uppercase in the
Click to expand...
original cell (I know this is possible
in Word forms) and is there any way this format can be added to the
Click to expand...
"Special" category in cell formats.
 
Back
Top