Converting a table in text form into table form

Have you ever copied a table from the web and pasted it into a spreadsheet? Many times the table gets squashed into the first column. Thankfully, there is a way around this problem that is a lot more pleasant than copying the table cell by cell.

There are various methods for fixing this problem. I will show you how to do so for the two most widely used desktop office suites, Microsoft Office 2010 and Libreoffice 3.3.3.

12 13 14 15 16 17 18 19

13 14 15 16 17 18 19 20

14 15 16 17 18 19 20 21

15 16 17 18 19 20 21 22

16 17 18 19 20 21 22 23

17 18 19 20 21 22 23 24

18 19 20 21 22 23 24 25

10 11 12 13 14 15 16 17

Above is a text table, similar to what you mostly find on the internet. If you copy this to a spreadsheet, then you'll get this:

Libreoffice 3.3.3


Step 1: Open Writer.
Step 2: Copy the table there

Step 3: Select the text and then go to Table » Convert » Text to table.

Step 4: 

A dialog box will appear, asking at what intervals to create columns. Since these numbers are separated by spaces, you will have to select the other option and then create a single space.

Step 5:

Voilà!

This table can now be pasted back into the spreadsheet.

Microsoft Office 2010

For Microsoft Office, the procedure is very similar to the one outlined above for Libreoffice. 

Step 1: 

Copy and paste the table into Word, and then go to Insert » Table » Convert Text to Table.

Step 2:

Select the Other option and press space once. If space doesn't work, then press the right arrow key so that the cursor is one space to the right. 

Step 3:

Done!

Scenario II

Sometimes, it isn't so easy. There are quite a few articles around the web showing you how to convert text to table this but none (that I'm aware of) that show you how to handle multiple spaces between values.

Here, we have a table of the insolation (energy received by the sun) at a specified location (via NASA [1]). There are multiple spaces between the values that make it difficult for us to perform the text to table conversion but don't worry, I will show you how.

                                          Sunlight
                                          Weighted
                                 Daily     Cosine
                                Average      of
                                Sunlight   Zenith
      Date   Sunrise   Sunset    (W/m�)    Angle
      ----   -------   ------    ------    -----
2011/06/01     4:27    19:21     478.44   0.737
        02     4:26    19:21     479.17   0.737
        03     4:26    19:22     479.86   0.738
        04     4:25    19:23     480.51   0.738
        05     4:25    19:24     481.12   0.739
        06     4:25    19:24     481.69   0.739
        07     4:25    19:25     482.22   0.739
        08     4:24    19:25     482.71   0.740
        09     4:24    19:26     483.16   0.740
        10     4:24    19:27     483.57   0.740
        11     4:24    19:27     483.94   0.741
        12     4:24    19:28     484.27   0.741
        13     4:24    19:28     484.55   0.741
        14     4:24    19:28     484.80   0.741
        15     4:24    19:29     485.00   0.741
      

For Libreoffice 3.3.3

Step 1:

The headings are not needed and will just complicate things so let's copy all the values to a spreadsheet.

Step 2:

Then, in the column, adjacent to the column that you have copied the text into paste the following formula:

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

This will yield the result of removing the extra spaces and keeping only one space between the values.

From now on, you can just follow the steps that were outlined for Libreoffice earlier. Remember to use Paste Special (Ctrl+Shift+V) and use the unformatted text option while pasting the table into Libreoffice writer.


You will probably end up with this:

For Microsoft Office 2010

Firstly, please read what I have written for this type of situation in the Libreoffice section. I had to change the formula for Microsoft Excel. It now reads:

=TRIM((SUBSTITUTE(A1;CHAR(160);CHAR(32))))

This will replace the multiple spaces with one space.

To apply this formula to all of the values, the formula will have to be dragged down using the "plus" sign that appears when hovering over the below right side of the selected cell.

Now copy these cells and paste into Microsoft Word using the the "Keep Text Only" option.

You now have a text table. You can now repeat the steps that were shown above for Microsoft Office 2010.

Your time spent reading this article will be rewarded multiple-fold by this cool trick whenever you want to paste a text table into a spreadsheet. Hope it proves useful to you.

Links
[1] NASA, Insolation at a Specified Location


Word count: 610

All articles

The Review Meter

How Software is reviewed