16 digit numbers

[Rich]

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 ?

 

[porter444]

Can you send me a sample file Rich? Please do not include any confidential
information.

Email to: (e-mail address removed)
--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott

 

[Niek Otten]

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.

 

[Chip Pearson]

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)

 

[Rich]

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)

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.

 

[porter444]

Whatever dude... you should probably mind you own business.

 

[Rich]

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

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]

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

--

 

[porter444]

The link is taking me to a 404 page.
--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott

 

[Dave Peterson]

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.

 

[Niek Otten]

<mind you own business>

I do. This is my business.
Thanks for your helpful comments.

 

[Rich]

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.

Thanks Dave,

I'll give that a try and report back....