Excel change format in cell
I have created a form where users enter a part numer like 12345678. The visable output (same cell) should be 1234-56-78. This I have accomplisked via excel format cells and mask ####-##-##.
The problem now is that the part number can be longer like 1234567890 and should be displayed as 1234-56-7890.
How do I accomplish this?
Thank you!
The problem now is that the part number can be longer like 1234567890 and should be displayed as 1234-56-7890.
How do I accomplish this?
Thank you!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Would this mean that a number is either 8 char long and then it has 2 char to the right or 10 char and tren it has 4 char to the right?
you could try this:
it will change format acording to lenghts of entered field
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myString As String
Dim cel As Range, TARG As Range
Dim KOLOM As Integer
Application.EnableEvents = False
On Error Resume Next
Set TARG = Intersect(Target, Range("A:A")) 'Obviously you can change this target range to your desired range
KOLOM = TARG.Column
If TARG Is Nothing Then
Application.EnableEvents = True
Exit Sub
Else
myString = TARG
End If
If Len(myString) < 9 Then
Target.NumberFormat = "####-##-##"
Else
Target.NumberFormat = "####-##-####"
End If
End Sub
Kind regards