Excel import txt file carriage return as delimiters

[Guest]

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

 

[Dave Peterson]

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.)

 

[Bryan Hessey]

If you have less than 65536 items, AND, if the items represented equa
numbers of items per row, you could import them into column A, and the
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 you
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.

 

[hozey]

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

 

[Guest]

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?

 

[Guest]

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