+ Reply to Thread
Results 1 to 5 of 5

importing undelimited text file data, character-by-character

  1. #1
    The Mos$
    Guest

    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. #2
    Niek Otten
    Guest

    Re: 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 Mos$@discussions.microsoft.com> wrote in message
    news:475F585A-E2AF-4056-B417-9A916FF294C8@microsoft.com...
    >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. #3
    The Mos$
    Guest

    Re: 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 Mos$@discussions.microsoft.com> wrote in message
    > news:475F585A-E2AF-4056-B417-9A916FF294C8@microsoft.com...
    > >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. #4
    Niek Otten
    Guest

    Re: 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$" <TheMos@discussions.microsoft.com> wrote in message
    news:4316E16E-B24D-48CA-BCE5-E20C332AAC45@microsoft.com...
    > 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 Mos$@discussions.microsoft.com> wrote in message
    >> news:475F585A-E2AF-4056-B417-9A916FF294C8@microsoft.com...
    >> >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. #5
    Niek Otten
    Guest

    Re: 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" <nicolaus@xs4all.nl> wrote in message
    news:u9$H%235fCGHA.1180@TK2MSFTNGP09.phx.gbl...
    > 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$" <TheMos@discussions.microsoft.com> wrote in message
    > news:4316E16E-B24D-48CA-BCE5-E20C332AAC45@microsoft.com...
    >> 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 Mos$@discussions.microsoft.com> wrote in message
    >>> news:475F585A-E2AF-4056-B417-9A916FF294C8@microsoft.com...
    >>> >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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1