What is happening here is not an Excel bug, but rather a computer science issue regarding Double Precision Floating Point numbers, with 15 maximum digits, a potential problem even if the cell is preformatted as text.
To get around that we can use VBA with a TextBox, or to keep things simple, an InputBox.
Two items I do not understand...
You wrote:
"I want the formula to return:
027-123456-0000-000000-000000-0000-0000
The computer puts:
000-000000-0000-000000-000000-2712-3456
The number will ALWAYS start with 0 and end with 0000."
Later in this thread, you wrote:
"Entered:
123123456123412345612345612341234 which should result in
123-123456-1234-123456-123456-1234-1234"
I do not understand why the latter scenario should result as you say, if there is no leading zero and no trailing 4 zeros like you first said.
I do not know what you would expect if more than 33 characters are entered (33 based on how you presented the layout, not including the dashes).
If the latter scenario (no leading zero) is really what you want
and
If you want the value to be truncated for the first 33 characters if more than 33 are attempted
then see if this helps.
Right click on your sheet tab, left click on View Code, and paste the following code into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Public RangeMisc As Range
Public RangeRev As Range
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Range("MISC")
Set RangeMisc = .Offset(0, 4).Resize(.Rows.Count, 1)
End With
With Range("REV")
Set RangeRev = .Offset(0, 4).Resize(.Rows.Count, 1)
End With
If Intersect(Target, RangeMisc) Is Nothing _
And Intersect(Target, RangeRev) Is Nothing Then
Set RangeMisc = Nothing: Set RangeRev = Nothing
Exit Sub
Else
Application.EnableEvents = False
Cancel = True
Dim TargAdd$, MyValRaw$, MyValFormat$
TargAdd = Target.Address(0, 0)
MyValRaw = _
InputBox("Enter your value for " & TargAdd & ":", _
"What do you want to enter into cell " & TargAdd & "?")
Select Case True
Case StrPtr(MyValRaw) = 0
MsgBox "You hit Cancel.", 48, "Entry cancelled for " & TargAdd
Case Len(MyValRaw) = 0
MsgBox "You hit OK but entered nothing.", 48, "Entry scuttled for " & TargAdd
Case Else
If Len(MyValRaw) < 33 Then
Dim MyValLen%
MyValLen = 33 - Len(MyValRaw)
MyValRaw = MyValRaw & WorksheetFunction.Rept("0", MyValLen)
End If
MyValFormat = _
Chr(39) & _
Left(MyValRaw, 3) & "-" & _
Mid(MyValRaw, 4, 6) & "-" & _
Mid(MyValRaw, 10, 4) & "-" & _
Mid(MyValRaw, 14, 6) & "-" & _
Mid(MyValRaw, 20, 6) & "-" & _
Mid(MyValRaw, 26, 4) & "-" & _
Mid(MyValRaw, 30, 4)
Target.Value = MyValFormat
End Select
Application.EnableEvents = True
Set RangeMisc = Nothing: Set RangeRev = Nothing
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
With Range("MISC")
Set RangeMisc = .Offset(0, 4).Resize(.Rows.Count, 1)
End With
With Range("REV")
Set RangeRev = .Offset(0, 4).Resize(.Rows.Count, 1)
End With
If Intersect(Target, RangeMisc) Is Nothing _
And Intersect(Target, RangeRev) Is Nothing Then
Set RangeMisc = Nothing: Set RangeRev = Nothing
Exit Sub
Else
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Please double-click cell " & Target.Address(0, 0) & vbCrLf & _
"to enter a value into it.", 64, "DoubleClick needed as entry method"
Set RangeMisc = Nothing: Set RangeRev = Nothing
End If
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Note, this code will adjust itself for the named ranges if rows are inserted or deleted, which is also what I think you requested.
Also, I'd recommend instead of the Change event that you establish a data validation message box when a cell in column E of those named ranges is selected, so people will not enter in a long value only to be told later it must be entered with an inputbox. I added the Change event here anyway, just to cover our bases temporarily, or permanently if you want to keep it that way.
