After using the VBA cstr function, Excel keeps converting string values to number.

[eiasbk]

Hi all,

I'm trying to write code that converts a range of numeric values to string values, e.g. the number 123.456 to the string 123.456. I have tried using the cstr function to do this, but it doesn't seem to work. I find that my converted values are still numeric. Suppose I have 20 numbers in the range A1:A20, and I'd like these to be converted to strings and placed in the range B1:B20. This is the code I am using:

Option Explicit

Sub ConvertToString()

Dim i As Integer

For i = 1 To 20
     Cells(i, 2).Value = CStr(Cells(i, 1).Value)
Next 'i

End Sub

I'd expect the range B1:B20 to contain the string equivalents of the numbers in A1:A20, but this is not the case. What I find is that B1:B20 is the same A1:A20, i.e. using the =isnumber worksheet function verifies that both columns have numeric values.

Any idea what I am doing wrong?

Many thanks,
I.

[nathansav]

Thats right, i've just run it, one column as number format, run the code and get the same values, but in General format

you could prefix with a "'" or use the worksheetfunction.text() approach?

[Kaper]

try formatting cells as text before assigning:

For i = 1 To 20
     Cells(i, 2).NumberFormat = "@"
     Cells(i, 2).Value = CStr(Cells(i, 1).Value)
Next 'i

it will be formatted as text but isnumeric will anyway return frue.

[sktneer]

Why not this......

Sub ConvertToString()
Dim i As Integer
For i = 1 To 20
     Cells(i, 2).Value = Cells(i, 1).Value
     Cells(i, 2).NumberFormat = "@"
Next i
End Sub

[eiasbk]

Hi all,

Many thanks to all of you for the help.

I went for Kaper's suggestion, and it works fine, does exactly what I want it to. I can test that I have the right data type by using the worksheet functions =isnumber and =istext.

Thanks again, really do appreciate it!
I.

Dunno if I'm supposed to somehow mark this thread as "solved."

[Kaper]

As described in http://www.excelforum.com/forum-rule...rum-rules.html :
9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

Would be also nice if you add some roeputation by clicking small star-shaped icons in lowerleft corners of the post(s) to those who helped.