You won't be able to do that with Data Validation alone.

Without DV just format column N as Number with 2 DP and use comma separator.

When user enters 123456.78 it will become 123,456.78

To actually divide the number will have to done via event code.

If using event code why not do the formatting at the same time?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("N:N")) Is Nothing Then
With Target
If .Value < "" Then
.Value = .Value / 1000
.NumberFormat = "#,##0.00"
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP


On Thu, 29 Nov 2007 15:55:31 GMT, "sidm via OfficeKB.com" <u38329@uwe wrote:

Hi ,
I would like that when a number is entered in column N the person entering
the column is prompted to enter in format 100,000.00 ( ex 250,225) and then
once the number is entered it is converted in format 250.26 i.e. divided by
1000 and rounded off to two decimal places.