Number Formatting only works with numbers.

But you could use an event macro that does the work for you. It'll actually
change the value of the string so that it includes those space characters.

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the code window that just
opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String

With Target
If .Cells.Count 1 Then Exit Sub
If .HasFormula Then Exit Sub
If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

If Len(.Value) = 9 Then
'do it
'@@ ## ## ## @

myStr = Left(.Value, 2) & " " _
& Mid(.Value, 3, 2) & " " _
& Mid(.Value, 5, 2) & " " _
& Mid(.Value, 7, 2) & " " _
& Left(.Value, 1)
'make those letters uppercase???
myStr = UCase(myStr)
Application.EnableEvents = False
.Value = myStr
End If
End With

ErrHandler:
Application.EnableEvents = True

End Sub

This code modifies any entry in column A. Change this line to look at the range
you want:

If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then

And delete that UCase() line if you don't want it automatically changed to upper
case.

Some references:

David McRitchie's intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm


Sean Bishop wrote:

I am using Excel 2000

I use a reference which is set up as 2 alpha, 6 numerics and one alpha
(aannnnnna). It is the National Insurance (Social Security) Number given to
everyone at age 16.

I wanted to use a format to ensure that the item was being entered properly
and I would also like it to be displayed as 'aa nn nn nn a' (the normal
method of notation).

I tried setting up custom formats '@@ ## ## ## @' and '@@ @@ @@ @@ @'
neither of which worked, nor could I find any formats of this type.

Is there a way of setting up a custom format that will a format of this type.

May I thank you in advance for your help.

Sean Bishop

--

Dave Peterson