converting 16 digit number to text
 

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

converting 16 digit number to text
 

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


--
Regards,
Tom Ogilvy

"Troy" wrote in message
...
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

converting 16 digit number to text
 

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" wrote in message
...
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

converting 16 digit number to text
 

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.



"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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" wrote in message
...
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

converting 16 digit number to text
 

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...eio/fileio.asp
File Access with Visual Basic® for Applications
--
Regards,
Tom Ogilvy

Troy wrote in message
...
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.



"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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" wrote in message
...
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