Excel file with hidden rows
Thread poster: Heinrich Pesch

Heinrich Pesch  Identity Verified
Finland
Local time: 08:36
Member (2003)
Finnish to German
+ ...
May 8, 2012

Yesterday there was a discussion here about translating Excel-files. This reminded me of a case I had many years ago. Then I could not resolve it and lost a client.

This client had sent to their translators software strings in Excel. So there were the source column and lots of target columns. When they updated the software new strings were added between already translated rows, and the previously translated rows were hidden.

I did then not notice the hidden rows. My technique then was to copy the source column to Word and translate this using a CAT. After that I copied the translated column back to the Excel-sheet at the place where my target language was.

Of course this resulted in a mess. My translated column would be copied also into the hidden cells. I did not know what to do and send the word-file to the customer, stating that I could not resolve this problem. The PM responded, that no other translator had had any problems with this kind of task.

So now I would like how you would have solved this. (Today I would use Wordfast Pro, but that was not around in 2005). One possibility of course would have been to simply type the translation into each target cell, but how could it be done using normal CAT-software?


Direct link Reply with quote
 

Tony M  Identity Verified
France
Local time: 07:36
Member
French to English
+ ...
Two ideas May 8, 2012

First of all, it depends exactly what you mean by 'hidden'? If you mean that the height of the rows was set to '0' so they were invisible, then maybe the simple answer would just have been to set row heights globally to a non-zero value?

However, Wordfast Classic lets you translate an Excel spreadsheet directly, using a Word document as a working space. In this instance, I think I am right in saying that Wordfast ignores rows that are set to zero height.

However, you do still of course have the problem of what to do with all the unwanted columns etc.

My own feeling would be to copy out just the source language column into a fresh Excel spreadsheet, then translate that using Wordfast Classic, and then finally copy it back, this time in place of the target language column. Normally, this ought to maintain the original Excel formatting (that you lost when you copied via Word). However, I must emphasize this is only an idea, I haven't actually tried it!


Direct link Reply with quote
 
wotswot  Identity Verified
France
Local time: 07:36
Member (2011)
French to English
Hide the columns May 8, 2012

The way I do it is to actually hide the columns (not set their width to zero), a fairly trivial task in Excel, then translate in my CAT software (Transit NXT in my case), which has the option of ignoring hidden text (Excel, Word, etc.), and I reckon that Wordfast Pro has this option too. If it doesn't the only safe way would be to copy the columns to translate to another Excel workbook, translate that in Wordfast Pro and copy back the translated columns into the original workbook.

Direct link Reply with quote
 

Heinrich Pesch  Identity Verified
Finland
Local time: 08:36
Member (2003)
Finnish to German
+ ...
TOPIC STARTER
Relatively new feature? May 8, 2012

wotswot wrote:

The way I do it is to actually hide the columns (not set their width to zero), a fairly trivial task in Excel, then translate in my CAT software (Transit NXT in my case), which has the option of ignoring hidden text (Excel, Word, etc.), and I reckon that Wordfast Pro has this option too. If it doesn't the only safe way would be to copy the columns to translate to another Excel workbook, translate that in Wordfast Pro and copy back the translated columns into the original workbook.


This is what I would do today, but I just wonder how it could be done 5-6 years ago. Thanks anyway.
Yes the rows were hidden, not set to 0.


Direct link Reply with quote
 
wotswot  Identity Verified
France
Local time: 07:36
Member (2011)
French to English
5-6 years ago May 8, 2012

Sorry, I couldn't say how one would have gone about it 5 or 6 years ago.

Are you saying that 5 years ago one couldn't even translate an Excel workbook in Trados?

I'm a "late adopter" of CAT; I only know Transit NXT and have a passing acquaintance with other packages (Studio, DVX). Being an (ex) programmer, I found the early versions of Trados, Wordfast etc. very confusing and user-unfriendly, and just found it quicker to write my own macros in VBA. All that has now changed with the latest generation of powerful CAT packages.

Regards

Richard


Direct link Reply with quote
 

John Fossey  Identity Verified
Canada
Local time: 01:36
Member (2008)
French to English
WFC ignores hidden cells May 9, 2012

Wordfast Classic doesn't "see" hidden rows and columns. You have to give focus to a cell to start with and it steps right over hidden cells, without noticing them. I can't say if it's always behaved in that way, but at least for the last 4 or 5 years.

Direct link Reply with quote
 

István Hirsch  Identity Verified
Local time: 07:36
English to Hungarian
With Excel 2003 May 9, 2012

Studio 2009, for example, ignores hidden cells, but the problem is that the extracted (not hidden), then translated cells create a contiguous vertical array of cells after saving as target, and these cells are hard to reinsert/redistribute among the already existing hidden target Excel cells left untouched, I think.

Therefore it seems easier to unhide each hidden rows and work as follows:

If A is the source column, B is your target language column, and G is the first empty column, enter the formula below into cell G1:

=IF(B1="",A1,"")

then move the mouse over the bottom right corner of G1 and drag the filling plus down as long as there are data in column A, to extract in this column all cells to be translated from column A.
Copy/paste this column into a new file, translate it, then copy/paste the target column into the H column of the previous Excel file.
*Select this column, Copy, click on B1, then on the arrow next to the Paste symbol, select Paste Special from the drop-down list and check the ’Skip blanks’ checkbox, then OK.*

Instead of the part between asterisks using Paste Special, you could also work with a similar formula (written, say, in I1) as above to unite old and new target cells in column I, then replace column B with it.

If restoring the original hidden-unhidden structure is important, filter column G or H with blank cells, hide the filtered rows, then column G and H (and I) can be deleted.


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Excel file with hidden rows

Advanced search






WordFinder
The words you want Anywhere, Anytime

WordFinder is the market's fastest and easiest way of finding the right word, term, translation or synonym in one or more dictionaries. In our assortment you can choose among more than 120 dictionaries in 15 languages from leading publishers.

More info »
Anycount & Translation Office 3000
Translation Office 3000

Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search