Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting issue
Hello,
We have data that is downloaded to a .xls file from a SAP application. The field in question is a 30 character field that contains all numeric data. The problem is when opened in Excel the format of the data is change to scientific notation and the right 7 characters are changed to zeros. The character string should be 9611019049526870545643 but in Excel it appears as 9.61102e+21 and formated as a general number. When converted to a displayable number it converts to 9611019049526870000000. I tried saving the file rather than opening it into Excel and found the character string did contain all the numbers, when opening it in Notepad it appears as <td class='SAPBExSt' style=" " 9611019049526870545643 </td. Any assistance would be appreciated. Thanks, Rick |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting issue
If you don't have to do any math on the figures, you might try importing that
column as TEXT, to get it to display all the numbers.... Vaya con Dios, Chuck, CABGx3 " wrote: Hello, We have data that is downloaded to a .xls file from a SAP application. The field in question is a 30 character field that contains all numeric data. The problem is when opened in Excel the format of the data is change to scientific notation and the right 7 characters are changed to zeros. The character string should be 9611019049526870545643 but in Excel it appears as 9.61102e+21 and formated as a general number. When converted to a displayable number it converts to 9611019049526870000000. I tried saving the file rather than opening it into Excel and found the character string did contain all the numbers, when opening it in Notepad it appears as <td class='SAPBExSt' style=" " 9611019049526870545643 </td. Any assistance would be appreciated. Thanks, Rick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting issue
On May 16, 9:38 am, CLR wrote:
If you don't have to do any math on the figures, you might try importing that column as TEXT, to get it to display all the numbers.... Vaya con Dios, Chuck, CABGx3 " wrote: Hello, We have data that is downloaded to a .xls file from a SAP application. The field in question is a 30 character field that contains all numeric data. The problem is when opened in Excel the format of the data is change to scientific notation and the right 7 characters are changed to zeros. The character string should be 9611019049526870545643 but in Excel it appears as 9.61102e+21 and formated as a general number. When converted to a displayable number it converts to 9611019049526870000000. I tried saving the file rather than opening it into Excel and found the character string did contain all the numbers, when opening it in Notepad it appears as <td class='SAPBExSt' style=" " 9611019049526870545643 </td. Any assistance would be appreciated. Thanks, Rick- Hide quoted text - - Show quoted text - Chuck, Thank you for your response. Is there a way to specify the field's import format? Thanks, RIck |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting issue
If you import it using the text import wizard you can click next twice and
select text under column data format -- Regards, Peo Sjoblom wrote in message ups.com... On May 16, 9:38 am, CLR wrote: If you don't have to do any math on the figures, you might try importing that column as TEXT, to get it to display all the numbers.... Vaya con Dios, Chuck, CABGx3 " wrote: Hello, We have data that is downloaded to a .xls file from a SAP application. The field in question is a 30 character field that contains all numeric data. The problem is when opened in Excel the format of the data is change to scientific notation and the right 7 characters are changed to zeros. The character string should be 9611019049526870545643 but in Excel it appears as 9.61102e+21 and formated as a general number. When converted to a displayable number it converts to 9611019049526870000000. I tried saving the file rather than opening it into Excel and found the character string did contain all the numbers, when opening it in Notepad it appears as <td class='SAPBExSt' style=" " 9611019049526870545643 </td. Any assistance would be appreciated. Thanks, Rick- Hide quoted text - - Show quoted text - Chuck, Thank you for your response. Is there a way to specify the field's import format? Thanks, RIck |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting issue
Yes, usually in the Importing Utility.............how are you bringing the
data in to Excel?.......If through MSQuery you can just follow the menus and should see the option to format each field. Vaya Con Dios, Chuck, CABGx3 " wrote: On May 16, 9:38 am, CLR wrote: If you don't have to do any math on the figures, you might try importing that column as TEXT, to get it to display all the numbers.... Vaya con Dios, Chuck, CABGx3 " wrote: Hello, We have data that is downloaded to a .xls file from a SAP application. The field in question is a 30 character field that contains all numeric data. The problem is when opened in Excel the format of the data is change to scientific notation and the right 7 characters are changed to zeros. The character string should be 9611019049526870545643 but in Excel it appears as 9.61102e+21 and formated as a general number. When converted to a displayable number it converts to 9611019049526870000000. I tried saving the file rather than opening it into Excel and found the character string did contain all the numbers, when opening it in Notepad it appears as <td class='SAPBExSt' style=" " 9611019049526870545643 </td. Any assistance would be appreciated. Thanks, Rick- Hide quoted text - - Show quoted text - Chuck, Thank you for your response. Is there a way to specify the field's import format? Thanks, RIck |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting issue
On May 16, 11:53 am, CLR wrote:
Yes, usually in the Importing Utility.............how are you bringing the data in to Excel?.......If through MSQuery you can just follow the menus and should see the option to format each field. Vaya Con Dios, Chuck, CABGx3 " wrote: On May 16, 9:38 am, CLR wrote: If you don't have to do any math on the figures, you might try importing that column as TEXT, to get it to display all the numbers.... Vaya con Dios, Chuck, CABGx3 " wrote: Hello, We have data that is downloaded to a .xls file from a SAP application. The field in question is a 30 character field that contains all numeric data. The problem is when opened in Excel the format of the data is change to scientific notation and the right 7 characters are changed to zeros. The character string should be 9611019049526870545643 but in Excel it appears as 9.61102e+21 and formated as a general number. When converted to a displayable number it converts to 9611019049526870000000. I tried saving the file rather than opening it into Excel and found the character string did contain all the numbers, when opening it in Notepad it appears as <td class='SAPBExSt' style=" " 9611019049526870545643 </td. Any assistance would be appreciated. Thanks, Rick- Hide quoted text - - Show quoted text - Chuck, Thank you for your response. Is there a way to specify the field's import format? Thanks, RIck- Hide quoted text - - Show quoted text - Unfortunately the file is already a .xls file and Excel does not recoginize the need to run the import wizard to open the file. It is a download from a SAP Web query. I did check the .xls file and the string is all there before Excel opens it and changes it. I tried cutting and pasting a long numeric string into an Excel cell and experienced the same results. Is there a way to override the 15 char limit in Excel or to treat the string as a string and not a number? Thanks, Rick |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting issue
On May 16, 11:53 am, CLR wrote:
Yes, usually in the Importing Utility.............how are you bringing the data in to Excel?.......If through MSQuery you can just follow the menus and should see the option to format each field. Vaya Con Dios, Chuck, CABGx3 " wrote: On May 16, 9:38 am, CLR wrote: If you don't have to do any math on the figures, you might try importing that column as TEXT, to get it to display all the numbers.... Vaya con Dios, Chuck, CABGx3 " wrote: Hello, We have data that is downloaded to a .xls file from a SAP application. The field in question is a 30 character field that contains all numeric data. The problem is when opened in Excel the format of the data is change to scientific notation and the right 7 characters are changed to zeros. The character string should be 9611019049526870545643 but in Excel it appears as 9.61102e+21 and formated as a general number. When converted to a displayable number it converts to 9611019049526870000000. I tried saving the file rather than opening it into Excel and found the character string did contain all the numbers, when opening it in Notepad it appears as <td class='SAPBExSt' style=" " 9611019049526870545643 </td. Any assistance would be appreciated. Thanks, Rick- Hide quoted text - - Show quoted text - Chuck, Thank you for your response. Is there a way to specify the field's import format? Thanks, RIck- Hide quoted text - - Show quoted text - Unfortunately the .xls file is alreay build in a download from SAP. When checking the .xls file in Notepad the correct amount of characters is displayed it is only when opening in Excel that the truncation occurs. There is no import process that would allow the specification of a columns format. Thanks, Rick |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting issue
If you use a web query you can pre-format the columns as text and then in
the query process (under properties) select Preserve cell formatting -- Regards, Peo Sjoblom wrote in message ups.com... On May 16, 11:53 am, CLR wrote: Yes, usually in the Importing Utility.............how are you bringing the data in to Excel?.......If through MSQuery you can just follow the menus and should see the option to format each field. Vaya Con Dios, Chuck, CABGx3 " wrote: On May 16, 9:38 am, CLR wrote: If you don't have to do any math on the figures, you might try importing that column as TEXT, to get it to display all the numbers.... Vaya con Dios, Chuck, CABGx3 " wrote: Hello, We have data that is downloaded to a .xls file from a SAP application. The field in question is a 30 character field that contains all numeric data. The problem is when opened in Excel the format of the data is change to scientific notation and the right 7 characters are changed to zeros. The character string should be 9611019049526870545643 but in Excel it appears as 9.61102e+21 and formated as a general number. When converted to a displayable number it converts to 9611019049526870000000. I tried saving the file rather than opening it into Excel and found the character string did contain all the numbers, when opening it in Notepad it appears as <td class='SAPBExSt' style=" " 9611019049526870545643 </td. Any assistance would be appreciated. Thanks, Rick- Hide quoted text - - Show quoted text - Chuck, Thank you for your response. Is there a way to specify the field's import format? Thanks, RIck- Hide quoted text - - Show quoted text - Unfortunately the file is already a .xls file and Excel does not recoginize the need to run the import wizard to open the file. It is a download from a SAP Web query. I did check the .xls file and the string is all there before Excel opens it and changes it. I tried cutting and pasting a long numeric string into an Excel cell and experienced the same results. Is there a way to override the 15 char limit in Excel or to treat the string as a string and not a number? Thanks, Rick |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting issue
On May 16, 4:04 pm, "Peo Sjoblom" wrote:
If you use a web query you can pre-formatthe columns as text and then in the query process (under properties) select Preserve cell formatting -- Regards, Peo Sjoblom wrote in message ups.com... On May 16, 11:53 am, CLR wrote: Yes, usually in the Importing Utility.............how are you bringing the data in toExcel?.......If through MSQuery you can just follow the menus and should see the option toformateach field. Vaya Con Dios, Chuck, CABGx3 " wrote: On May 16, 9:38 am, CLR wrote: If you don't have to do any math on the figures, you might try importing that column as TEXT, to get it to display all the numbers.... Vaya con Dios, Chuck, CABGx3 " wrote: Hello, We have data that is downloaded to a .xls file from a SAP application. The field in question is a 30 character field that contains all numeric data. The problem is when opened inExceltheformatof the data is change to scientific notation and the right 7 characters are changed to zeros. The character string should be 9611019049526870545643 but inExcelit appears as 9.61102e+21 and formated as a general number. When converted to a displayable number it converts to 9611019049526870000000. I tried saving the file rather than opening it intoExceland found the character string did contain all the numbers, when opening it in Notepad it appears as <td class='SAPBExSt' style=" " 9611019049526870545643 </td. Any assistance would be appreciated. Thanks, Rick- Hide quoted text - - Show quoted text - Chuck, Thank you for your response. Is there a way to specify the field's importformat? Thanks, RIck- Hide quoted text - - Show quoted text - Unfortunately the file is already a .xls file andExceldoes not recoginize the need to run the import wizard to open the file. It is a download from a SAP Web query. I did check the .xls file and the string is all there beforeExcelopens it and changes it. I tried cutting and pasting a long numeric string into anExcelcell and experienced the same results. Is there a way to override the 15 char limit inExcelor to treat the string as a string and not a number? Thanks, Rick- Hide quoted text - - Show quoted text - Thank you for the response but I don't see where I have the control to specify the maintian cell formatting. In testing I have a situation where one value is truncated into the scientific notation and the other is not. Looking at the .xls file I do not see a difference between the two character strings that would cause one to truncate and the other to open correctly. Any thoughts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting issue | Excel Discussion (Misc queries) | |||
Formatting issue ??? | Excel Discussion (Misc queries) | |||
Cell formatting issue | Excel Discussion (Misc queries) | |||
formatting issue? | Excel Discussion (Misc queries) | |||
Formatting issue | Excel Worksheet Functions |