[Solved+Issue] Convert text to table multi columns

[fxparlant]

Hi,

I am missing a feature found in ms-word when converting text to table: I can tell the number of columns and the text is displayed in each column.

For example
1
2
3
1a
2a
3a
1b
2b
3b

will become in one click

Code: Select all

| 1  | 2  | 3   |
|1a  | 2a | 3a  |
|1b  | 2b | 3b  |

I don't know any easy way to do this in open office. Can someone give me a hint or point me toward a macro to do this ?

Thanks a lot

François

[Jon]

Writer includes the ability to convert text into a table and you can choose the separator that defines the cells (see tables|convert). However, you cannot define the number of columns in the way you described. In the example you gave, you would choose "paragraph" as the separator, but it would produce a table that is composed of one column and 9 rows. The only way to produce the results you wanted in the example would be to have the items you want in the same row, using the separator you designate in the conversion. For example, to produce your result, your writer document could be like this:

1, 2, 3
1a, 2a, 3a
1b, 2b, 3b

When you did the conversion you would select a comma as the separator (you can use a different separator).

[Villeroy]

Copy the list into a spreadsheet (paste special as unformatted text").
With the list beeing selected, type "vector" into the name box left of the formula box.
Select another sheet (tab at the bottom of the spreadsheet view)
Put this formula into cell A1: =INDEX(vector;(ROW()-1)*3+COLUMN())
Drag A1 to the right until C1 using the tiny square at the bottom-right corner of a cell selection.
Drag A1:C1 down until you get errors.

[acknak]

I'm just curious: under what circumstances do you use this feature in Word?

I use Text > Table often, but I don't remember ever needing to convert any text data that was in the form that you started with.

[fxparlant]

Hi,

To answer Acknak first, I'll give two examples when I used this:
1) I find a glossary in a web page, with dd tags for definitions. When copy and pasting, I get the list terms above definitions, without separators.
2) I import my telephone contacts (vcf) or calendar (ics), and I want to get rid of some columns for another use. Ms-word does the job of getting every item in its column easily.
You are right, it's not an everyday use, but when you have 300 lines to sort, it is really useful.

Now, thank you so much Villeroy for the solution in calc. I took some time looking for the solution with "indirect" functions, without succeeding. Your formula is quite nice worth being to a special very useful formulas memo

Thank again for your answers

François

[acknak]

Makes sense--thanks.

It seems it would be worth requesting this as an enhancement... ok, it's already there: Issue 63065: Allow to specify the column count in text->table conversion.

You can register there and add a vote or comment.