convert number to text
 

how to convert a number to text in vba, e.g. showing 5 as "005". I can do it
on spreadsheet with a function like '=Text(A1,"000")'. can it be done in vba?

convert number to text
 

sStr = format(5,"000")

--
Regards,
Tom Ogilvy


"Scott" wrote:

how to convert a number to text in vba, e.g. showing 5 as "005". I can do it
on spreadsheet with a function like '=Text(A1,"000")'. can it be done in vba?

convert number to text
 

Sub routine()
Dim n As Integer, s As String
n = 5
s = Format(n, "000")
MsgBox (s)
End Sub
--
Gary''s Student
gsnu200711

convert number to text
 

format([a1],"000")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Scott" wrote in message ...
| how to convert a number to text in vba, e.g. showing 5 as "005". I can do it
| on spreadsheet with a function like '=Text(A1,"000")'. can it be done in vba?

convert number to text
 

Here are my codes:

Dim i As Long, lastrow As Long
lastrow = Range("A1").CurrentRegion.Rows.Count
For i = 1 To lastrow
Cells(i, 8) = Format(Cells(i, 6), "000")
Next

I still got numbers, not text.


"Gary''s Student" wrote:

Sub routine()
Dim n As Integer, s As String
n = 5
s = Format(n, "000")
MsgBox (s)
End Sub
--
Gary''s Student
gsnu200711

convert number to text
 

Dim i As Long, lastrow As Long
lastrow = Range("A1").CurrentRegion.Rows.Count
For i = 1 To lastrow
Cells(i, 8) = "'" & Format(Cells(i, 6), "000")
Next


--
Regards,
Tom Ogilvy


"Scott" wrote:

Here are my codes:

Dim i As Long, lastrow As Long
lastrow = Range("A1").CurrentRegion.Rows.Count
For i = 1 To lastrow
Cells(i, 8) = Format(Cells(i, 6), "000")
Next

I still got numbers, not text.


"Gary''s Student" wrote:

Sub routine()
Dim n As Integer, s As String
n = 5
s = Format(n, "000")
MsgBox (s)
End Sub
--
Gary''s Student
gsnu200711

convert number to text
 

It works!

Thank you, Tom.

"Tom Ogilvy" wrote:

Dim i As Long, lastrow As Long
lastrow = Range("A1").CurrentRegion.Rows.Count
For i = 1 To lastrow
Cells(i, 8) = "'" & Format(Cells(i, 6), "000")
Next


--
Regards,
Tom Ogilvy


"Scott" wrote:

Here are my codes:

Dim i As Long, lastrow As Long
lastrow = Range("A1").CurrentRegion.Rows.Count
For i = 1 To lastrow
Cells(i, 8) = Format(Cells(i, 6), "000")
Next

I still got numbers, not text.


"Gary''s Student" wrote:

Sub routine()
Dim n As Integer, s As String
n = 5
s = Format(n, "000")
MsgBox (s)
End Sub
--
Gary''s Student
gsnu200711