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

durandal05 · #1 06-08-2008, 07:01 PM

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.

**rylo** · #2 06-08-2008, 07:37 PM

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

durandal05 · #3 06-08-2008, 08:10 PM

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

durandal05 · #4 06-09-2008, 08:22 AM

ok I got the dates working

Code:

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

Wskip49 · #5 06-09-2008, 09:28 AM

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

**rylo** · #6 06-10-2008, 08:55 PM

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

Welcome to the Excel Forum

Forum Statistics