| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
16 digit numbers
Hi,
The team at work have to export a file from an app one column which contains 16 digits product codes. The app only exports as a CSV. My colleagues use excel to open the csv, but the long number reverts to scientific notation, and formatting the cell to number corrupts the number slightly. The only work around I can offer if to rename the CSV as text, open with excel then go through the wizard and set the offending column to text, which works, but is over complicated and cumbersome for the team. Can anyone offer an easy solution or a macro method to ease this process ? -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers
Hi Scott,
These newsgroups are meant to share solutions, so we can all learn from each other. Sending files via email should only be done if nothing else helped and there is no hope for a shared solution anymore. Even then any solution found should be posted to the newsgroup. -- Kind regards, Niek Otten Microsoft MVP - Excel "porter444" wrote in message ... Can you send me a sample file Rich? Please do not include any confidential information. Email to: -- If my posting was helpful, please click the "Yes" button. ROCK ON!, Scott "Rich" wrote: Hi, The team at work have to export a file from an app one column which contains 16 digits product codes. The app only exports as a CSV. My colleagues use excel to open the csv, but the long number reverts to scientific notation, and formatting the cell to number corrupts the number slightly. The only work around I can offer if to rename the CSV as text, open with excel then go through the wizard and set the offending column to text, which works, but is over complicated and cumbersome for the team. Can anyone offer an easy solution or a macro method to ease this process ? -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers
Change file from CSV to txt (change the file extension from .csv to
..txt) and use the Import Text tool to import the file. This will allow you to specify the formatting of the problematic values. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 26 Nov 2008 20:53:32 -0000, "Rich" wrote: Hi, The team at work have to export a file from an app one column which contains 16 digits product codes. The app only exports as a CSV. My colleagues use excel to open the csv, but the long number reverts to scientific notation, and formatting the cell to number corrupts the number slightly. The only work around I can offer if to rename the CSV as text, open with excel then go through the wizard and set the offending column to text, which works, but is over complicated and cumbersome for the team. Can anyone offer an easy solution or a macro method to ease this process ? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers
"Chip Pearson" wrote in message ... Change file from CSV to txt (change the file extension from .csv to .txt) and use the Import Text tool to import the file. This will allow you to specify the formatting of the problematic values. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 26 Nov 2008 20:53:32 -0000, "Rich" wrote: Hi, The team at work have to export a file from an app one column which contains 16 digits product codes. The app only exports as a CSV. My colleagues use excel to open the csv, but the long number reverts to scientific notation, and formatting the cell to number corrupts the number slightly. The only work around I can offer if to rename the CSV as text, open with excel then go through the wizard and set the offending column to text, which works, but is over complicated and cumbersome for the team. Can anyone offer an easy solution or a macro method to ease this process ? Thanks Chip, The users already have this solution, it's not really what they're looking for, as it involves too many steps, I was looking for something that won't scare excel beginners. -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers
Whatever dude... you should probably mind you own business.
|
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers
"Niek Otten" wrote in message ... Hi Scott, These newsgroups are meant to share solutions, so we can all learn from each other. Sending files via email should only be done if nothing else helped and there is no hope for a shared solution anymore. Even then any solution found should be posted to the newsgroup. -- Kind regards, Niek Otten Microsoft MVP - Excel "porter444" wrote in message ... Can you send me a sample file Rich? Please do not include any confidential information. Email to: -- If my posting was helpful, please click the "Yes" button. ROCK ON!, Scott "Rich" wrote: Hi, The team at work have to export a file from an app one column which contains 16 digits product codes. The app only exports as a CSV. My colleagues use excel to open the csv, but the long number reverts to scientific notation, and formatting the cell to number corrupts the number slightly. The only work around I can offer if to rename the CSV as text, open with excel then go through the wizard and set the offending column to text, which works, but is over complicated and cumbersome for the team. Can anyone offer an easy solution or a macro method to ease this process ? -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** Scott, Thanks for the offer, Niek you're right too. I've posted the example to http://www.richdavies.com/emample2611.xls The product code is 5171261108300966. Excel turns it into 5.17126E+15 When you convert it by formatting as number, it becomes 5171261108300960 -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers OOPS
"Rich" wrote in message ... "Niek Otten" wrote in message ... Hi Scott, These newsgroups are meant to share solutions, so we can all learn from each other. Sending files via email should only be done if nothing else helped and there is no hope for a shared solution anymore. Even then any solution found should be posted to the newsgroup. -- Kind regards, Niek Otten Microsoft MVP - Excel "porter444" wrote in message ... Can you send me a sample file Rich? Please do not include any confidential information. Email to: -- If my posting was helpful, please click the "Yes" button. ROCK ON!, Scott "Rich" wrote: Hi, The team at work have to export a file from an app one column which contains 16 digits product codes. The app only exports as a CSV. My colleagues use excel to open the csv, but the long number reverts to scientific notation, and formatting the cell to number corrupts the number slightly. The only work around I can offer if to rename the CSV as text, open with excel then go through the wizard and set the offending column to text, which works, but is over complicated and cumbersome for the team. Can anyone offer an easy solution or a macro method to ease this process ? -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** Scott, Thanks for the offer, Niek you're right too. I've posted the example to http://www.richdavies.com/emample2611.xls The product code is 5171261108300966. Excel turns it into 5.17126E+15 When you convert it by formatting as number, it becomes 5171261108300960 -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm Oops ! Should have been http://www.richdavies.com/example2611.xls -- -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers
The link is taking me to a 404 page.
-- If my posting was helpful, please click the "Yes" button. ROCK ON!, Scott "Rich" wrote: "Niek Otten" wrote in message ... Hi Scott, These newsgroups are meant to share solutions, so we can all learn from each other. Sending files via email should only be done if nothing else helped and there is no hope for a shared solution anymore. Even then any solution found should be posted to the newsgroup. -- Kind regards, Niek Otten Microsoft MVP - Excel "porter444" wrote in message ... Can you send me a sample file Rich? Please do not include any confidential information. Email to: -- If my posting was helpful, please click the "Yes" button. ROCK ON!, Scott "Rich" wrote: Hi, The team at work have to export a file from an app one column which contains 16 digits product codes. The app only exports as a CSV. My colleagues use excel to open the csv, but the long number reverts to scientific notation, and formatting the cell to number corrupts the number slightly. The only work around I can offer if to rename the CSV as text, open with excel then go through the wizard and set the offending column to text, which works, but is over complicated and cumbersome for the team. Can anyone offer an easy solution or a macro method to ease this process ? -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** Scott, Thanks for the offer, Niek you're right too. I've posted the example to http://www.richdavies.com/emample2611.xls The product code is 5171261108300966. Excel turns it into 5.17126E+15 When you convert it by formatting as number, it becomes 5171261108300960 -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers
Maybe...
Option Explicit Sub testme01() Dim CSVFileName As Variant Dim TxtFileName As String Dim TempWks As Worksheet CSVFileName = Application.GetOpenFilename(Filefilter:="CSV Files, *.csv") If CSVFileName = False Then Exit Sub 'user hit cancel End If TxtFileName = CSVFileName & ".txt" FileCopy Source:=CSVFileName, Destination:=TxtFileName Workbooks.OpenText Filename:=TxtFileName, _ Origin:=437, StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _ Tab:=False, Semicolon:=False, Comma:=True, _ Space:=False, Other:=False, _ FieldInfo:=Array(1, 2) Set TempWks = ActiveSheet 'copy to a new workbook 'so we can close and kill the text file TempWks.Copy TempWks.Parent.Close savechanges:=False Kill TxtFileName End Sub You'll want to record a macro when you open one of your text files (after you've renamed the .csv to .txt) so that you can get that .opentext line correct--especially the delimiters and the fieldinfo stuff. Rich wrote: Hi, The team at work have to export a file from an app one column which contains 16 digits product codes. The app only exports as a CSV. My colleagues use excel to open the csv, but the long number reverts to scientific notation, and formatting the cell to number corrupts the number slightly. The only work around I can offer if to rename the CSV as text, open with excel then go through the wizard and set the offending column to text, which works, but is over complicated and cumbersome for the team. Can anyone offer an easy solution or a macro method to ease this process ? -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers
<mind you own business
I do. This is my business. Thanks for your helpful comments. -- Kind regards, Niek Otten Microsoft MVP - Excel "porter444" wrote in message ... Whatever dude... you should probably mind you own business. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
16 digit numbers
"Dave Peterson" wrote in message ... Maybe... Option Explicit Sub testme01() Dim CSVFileName As Variant Dim TxtFileName As String Dim TempWks As Worksheet CSVFileName = Application.GetOpenFilename(Filefilter:="CSV Files, *.csv") If CSVFileName = False Then Exit Sub 'user hit cancel End If TxtFileName = CSVFileName & ".txt" FileCopy Source:=CSVFileName, Destination:=TxtFileName Workbooks.OpenText Filename:=TxtFileName, _ Origin:=437, StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _ Tab:=False, Semicolon:=False, Comma:=True, _ Space:=False, Other:=False, _ FieldInfo:=Array(1, 2) Set TempWks = ActiveSheet 'copy to a new workbook 'so we can close and kill the text file TempWks.Copy TempWks.Parent.Close savechanges:=False Kill TxtFileName End Sub You'll want to record a macro when you open one of your text files (after you've renamed the .csv to .txt) so that you can get that .opentext line correct--especially the delimiters and the fieldinfo stuff. Rich wrote: Hi, The team at work have to export a file from an app one column which contains 16 digits product codes. The app only exports as a CSV. My colleagues use excel to open the csv, but the long number reverts to scientific notation, and formatting the cell to number corrupts the number slightly. The only work around I can offer if to rename the CSV as text, open with excel then go through the wizard and set the offending column to text, which works, but is over complicated and cumbersome for the team. Can anyone offer an easy solution or a macro method to ease this process ? -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm Thanks Dave, I'll give that a try and report back.... -- Rich http://www.richdavies.com http://www.richdavies.com/excel.htm http://www.richdavies.com/fantasy-football.htm ** Posted from http://www.teranews.com ** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Store numbers starting with zero as 2 digit numbers | Excel Discussion (Misc queries) | |||
| 15+ digit numbers | Excel Worksheet Functions | |||
| what is the minimum numbers set for 4 digit numbers from 0000 to 9 | Excel Discussion (Misc queries) | |||
| 16 digit numbers | Excel Discussion (Misc queries) | |||
| 16 DIGIT NUMBERS IN CELL WITHOUT LAST DIGIT BEING A ZERO | Excel Worksheet Functions | |||
![Rich[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
