converting 16 digit number to text

[Troy]

Hello,

I have a spreadsheet with a single column containing 25000 rows that have a
16 digit number. I'd like to convert the .xls to csv and in doing that i
changed the number to an exponential number. So avoid this I came up with
the follwoing code that will add a ' to all the rows but it didn't work
because after the single quote was added the number flipped to an
exponential number.

Sub test()
'

'
' Keyboard Shortcut: Ctrl+Shift+Q
'

Dim irow1 As Long
Dim ws1 As Worksheet
Dim test As String

Set ws1 = Worksheets("Sheet1")
For irow1 = 1 To 50
'test = ws1.Cells(irow1, 3)
'MsgBox test
ws1.Cells(irow1, 6) = "'" & ws1.Cells(irow1, 3)
Next irow1

End Sub

Any ideas?

thanks

 

[Tom Ogilvy]

Dim irow1 As Long
Dim ws1 As Worksheet
Dim test As String

Set ws1 = Worksheets("Sheet1")
For irow1 = 1 To 50
test = ws1.Cells(irow1, 3).Text
cells(irow1,6).Numberformat = "@"
'MsgBox test
ws1.Cells(irow1, 6) = "'" & test
Next irow1

End Sub

 

[Vasant Nanavati]

Hi Troy:

Excel can only handle 15 significant digits and so will not store or display
16-digit numbers accurately, unless they are already stored as text. So I'm
not exactly clear on what your question is.

Regards,

Vasant.

 

[Troy]

Thanks for responding so quickly. The excel spreadsheet displays the 16
digit numbers just fine but as soon as I convert the file into csv it turns
the numbers into exponential numbers hence the exercise of adding a single
quote to all the numbers.

 

[Tom Ogilvy]

That probably won't affect what CSV does. You need to use low level file IO
and write your own macro to built the file would be my guess.

I haven't tested it, but CSV is pretty stubborn in how it acts.

http://support.microsoft.com/support/excel/content/fileio/fileio.asp
File Access with Visual Basic® for Applications