I want to know how to format a cell so it only takes the following
information...

Char.Char,Int,Int <space Int,Char, Char

So for example it would look like this...

NR01 2RJ

I also want the characters changed to capitals if they were entered in
lowercase...

I'm sure this is an easy one... but I cant find the solution anywhere...

Scott

Hi Scott,

Probably better to do it with event code as I don't think any format will
upshift

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = UCase(.Value)
.Value = Left(.Value, 4) & " " & Right(.Value, 3)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Scott" wrote in message
...
I want to know how to format a cell so it only takes the following
information...

Char.Char,Int,Int <space Int,Char, Char

So for example it would look like this...

NR01 2RJ

I also want the characters changed to capitals if they were entered in
lowercase...

I'm sure this is an easy one... but I cant find the solution anywhere...

Scott

Scott wrote:
Char.Char,Int,Int <space Int,Char, Char

Remember the Tiswas postcode, B1 2JP?

You format is wrong, as I may have mentioned befo

http://www.evoxfacilities.co.u*k/evoxps.htm

Jamie.

--

Do you mean you want to impose data validation as well as formatting and caps?
Bob's code will do that latter, but not the former.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As String

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
X = UCase$(.Value)
If X Like "[A-Z][A-Z]## #[A-Z][A-Z]" Then
'it's OK as is
ElseIf X Like "[A-Z][A-Z]###[A-Z][A-Z]" Then
X = Left$(X, 4) & " " & Right$(X, 3)
Else
Msgbox "Incorrect format: AA## #AA", vbokonly, "Error!"
'leave the string as-is so they can correct without
'retyping the whole thing
End If
.Value = X
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub




On Tue, 15 Mar 2005 09:58:22 GMT, "Scott" wrote:

I want to know how to format a cell so it only takes the following
information...

Char.Char,Int,Int <space Int,Char, Char

So for example it would look like this...

NR01 2RJ

I also want the characters changed to capitals if they were entered in
lowercase...

I'm sure this is an easy one... but I cant find the solution anywhere...

Scott