format cell

  • Thread starter Thread starter The Other Mike
  • Start date Start date
T

The Other Mike

I have a cell that I need to enter a value with 3 numbers or 3 numbers and a
letter.

1) The user enters 002 and it formats it to D002/2009. the format cell
reads "D"000"/2009".
2) Now I need to enter 002A and need it formated to D002A/2009.
What is the formating for problem 2 and can I have the cell formatted for
both situations?

Thanks
Mike
 
You can set up a custom format like this:

"D"000"/2009";;"D"@"/2009"

to cover both situations.

Note that if you are entering purely numbers, like 002, you only need to
enter 2, but you need to enter the leading zeroes if you are entering text.
The results show like this:

Entered Displayed
3 --> D003/2009
003A --> D003A/2009

Hope this helps.

Pete
 
One way. Right click sheet tab>view code>insert this.

Private Sub Worksheet_Change(ByVal Target As Range)
'format input range as TEXT and restrict if desired.
x = Len(Application.Trim(Target))

Application.EnableEvents = False
If x < 3 Or x > 4 Or _
x = 4 And IsNumeric(Right(Target, 1)) Then
Target.Value = ""
Application.EnableEvents = True
Exit Sub

End If
If x = 3 Or Not IsNumeric(Right(Target, 1)) _
Then Target.Value = "D" & Target & "/2009"
Application.EnableEvents = True
End Sub
Sub Fixit()
Application.EnableEvents = True
End Sub
 
Both will work.

Thanks for the help.

Pete_UK said:
You can set up a custom format like this:

"D"000"/2009";;"D"@"/2009"

to cover both situations.

Note that if you are entering purely numbers, like 002, you only need to
enter 2, but you need to enter the leading zeroes if you are entering
text. The results show like this:

Entered Displayed
3 --> D003/2009
003A --> D003A/2009

Hope this helps.

Pete
Click to expand...
 
Back
Top