Thread: I need to format various strings (numbers, dates) precisely

I have a few fields where I need the resulting string to have a fixed number of digits

For instance,
I need text entered as '71' to become '071'
And i need a date like '01/4/2008' to become '010408'
And other numbers, like '5' must become '5.00'

If anyone knows how to handle any of the above, I'd really appreciate being pointed in the right direction.

Hi

How do you know if a number (71) is to be formatted 071 or 71.00???

And is the formatting just for display, or does the result have to be in that structure?

rylo

I am feeding data into another program, and it demands that you type numbers in these strange formats

so if 70 was fed into the other program, it wouldn't work, it would have to be 070 (it's a really really old database program) for it to accept the input

i just need code that will take strings in VBA and format them like this
sometimes i need it to add zeros to make the string a certain length, other times i need to add 2 decimal places, other times i need the date in 6 digit format

ok I got the dates working

Code:

Private Sub cmbDate_Click()
Me.cmbDate.value=Format("ddmmyy")
End Sub

it came up with a 'object not in library' error which i fixed by going to tools>references and unchecking the 'Missing' directories

still stumped with making numbers into 3 digits like '55' to '055'

Can't this be done under Cell Format, Custom Formatting?

Hi

If you want a number to have a leading 0, then you have to make the cell text. You can either change the cell format, or put a ' before the number.

As I asked earlier, how is the macro to know if a number is being changed to have a leading 0, or to have trailing decimal places?

rylo