View Full Version : Solved: convert number to text
vzachin
11-22-2009, 10:50 AM
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
11-22-2009, 11:32 AM
How about
=TEXT(A3*100,"0000")
vzachin
11-22-2009, 01:59 PM
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
11-22-2009, 02:20 PM
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
11-22-2009, 02:57 PM
hi Bob,
thanks for that.
is there a way to make that TEXT instead of Number Format?
zach
Bob Phillips
11-22-2009, 03:37 PM
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
11-23-2009, 06:05 AM
hi Bob,
thanks! works great!
zach
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.