Solved: convert number to text

[vzachin]

hi,

is there a way to convert a number such as 8.5 to a text value of 0850 when the number is entered into a field? the numbers entered can be from 1 to 99 and if there is a decimal, it can be 25,50,or 75.

eg: 8, 12, 8.5, 12.75

the text value must always be a length of 4 characters.

the maximum entries in the column of numbers will be 100.


thanks
zach

[mdmackillop]

How about
=TEXT(A3*100,"0000")

[vzachin]

hi malcolm,

nice formula. that works..

can you show me how to write a udf(don't know if that is what it is called, maybe a worksheet change?) so that whenever an entry is made, the cell would evoke that formula?

thanks again
zach

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then

.Value = .Value * 100
.NumberFormat = "0000"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[vzachin]

hi Bob,

thanks for that.

is there a way to make that TEXT instead of Number Format?

zach

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error Goto ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then

.Value = Format(.Value * 100 , "0000")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[vzachin]

hi Bob,

thanks! works great!

zach