Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default importing undelimited text file data, character-by-character

I am trying to import a text file into an Excel worksheet so that each
character goes into a separate cell. There is no character which delimits the
characters which comprise the data (ie. the data looks like this: '1023B4'
and worksheet should have '1' in the first cell, '0' in the next, '2' in the
next, and so on.
At the moment I have used OpenText to import all the data into the first
cell and then used string functions to copy the Nth character into the Nth
cell. However, some of my data from the text file looks like a very large
number when imported to the worksheet and appears as something like
'1.62234211131231E+32'. I have changes the cell number format in various ways
and can get the number to APPEAR as it should in the first cell before I use
the string functions to move it to the appropriate cell. However, the number
it moves is always of the form '1.62234211131231E+32'.
Is there a way to turn off this representation of the data as a number ? Or
is there a better way to import each character into a separate cell ?

The text files will contain numerous lines of data in the way I have
described but the exact size will vary from file to file.

Excel 2002
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,440
Default importing undelimited text file data, character-by-character

You probably formatted the number like 0000000000000000000000000000000000
or something like that. Indeed splitting it will give you the original
number of digits.
Use (in a separate cell) =TEXT(A1,"0000000000000000000000000000000000") or
whatever format string you used: then it will take the number of digits that
you specify.

--
Kind regards,

Niek Otten

"The Mos$" <The wrote in message
...
I am trying to import a text file into an Excel worksheet so that each
character goes into a separate cell. There is no character which delimits
the
characters which comprise the data (ie. the data looks like this: '1023B4'
and worksheet should have '1' in the first cell, '0' in the next, '2' in
the
next, and so on.
At the moment I have used OpenText to import all the data into the first
cell and then used string functions to copy the Nth character into the Nth
cell. However, some of my data from the text file looks like a very large
number when imported to the worksheet and appears as something like
'1.62234211131231E+32'. I have changes the cell number format in various
ways
and can get the number to APPEAR as it should in the first cell before I
use
the string functions to move it to the appropriate cell. However, the
number
it moves is always of the form '1.62234211131231E+32'.
Is there a way to turn off this representation of the data as a number ?
Or
is there a better way to import each character into a separate cell ?

The text files will contain numerous lines of data in the way I have
described but the exact size will vary from file to file.

Excel 2002



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default importing undelimited text file data, character-by-character

Thank you for your reply.

I have tried your suggestion in this way:

Cells(5, 2) = "=Text(" & Cells(5, 1) & ", 0)"

- the line is displayed as I hope but it still produces the same
'4.44444444222222E+22' in the formula box when the cell is selected. I also
have these lines:

Columns("A:A").Select
Selection.NumberFormat = "0"

which allow the cell to be displayed as I want it to be:

44444444422222200000000

but the string is still copied like this:

'4.44444444222222E+22'

and the 17th and 18th characters are always copied as 'E' and '+' and the
2nd character is always '.'.

I feel I should make it clear that I am writing code so that a user can open
files of a similar format but where the number of characters in a line will
be unspecified and will vary.



"Niek Otten" wrote:

You probably formatted the number like 0000000000000000000000000000000000
or something like that. Indeed splitting it will give you the original
number of digits.
Use (in a separate cell) =TEXT(A1,"0000000000000000000000000000000000") or
whatever format string you used: then it will take the number of digits that
you specify.

--
Kind regards,

Niek Otten

"The Mos$" <The wrote in message
...
I am trying to import a text file into an Excel worksheet so that each
character goes into a separate cell. There is no character which delimits
the
characters which comprise the data (ie. the data looks like this: '1023B4'
and worksheet should have '1' in the first cell, '0' in the next, '2' in
the
next, and so on.
At the moment I have used OpenText to import all the data into the first
cell and then used string functions to copy the Nth character into the Nth
cell. However, some of my data from the text file looks like a very large
number when imported to the worksheet and appears as something like
'1.62234211131231E+32'. I have changes the cell number format in various
ways
and can get the number to APPEAR as it should in the first cell before I
use
the string functions to move it to the appropriate cell. However, the
number
it moves is always of the form '1.62234211131231E+32'.
Is there a way to turn off this representation of the data as a number ?
Or
is there a better way to import each character into a separate cell ?

The text files will contain numerous lines of data in the way I have
described but the exact size will vary from file to file.

Excel 2002




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,440
Default importing undelimited text file data, character-by-character

Try with the many zeros (20 I think) format. I tested it. Works for me. Less
effort than posting another message.

--
Kind regards,

Niek Otten

"The Mos$" wrote in message
...
Thank you for your reply.

I have tried your suggestion in this way:

Cells(5, 2) = "=Text(" & Cells(5, 1) & ", 0)"

- the line is displayed as I hope but it still produces the same
'4.44444444222222E+22' in the formula box when the cell is selected. I
also
have these lines:

Columns("A:A").Select
Selection.NumberFormat = "0"

which allow the cell to be displayed as I want it to be:

44444444422222200000000

but the string is still copied like this:

'4.44444444222222E+22'

and the 17th and 18th characters are always copied as 'E' and '+' and the
2nd character is always '.'.

I feel I should make it clear that I am writing code so that a user can
open
files of a similar format but where the number of characters in a line
will
be unspecified and will vary.



"Niek Otten" wrote:

You probably formatted the number like 0000000000000000000000000000000000
or something like that. Indeed splitting it will give you the original
number of digits.
Use (in a separate cell) =TEXT(A1,"0000000000000000000000000000000000")
or
whatever format string you used: then it will take the number of digits
that
you specify.

--
Kind regards,

Niek Otten

"The Mos$" <The wrote in message
...
I am trying to import a text file into an Excel worksheet so that each
character goes into a separate cell. There is no character which
delimits
the
characters which comprise the data (ie. the data looks like this:
'1023B4'
and worksheet should have '1' in the first cell, '0' in the next, '2'
in
the
next, and so on.
At the moment I have used OpenText to import all the data into the
first
cell and then used string functions to copy the Nth character into the
Nth
cell. However, some of my data from the text file looks like a very
large
number when imported to the worksheet and appears as something like
'1.62234211131231E+32'. I have changes the cell number format in
various
ways
and can get the number to APPEAR as it should in the first cell before
I
use
the string functions to move it to the appropriate cell. However, the
number
it moves is always of the form '1.62234211131231E+32'.
Is there a way to turn off this representation of the data as a number
?
Or
is there a better way to import each character into a separate cell ?

The text files will contain numerous lines of data in the way I have
described but the exact size will vary from file to file.

Excel 2002






  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,440
Default importing undelimited text file data, character-by-character

it may be a bit more complicated: the number of zeros in the format string
depends on the last two digits of the number.
Post again in this thread if you have problems with that

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
Try with the many zeros (20 I think) format. I tested it. Works for me.
Less effort than posting another message.

--
Kind regards,

Niek Otten

"The Mos$" wrote in message
...
Thank you for your reply.

I have tried your suggestion in this way:

Cells(5, 2) = "=Text(" & Cells(5, 1) & ", 0)"

- the line is displayed as I hope but it still produces the same
'4.44444444222222E+22' in the formula box when the cell is selected. I
also
have these lines:

Columns("A:A").Select
Selection.NumberFormat = "0"

which allow the cell to be displayed as I want it to be:

44444444422222200000000

but the string is still copied like this:

'4.44444444222222E+22'

and the 17th and 18th characters are always copied as 'E' and '+' and the
2nd character is always '.'.

I feel I should make it clear that I am writing code so that a user can
open
files of a similar format but where the number of characters in a line
will
be unspecified and will vary.



"Niek Otten" wrote:

You probably formatted the number like
0000000000000000000000000000000000
or something like that. Indeed splitting it will give you the original
number of digits.
Use (in a separate cell) =TEXT(A1,"0000000000000000000000000000000000")
or
whatever format string you used: then it will take the number of digits
that
you specify.

--
Kind regards,

Niek Otten

"The Mos$" <The wrote in message
...
I am trying to import a text file into an Excel worksheet so that each
character goes into a separate cell. There is no character which
delimits
the
characters which comprise the data (ie. the data looks like this:
'1023B4'
and worksheet should have '1' in the first cell, '0' in the next, '2'
in
the
next, and so on.
At the moment I have used OpenText to import all the data into the
first
cell and then used string functions to copy the Nth character into the
Nth
cell. However, some of my data from the text file looks like a very
large
number when imported to the worksheet and appears as something like
'1.62234211131231E+32'. I have changes the cell number format in
various
ways
and can get the number to APPEAR as it should in the first cell before
I
use
the string functions to move it to the appropriate cell. However, the
number
it moves is always of the form '1.62234211131231E+32'.
Is there a way to turn off this representation of the data as a number
?
Or
is there a better way to import each character into a separate cell ?

The text files will contain numerous lines of data in the way I have
described but the exact size will vary from file to file.

Excel 2002







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 - Formatting text in cell (character by character) TomC Excel Discussion (Misc queries) 0 January 29th 10 08:25 PM
255 character limit when importing data Dmac_59 Excel Discussion (Misc queries) 0 October 31st 07 06:18 AM
Excel-Match 1st text character in a string to a known character? bushlite Excel Worksheet Functions 2 January 15th 07 07:36 PM
character restrictions when importing data from a text file richtea Excel Discussion (Misc queries) 3 September 3rd 05 04:13 PM
Convert a 100,000 character linear text file Matthew[_9_] Excel Programming 4 February 17th 04 04:53 PM


All times are GMT +1. The time now is 06:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"