I would like to truncate a cell as the user is entering data. All the number
formating techiques round the data up to the next value but I just want to
truncate it.

I see the formula =TRUNC(A1,4) will work but I cannot figure out how to use
the formula as a format. If the user is entering data into A1, it will
change A1's format to number when they enter data.

Any help will be appreciated.

I believe you would have to use VBA event code to truncate when the number
is entered.

Anything else will require a helper cell.

Example code...................no error trapping for text or dates

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) 4 Then
.Value = Left(.Value, 4)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 16 Feb 2009 12:16:08 -0800, joerut
wrote:

I would like to truncate a cell as the user is entering data. All the number
formating techiques round the data up to the next value but I just want to
truncate it.

I see the formula =TRUNC(A1,4) will work but I cannot figure out how to use
the formula as a format. If the user is entering data into A1, it will
change A1's format to number when they enter data.

Any help will be appreciated.

That was great and quick. I have never used Macros before so it took some
time to figure out how but it works great.

One question. Is there any way to just truncate the decimal option of the
value?

I really appreciate the help. Thank you , thank you, thank you.

"Gord Dibben" wrote:

I believe you would have to use VBA event code to truncate when the number
is entered.

Anything else will require a helper cell.

Example code...................no error trapping for text or dates

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) 4 Then
.Value = Left(.Value, 4)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 16 Feb 2009 12:16:08 -0800, joerut
wrote:

I would like to truncate a cell as the user is entering data. All the number
formating techiques round the data up to the next value but I just want to
truncate it.

I see the formula =TRUNC(A1,4) will work but I cannot figure out how to use
the formula as a format. If the user is entering data into A1, it will
change A1's format to number when they enter data.

Any help will be appreciated.

Gord,

I figured it out by writing some VB code and it works great.
Thanks again.

Joe

"Gord Dibben" wrote:

I believe you would have to use VBA event code to truncate when the number
is entered.

Anything else will require a helper cell.

Example code...................no error trapping for text or dates

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) 4 Then
.Value = Left(.Value, 4)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 16 Feb 2009 12:16:08 -0800, joerut
wrote:

I would like to truncate a cell as the user is entering data. All the number
formating techiques round the data up to the next value but I just want to
truncate it.

I see the formula =TRUNC(A1,4) will work but I cannot figure out how to use
the formula as a format. If the user is entering data into A1, it will
change A1's format to number when they enter data.

Any help will be appreciated.