Is there a file format I can use that will allow me to import numeric text into excel without having to decorate the data?

I have data I am importing into Excel as a .csv or .html file. One of the columns has numeric text. Problem is the numeric text displays in exponential notation.  I have reviewed this article http://support.microsoft.com/kb/214233/en-us?wa=wsignin1.0 and now I prefix the data with a single quote char.  What format should I write the input data in so I do not have to decorate the numeric text?

thanks,

<table>
<tr>
<th>POKEY</th>
<th>ORHSHPNAM</th>
<th>CUSTORDNUM</th>
<th>PODATE</th>
</tr>
<tr>
<td>183236</td>
<td></td>
<td>'74455919399999</td>
<td>2013-08-31</td>
</tr>
<tr>
<td>183237</td>
<td></td>
<td>'74455919399999</td>
<td>2013-08-31</td>
</tr>
</table>

Select column with chars and use this code:

Sub lk()
Dim el As Range, wart
For Each el In Selection
    wart = Replace(el, "'", vbNullString)
    If IsNumeric(wart) = True Then el.Value = wart: el.NumberFormat = "0"
Next
End Sub

Select column with chars and use this code:

thank you.  I have end users who are opening the .csv and .html files.  How do I encode the input data so that it displays as text?

thanks,

If you import data from CSV using Excels interface, you have columns with data. Import this code to vba module, select range of numerics with "'" and rune this code.  you can also run replace using [Ctrl+h] (change ' t

Hi,

Please add equal sign and quotation marks before the number, such as ="123124516513323000000" in CSV file, then the numeric text is displayed as text, not in scientific notation.Please refer to the following picture:

Thanks,

George Zhao

Forum Support

________________________________________

Come back and mark the replies as answers if they help and unmark them if they provide no help.

If you have any feedback on our support, please click here

• Marked as answer by Steve Richter Monday, September 23, 2013 12:40 PM

Hi,

Please add equal sign and quotation marks before the number, such as ="123124516513323000000" in CSV file, then the numeric text is displayed as text, not in scientific notation.

that did it.  Thank you.