Convert and format numbers to text with VBA

Hi all.

I open a .csv file where I have a number stored in one column. I need to format that number as text in a particular format (with leading zeroes) and move it to another place with some added code. If I were doing this manually I would use the worksheet text() function, to whit:

="BILL-TO - " & TEXT(J15,"0000000")

Then I would copy the result and paste-special the value into the cell.

But I need to do it in a function. Str() converts the number to text, but won't format it.

Any help would be greatly appreciated.