Formatting text and numbers in a cell

[Sean Bishop]

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]

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

 

[Teethless mama]

=LEFT(A1,2)&" "&MID(A1,3,2)&" "&MID(A1,5,2)&" "&MID(A1,7,2)&" "&RIGHT(A1,1)

 

[Sean Bishop]

Dave Peterson,

Thank you for your response to my question. It worked and did exactly what I
wanted it to do.
Typically it is only when you see the answer that you realise that you did
not phrase the question fully. I should have said that the spreadsheet is
laid out like a form and the question applied to one specific cell.
However I now have something I can work upon and also a template if I have
any further formatting problems.
Again, thank you.
Sean Bishop

 

[Dave Peterson]

If it's a single cell, you can change this line:

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