Gos-C
Try this macro - It does not verify Letters & Number are placed in the correct order - This can be added if required.
Copy this macro
Goto Excel
Right Click on Sheet Name Tab > select View Code
Past macro into the Worksheet Module displayed
Change Column number to suit (shown In red in this posting)
Return to Excel and test
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
For Each Rng In Target
If Rng.Column = 1 Then
Application.EnableEvents = False
Select Case Len(Rng.Value)
Case 6
Rng.Value = UCase(Left(Rng.Value, 3) & " " & Right(Rng.Value, 3))
Case 7
Rng.Value = UCase(Rng.Value)
End Select
End If
Next Rng
Application.EnableEvents = True
End Sub