Excel VBA - Strings starting with 0

Question

Dim testtext As String
testtext = Worksheets("Sheet2").Range("B6").Value
Worksheets("Sheet2").Range("B7").Value = testtext

Simple copying of one cell's content into another. But I run into problems when content of B6 is '02345 - after running the macro B7 contains 2345 and I don't want to lose the leading zero. I tried replacing .Value with .Text in second line of the code and this didn't help.

Answer 1

If you change the .NumberFormat property to "@", it will write 02345 in the cell.

Dim testtext As String
testtext = Worksheets("Sheet2").Range("B6").Value
Worksheets("Sheet2").Range("B7").NumberFormat = "@"

Worksheets("Sheet2").Range("B7").Value = testtext

This works because the standard cell format reads a number with leading 0s as a numeric value, and therefore the leading 0s are removed. By forcing Excel to use the text format on that particualar cell, it will not treat it as a numeric value, but just as plain text.