| Pages in topic: [1 2] > | Excel: How to translate highlighted but empty cells? Thread poster: Hans Lenting
|
|---|
I have an Excel file with 30 columns and 3000 lines.
How can I fill the highlighted, empty cells in column L with the translation of column E in the same row?
| | | | Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER
- If not already present, add a column with row numbers.
- Select the whole worksheet.
- Sort on the yellow colour of the column with the empty cells.
- Copy the matching cells from the column with the source language to a new worksheet.
- Translate this worksheet in your CAT tool and export it.
- Open the translation
... See more
- If not already present, add a column with row numbers.
- Select the whole worksheet.
- Sort on the yellow colour of the column with the empty cells.
- Copy the matching cells from the column with the source language to a new worksheet.
- Translate this worksheet in your CAT tool and export it.
- Open the translation and paste the cells into the empty yellow cells of the big worksheet.
- Resort the worksheet on the column with row numbers. Remove this column.
Something like that? ▲ Collapse
| | | | Stepan Konev 
Russian Federation
Local time: 10:16
English to Russian
| Bilingual Excel? | Mar 17, 2022 |
I’m not sure if I understand your question correctly, but probably you can use a bilingual Excel workflow? For example, in memoQ, you can set any two columns as source (column E) and target (column L). Can you share a screenshot of what exactly is highlighted in your Excel and what it all looks like?
| | | | Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER
Stepan Konev wrote:
Can you share a screenshot of what exactly is highlighted in your Excel and what it all looks like?
Something like this:
| | |
|
|
|
Stepan Konev
Russian Federation
Local time: 10:16
English to Russian
| Multilingual delimited text filter | Mar 17, 2022 |
In memoQ:
1. Import with options
2. Select a file
3. In Filter & configuration column select 'Multilingual delimited text filter (default)'
4. Click Change filter & configuration
5. Go to tab Columns, click B, select 'Source text' for the 'Meaning of selected column' field
6. Click G, select Translation, For column B, select language as in your project
7. Go to 'Excel options' tab, check 'Only import if background color is...'
8. Click Pick in Exce... See more In memoQ:
1. Import with options
2. Select a file
3. In Filter & configuration column select 'Multilingual delimited text filter (default)'
4. Click Change filter & configuration
5. Go to tab Columns, click B, select 'Source text' for the 'Meaning of selected column' field
6. Click G, select Translation, For column B, select language as in your project
7. Go to 'Excel options' tab, check 'Only import if background color is...'
8. Click Pick in Excel
9. Select any highlighted cell, click Add colors of selection and Finish
That's it. Proceed with the import procedure
 ▲ Collapse
| | | | Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER
Many thanks, Stepan.
memoQ rules when it comes to importing XLSX files ...
Let's hope that I can use my old memoQ 2015 for this too. Else, I'll have to use the workflow above.
| | | | Samuel Murray
Netherlands
Local time: 09:16
Member (2006)
English to Afrikaans
+ ...
German-Dutch Engineering Translation wrote:
Copy the matching cells from the column with the source language to a new worksheet.
No, just copy them into the correct location, but copy them with e.g. leading and trailing brackets, or copy them with a certain unique style, so that you can identify them when you open this Excel file in a CAT tool.
Of course, this depends on how good your CAT tool is. Can you tell your CAT tool to ignore all text that is not inside brackets? Or to ignore all text that is not in a certain style? Better yet, can you tell your CAT tool to only translate cells that are highlighted in a certain colour?
All this sorting and copying would only work, of course, if there are not hidden rows.
| | | | Stepan Konev
Russian Federation
Local time: 10:16
English to Russian
By default, if you don't change the Sheet # value, sheet 1 settings will apply to all other worksheets (if any).
If you have different columns for source or translation in different worksheets, then you can set different settings again by changing the Sheet # value. For example, if you have column C for source and columns K for target in worksheet 7, click the arrow up button to count to 7 and put new settings accordingly.
German-Dutch Engineering Translation wrote:
Let's hope that I can use my old memoQ 2015 for this too. Else, I'll have to use the workflow above. I did it in memoQ 2013, when a client of mine sent me lockits in Excel format with multiple target languages in different columns and multiple worksheets. The feature was already available at that time, 2013.
Update: if you don't want the first row (language codes) to be imported into memoQ, the checkbox 'First row contains column names' must be checked unlike shown on the screenshot above. I didn't check it by omission.
[Edited at 2022-03-17 08:52 GMT]
| | |
|
|
|
Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER
It almost worked, perhaps due to the old version of memoQ (2015) that I'm using. All cells of the Dutch column were imported ...
| | | | Stepan Konev
Russian Federation
Local time: 10:16
English to Russian
| #FFFFFF = white | Mar 17, 2022 |
You somehow failed to select the yellow cell. The RGB code for yellow is #FFFF00
| | | | Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER | Had to enter the colour manually | Mar 17, 2022 |
The picking didn't work. I had to type '#ffff00' and click 'Add'.
Created a MQXLIFF file, processed it in CafeTran Espresso, imported the file in memoQ. The results was nearly perfect: the inline linefeeds were lost. There's probably a way to avoid that loss  .
Thanks again, Stepan!
I'll keep this workflow for the next gigantic MMI project.
| | | | Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER
|
|
|
Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER
Here I’ve found a macro to extract highlighted cells to a ‘master’ worksheet.
Sub ExtractYellowCells()
Dim ws As Worksheet, MainWs As Worksheet, cell As Range
Set MainWs = Sheets("master") '-- change name as needed
For Each ws In Sheets(Array("Sheet1", "Sheet2")) 'add sheet names<... See more Here I’ve found a macro to extract highlighted cells to a ‘master’ worksheet.
Sub ExtractYellowCells()
Dim ws As Worksheet, MainWs As Worksheet, cell As Range
Set MainWs = Sheets("master") '-- change name as needed
For Each ws In Sheets(Array("Sheet1", "Sheet2")) 'add sheet names
If ws.Name MainWs.Name Then
For Each cell In ws.UsedRange
'if your cells are colored through conditional formatting, delete/comment below line & uncomment the line after
If cell.Interior.Color = vbYellow Then cell.Copy MainWs.Range(cell.Address)
'If cell.DisplayFormat.Interior.Color = vbYellow Then MainWs.Range(cell.Address) = cell.Value
Next
End If
Next
End Sub
From this, it's a long way to create a macro that exactly would perform the task required.
[Edited at 2022-03-17 14:20 GMT] ▲ Collapse
| | | | | | Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER | A lot of work | Mar 17, 2022 |
Samuel Murray wrote:
No, just copy them into the correct location, but copy them with e.g. leading and trailing brackets, or copy them with a certain unique style, so that you can identify them when you open this Excel file in a CAT tool.
Hello Samuel,
That would be a lot of work, and not a pleasant activity.
| | | | | Pages in topic: [1 2] > | To report site rules violations or get help, contact a site moderator: You can also contact site staff by submitting a support request » Excel: How to translate highlighted but empty cells? | Trados Studio 2022 Freelance | The leading translation software used by over 270,000 translators.
Designed with your feedback in mind, Trados Studio 2022 delivers an unrivalled, powerful desktop
and cloud solution, empowering you to work in the most efficient and cost-effective way.
More info » |
| | Protemos translation business management system | Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!
The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
Translation news related to CAT tools
