Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a number (17 characters) to text (Scientific Notation)
Heres my dilemma and challenge€¦
€¦ This is through Automation€¦ Im receiving an XML file that has excel formatting€¦ Its very easy to import, the problem comes in when I import large numbers (17 €“ 20 characters long)€¦ They should be imported as text, but they are importing as numbers, so they appear as scientific notation€¦ When I go to create a file from the data, the numbers are being written in the scientific notation and not as text€¦ I am unable to control the content of the XML file. I do know the number is in a certain column€¦ Also, when I double click on a cell, I can edit it and place a single quote and it will store the number correctly€¦ But, when I do it through automation, it wont convert€¦ We are using excel 2003€¦ In the process of upgrading to 2007, but wont be for another 6 months€¦ Fustrating... Your help would be greatly appreciated.. Thanks, Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a number (17 characters) to text (Scientific Notation)
1st of all, only the first 15 digits will convert. The rest will be '0's.
Example, importing 12345678901234567890 to Excel will convert to 12345678901234500000. To convert an entire column of numbers to text quickly, you can use something like... Selection.TextToColumns _ Destination:=Selection.Range("A1"), _ DataType:=xlFixedWidth, _ FieldInfo:=Array(0, xlTextFormat), _ TrailingMinusNumbers:=True -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "MikeL" wrote: Heres my dilemma and challenge€¦ €¦ This is through Automation€¦ Im receiving an XML file that has excel formatting€¦ Its very easy to import, the problem comes in when I import large numbers (17 €“ 20 characters long)€¦ They should be imported as text, but they are importing as numbers, so they appear as scientific notation€¦ When I go to create a file from the data, the numbers are being written in the scientific notation and not as text€¦ I am unable to control the content of the XML file. I do know the number is in a certain column€¦ Also, when I double click on a cell, I can edit it and place a single quote and it will store the number correctly€¦ But, when I do it through automation, it wont convert€¦ We are using excel 2003€¦ In the process of upgrading to 2007, but wont be for another 6 months€¦ Fustrating... Your help would be greatly appreciated.. Thanks, Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a number (17 characters) to text (Scientific Notation)
I'm not sure how you're importing that XML file, but if you want to preserve all
17 digits, maybe you can bring the data in as text and then clean up the stuff that doesn't belong. MikeL wrote: Heres my dilemma and challenge€¦ €¦ This is through Automation€¦ Im receiving an XML file that has excel formatting€¦ Its very easy to import, the problem comes in when I import large numbers (17 €“ 20 characters long)€¦ They should be imported as text, but they are importing as numbers, so they appear as scientific notation€¦ When I go to create a file from the data, the numbers are being written in the scientific notation and not as text€¦ I am unable to control the content of the XML file. I do know the number is in a certain column€¦ Also, when I double click on a cell, I can edit it and place a single quote and it will store the number correctly€¦ But, when I do it through automation, it wont convert€¦ We are using excel 2003€¦ In the process of upgrading to 2007, but wont be for another 6 months€¦ Fustrating... Your help would be greatly appreciated.. Thanks, Michael -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a number (17 characters) to text (Scientific Notati
Dave, Can't thank you enough for the reminder aoubt the 17 didget
truncation... (Saved alot of rework here...) I could not figure out a way to import as text... (no filed definitions like open workbook...) But, when I tried importing the XML in access, it recognized it as text, and the import worked flawlessly... Again, because of the reminder, I went a different route... If you didn't nuge us in the right direction, it would have been a disaster.... (Thanks for going the extra mile... and making sure that we understood...) "Dave Peterson" wrote: I'm not sure how you're importing that XML file, but if you want to preserve all 17 digits, maybe you can bring the data in as text and then clean up the stuff that doesn't belong. MikeL wrote: Here€„¢s my dilemma and challenge€¦ €¦ This is through Automation€¦ I€„¢m receiving an XML file that has excel formatting€¦ It€„¢s very easy to import, the problem comes in when I import large numbers (17 €€œ 20 characters long)€¦ They should be imported as text, but they are importing as numbers, so they appear as scientific notation€¦ When I go to create a file from the data, the numbers are being written in the scientific notation and not as text€¦ I am unable to control the content of the XML file. I do know the number is in a certain column€¦ Also, when I double click on a cell, I can edit it and place a single quote and it will store the number correctly€¦ But, when I do it through automation, it won€„¢t convert€¦ We are using excel 2003€¦ In the process of upgrading to 2007, but won€„¢t be for another 6 months€¦ Fustrating... Your help would be greatly appreciated.. Thanks, Michael -- Dave Peterson . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a number (17 characters) to text (Scientific Notati
Glad you found a solution to the problem.
MikeL wrote: Dave, Can't thank you enough for the reminder aoubt the 17 didget truncation... (Saved alot of rework here...) I could not figure out a way to import as text... (no filed definitions like open workbook...) But, when I tried importing the XML in access, it recognized it as text, and the import worked flawlessly... Again, because of the reminder, I went a different route... If you didn't nuge us in the right direction, it would have been a disaster.... (Thanks for going the extra mile... and making sure that we understood...) "Dave Peterson" wrote: I'm not sure how you're importing that XML file, but if you want to preserve all 17 digits, maybe you can bring the data in as text and then clean up the stuff that doesn't belong. MikeL wrote: Here€„¢s my dilemma and challenge€¦ €¦ This is through Automation€¦ I€„¢m receiving an XML file that has excel formatting€¦ It€„¢s very easy to import, the problem comes in when I import large numbers (17 €€œ 20 characters long)€¦ They should be imported as text, but they are importing as numbers, so they appear as scientific notation€¦ When I go to create a file from the data, the numbers are being written in the scientific notation and not as text€¦ I am unable to control the content of the XML file. I do know the number is in a certain column€¦ Also, when I double click on a cell, I can edit it and place a single quote and it will store the number correctly€¦ But, when I do it through automation, it won€„¢t convert€¦ We are using excel 2003€¦ In the process of upgrading to 2007, but won€„¢t be for another 6 months€¦ Fustrating... Your help would be greatly appreciated.. Thanks, Michael -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show value as text not scientific notation | Excel Discussion (Misc queries) | |||
Number Formatting/Scientific notation | Excel Discussion (Misc queries) | |||
Scientific notation in text cells | Excel Discussion (Misc queries) | |||
Using scientific notation in TEXT() function | Excel Worksheet Functions | |||
convert scientific notation to a number | Excel Discussion (Misc queries) |