formatting a cell for both ZIP and ZIP+4

[Guest]

I am using data validation to ensure the cells in the range are either 5 or 9
digits in length; however, I don't know how to set the formatting of the cell
to recognize whether to use the Zip Code format or the ZIP Code +4 format.

Can I get both?

 

[Ragdyer]

Post the formulas you're using to ensure a 5 or 9 digit entry.

 

[CLR]

Maybe with something like this..........

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Len(Target) = 5 Then
Selection.NumberFormat = "00000"
Else
If Len(Target) = 9 Then
Selection.NumberFormat = "00000-0000"
Else
End If
End If
End Sub

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Try a custom format of
Format|Cells|Number tab|Custom category
[>99999]00000-0000;00000

 

[Guest]

Thank you all for your help!

Try a custom format of
Format|Cells|Number tab|Custom category
[>99999]00000-0000;00000

I am using data validation to ensure the cells in the range are either 5 or 9
digits in length; however, I don't know how to set the formatting of the cell
to recognize whether to use the Zip Code format or the ZIP Code +4 format.

Can I get both?