| موضوع میں صفحات: [1 2] > | Excel: How to translate highlighted but empty cells? دھاگا پوسٹ کرنے والے: CafeTran Trainer
|
|---|
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?
| | | |
- 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
| | | | | 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?
| | | |
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:
| | |
|
|
|
| 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
| | | |
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 
نیدر لینڈ
Local time: 00:06
رکن (2006)
افریکانسسےانگریزی
+ ...
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.
| | | |
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]
| | |
|
|
|
It almost worked, perhaps due to the old version of memoQ (2015) that I'm using. All cells of the Dutch column were imported ...
| | | | | #FFFFFF = white | Mar 17, 2022 |
You somehow failed to select the yellow cell. The RGB code for yellow is #FFFF00
| | | | | 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.
| | | | |
|
|
|
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
| | | | | Actually I know how to do it with Trados | Mar 17, 2022 |
I just didn't want to share any tips on Trados. But if you really need that...
You can first filter all yellow cells in Excel (Filter by color feature in Excel) and save the file as filtered. Then use the Bilingual Excel file type to import the file. Trados will only import the filtered cells.
To avoid bringing the Bilingual Excel file type up and down every time when you need or don't need the Bilingual Excel mode, I have created a permanent project in Trados with all file types disabled except for Bilingual Excel and SDLXLIFF. This saves much time.
[Edited at 2022-03-17 14:45 GMT]
| | | | | 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.
| | | | | موضوع میں صفحات: [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 Business Manager Lite | Create customer quotes and invoices from within Trados Studio
Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.
More info » |
| | 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 » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
Translation news related to CAT tools
