[Solved] Inputting text from an email to Calc

[radardoug]

Hi,
I am working on an application where I want import cells from an email. I thought if I used the Windows clipboard and formatted the text string as csv, it would import into multiple cells, but no.
So is there a way to take a text string from the windows clipboard and split it into multiple cells? What special formatting needs to be applied to the text string?

See my message near the end of this for how I solved it.

[Villeroy]

If it is only one row (paragraph), everything goes into one cell.

[Bill]

The Text Import dialog can be used to split the text, but it won't appear if only one paragraph of text is pasted in Calc. To invoke the Text Import dialog, copy two paragraphs (one can be blank) and paste in Calc using Edit > Paste Special > Unformatted Text.

[radardoug]

So it turns out if you have your data separated one item per line then it will paste in vertically in separate cells. But I am trying to paste horizontally. Any body got any thought on that?
By the way, it will do that without Paste special.

[FJCC]

If the text has a character that can be used as a field separator, you can use the menu Data -> Text To Columns after initially pasting the data into a single column.

[radardoug]

Tried that, doesn't work.

[radardoug]

Probably because its already in a column. I want it in rows, across.

[robleyd]

Once pasted into a column you can select, then cut the contents and use the Paste All and Transpose options in Paste Special. See the Help on transposing tables for more information.

[FJCC]

Just to make sure that we are talking about the same thing, I entered data in A1:A4 in a comma separated format. I then copied to over to D1:D4 and applied Text To Columns. Normally, you would not copy the data to a different location but I wanted to show the before and after states.

[radardoug]

Just to finish on this, so if you format your text with a tab character (9) in between each item, and finish with a CR character (13),it works beautifully.
You need to use Paste Special.

[Bill]

Just to finish on this, so if you format your text with a tab character (9) in between each item, and finish with a CR character (13),it works beautifully.
You need to use Paste Special.

That's right. A CR in pasted text starts a new row in Calc and separator characters split the text in each row into columns. If you don't use Paste Special, then the text in each row is pasted in one cell. If you use Paste Special, then the Text Import dialog appears and you can choose the separator character that splits the text.

[John_Ha]

I want import cells from an email. I thought if I used the Windows clipboard and formatted the text string as csv, it would import into multiple cells, but no.
So, is there a way to take a text string from the windows clipboard and split it into multiple cells?

1. Save the data as fred.txt (or fred.csv), using a csv format.
2. Start Calc.
3. File > Open ..., and navigate to fred.txt.
The data is inserted as defined by the csv format used.
4. Save as a .ods file.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[radardoug]

Sorry, my problem is a little more complicated and that solution wont work, but my current solution is looking good.