Write a large number (16 digits) to a text file

ddseager

New Member
Joined
May 21, 2010
Messages
3
Column A contains account numbers, which are 16 digits long.
Column A is formatted to "0000000000000000".
When I Write this out to a text file, Excel tries to help by converting column A to Scientific Notation.

Here's the code:
While iCounter < iLastRow + 1
Set RngA = Range("A" & iCounter)
Set RngB = Range("B" & iCounter)
Set RngC = Range("C" & iCounter)
Write #2, RngA, RngB, RngC
iCounter = iCounter + 1
Wend

Is there a way to Write the contents of column A as 16 digits, not Scientific Notation?
Thanks
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,534
I have some qeustions:
are the account numbers keeping the 16 digits in the cells formatted as "0000000000000000" as I'd have thought that numerics stored that way would round at around 13 digits. I'd have used text formatted cells for storing such numbers.

How are you checking the text file after it's been created? Don't say that you open it in Excel, as that will show what Excel translates the contents as, and not the actual contents ... you should be using a text editor to check the output.
 

ddseager

New Member
Joined
May 21, 2010
Messages
3
GlennUK
The account numbers are stored as text. I even put a ' in the first postion to guarantee it. However, when I select the cell in order to Write it, Excel automatically changes it to Scientific Notation. I use NotePad to look at the output CSV file and every other field is formatted correctly, even all numeric fields. Although, all other numeric fields contain fewer than 12 digits.

I put a MsgBox statement in the macro. So I can see the value of RngA in the MsgBox and at the same time I can see the value in column A. Column A has 16 digits and the MsgBox has Scientific Notation.

While iCounter < iLastRow + 1
Set RngA = Range("A" & iCounter)
MsgBox RngA
Set RngB = Range("B" & iCounter)
Set RngC = Range("C" & iCounter)
Write #2, RngA, RngB, RngC
iCounter = iCounter + 1
Wend

Thanks
 

ddseager

New Member
Joined
May 21, 2010
Messages
3
All,
For those of you who need to do this, I figured out the answer. Before when I was addressing the Account Number (RngA) I was addressing the Value. The solution is to address the Text. When you addresst he Text, Excel does not convert the cell content to Scientific Notation. It leaves it as it is formatted.
Thanks,


Here's the code:
While iCounter < iLastRow + 1
Set RngA = Range("A" & iCounter)
vTempA = RngA.Text
Set RngB = Range("B" & iCounter)
Set RngC = Range("C" & iCounter)
Write #2, vTempA, RngB, RngC
iCounter = iCounter + 1
Wend
 

Watch MrExcel Video

Forum statistics

Threads
1,123,171
Messages
5,600,124
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top