+ Reply to Thread
Results 1 to 6 of 6

Excel import txt file carriage return as delimiters

  1. #1
    Gordo T
    Guest

    Excel import txt file carriage return as delimiters

    I need to import a text file of data where the delimiter between fields is a
    carriage return.
    --
    Gordo T

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    If you have less than 65536 items, AND, if the items represented equal numbers of items per row, you could import them into column A, and then spread them across the required number of columns

    Assuming that there are 5 items per required row:

    In B1 put
    =INDIRECT("$a"&((ROW()*5)-4))

    In C1 put
    =INDIRECT("$a"&((ROW()*5)-3))

    In D1 put
    =INDIRECT("$a"&((ROW()*5)-2))

    In E1 put
    =INDIRECT("$a"&((ROW()*5)-1))

    In F1 put
    =INDIRECT("$a"&((ROW()*5)))

    and formula-copy these cells (B1 to F1) down to about one-fifth of your data items (ie, enough to cover the last line input)

    Then select columns B through F and Copy, then Paste Special = Values

    Then delete column A

    Hope this helps.


    Quote Originally Posted by Gordo T
    I need to import a text file of data where the delimiter between fields is a
    carriage return.
    --
    Gordo T

  3. #3
    Dave Peterson
    Guest

    Re: Excel import txt file carriage return as delimiters

    I think you have trouble.

    What's the delimiter for end of line? Carriage Return & Line feed?

    I think I'd try to open the file in a text editor program and change that field
    delimiter to something else.

    (I like UltraEdit (http://www.ultraedit.com) for this kind of thing.)

    Gordo T wrote:
    >
    > I need to import a text file of data where the delimiter between fields is a
    > carriage return.
    > --
    > Gordo T


    --

    Dave Peterson

  4. #4
    Registered User
    Join Date
    03-22-2006
    Posts
    6

    Similar problem

    When getting a pdf file with a list of contacts in vertical fields with a hard return between fields, is there a way to convert the vertical fields into horizontal fields short of cut and paste?

  5. #5
    tkwriter
    Guest

    RE: Excel import txt file with line break as delimiter

    Did you ever get a satisfactory answer to this? I have the same question: I
    want to import a txt file to an excel spreadsheet. What character can I use
    to specify that the delimiter is a line break (hard return)? Is it even
    possible to do this?


    "Gordo T" wrote:

    > I need to import a text file of data where the delimiter between fields is a
    > carriage return.
    > --
    > Gordo T


  6. #6
    EASY QUESTION
    Guest

    RE: Excel import txt file with line break as delimiter

    I'm by no means an Excel guru, more of a scrappy Excel user. All I did is
    copied the cells with hard returns in them into "Notepad" and then copied
    them back into Excel, then used the text to column feature to get rid of the
    "quote" marks that Notepad added. No complex code, and no purchase of Ultra
    Edit, and you can do it in 2 minutes. Sometimes just messing around gets you
    an answer

    "tkwriter" wrote:

    > Did you ever get a satisfactory answer to this? I have the same question: I
    > want to import a txt file to an excel spreadsheet. What character can I use
    > to specify that the delimiter is a line break (hard return)? Is it even
    > possible to do this?
    >
    >
    > "Gordo T" wrote:
    >
    > > I need to import a text file of data where the delimiter between fields is a
    > > carriage return.
    > > --
    > > Gordo T


+ 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