Format a number in VBA : 1 --> 001

Hi Guys,

How can I format a number in VBA ? For example I want to turn 1 in 001.

Thx,
Floep

Originally Posted by floep

Hi Guys,

How can I format a number in VBA ? For example I want to turn 1 in 001.

Thx,
Floep

Custom Format 000

Thx for the answer. Got another problem now. When I use the formatted result (0001) in a formula it changes back to its original format (1). For example I use a concatenation with the values 'T' and '0001' and I get 'T1' as a result although I need 'T0001'. This is just a simple example of the concatination, the the actual concatenation is much more complex and it is not possible to format the result of the concatenation. I there a way I can use the 0001 value in the concatenation ?

Floep

Originally Posted by floep

Thx for the answer. Got another problem now. When I use the formatted result (0001) in a formula it changes back to its original format (1). For example I use a concatenation with the values 'T' and '0001' and I get 'T1' as a result although I need 'T0001'. This is just a simple example of the concatination, the the actual concatenation is much more complex and it is not possible to format the result of the concatenation. I there a way I can use the 0001 value in the concatenation ?

Floep

Hi Floep,

When you get the formatted result 0001, can't you then copy and then format to text

In vba you can do so:
myCell = range("a1") 'the celle where is a number, for instance 1
range("b1") = format(myCell,"0000") ' you will have 0001 in cell B1

if you want to use also 'T' as prefix you can do:
range("b1") = "T" & format(myCell,"0000") ' you will have T0001 in cell B1

In Excel you can do so:
="T" & text(a1;"0000") and you will be have 'T0001

Rgards,
Antonio

The other thing you could do is set the variable to be text rather than an integer, that way any formatting you apply will stick.

Matt