* Logo
* Translator Tools
*
* Motto
* *
TransTools suite Other software Knowledge base Feedback Back to Index | Site map
*
*
*
*
*
*
*
*
*
*
*
*
Also on:

TransTools on Twitter TransTools on Google Plus TransTools on LinkedIn TransTools on Scoop.It!
*

Excel File Splitter

Split an Excel file among translators based on word count and re-join translated parts

Excel File Splitter

Introduction

Microsoft Excel is the most common format for managing translatable text and its translations, especially in website and software localization. Website content, software strings, database records and other localizable text can be easily transferred into Excel before it is translated into multiple languages. However, such Excel files are often very large and need to be distributed among several translators in order to be translated on time. This is difficult because every row can have different number of words, from a dozen words to thousands of words, and splitting the file into parts with an equal number of rows will result in very different word counts in each part. Excel File Splitter is a special program that makes it very easy to split Excel files into multiple parts based on word count or character count, and to re-join these parts after translation.

Excel File Splitter: splitting configuration

Excel File Splitter automatically calculates how many rows must be copied into each part in order to achieve the desired word count or character count. Obviously, the number of words or characters in each part will not be the same because each row has different size and it is not possible to split an individual row into parts, but the size of each part will be very similar.

Capabilities

Excel File Splitter can do the following:

  • Split any number of Excel files into multiple parts.
  • Specify which columns do not require translation and therefore should not be considered when splitting into parts.
  • Specify which columns contain HTML code which must be counted differently than regular text.
  • Specify the header rows. You can choose whether to include these header rows in every split part or to include them in the 1st part only.
  • Indicate the number of equal parts which must be produced.
  • Specify whether to split based on word count, character count, or number of characters with spaces.
  • Re-join translated parts to produce a complete file which can be returned to the customer.

Additional use cases

Excel File Splitter may be used to produce quick word counts in order to estimate the translation effort.

Step-by-step guide

Below you will find a quick explanation how to split and re-join Excel files with Excel File Splitter.

Splitting a file

1. To split file(s), select “Split files into several parts” and click Next:

Select operation (split files)

2. Add files that you would like to split by clicking Add Files button (to select individual Excel files) or Add Folder button (to select a folder containing the Excel files that you need to split).
When the files are added to the list, select the sheet you want to split from the Worksheet column. If the translatable text is found in several sheets, read this note.

Select files to split

For each file, you must specify how to process the selected sheet. To do this, click the dropdown list in Processing Configuration column and choose New...:

Create processing configuration

3. Create a processing configuration for the selected sheet.
If the sheet contains header rows which do not require translation, indicate which rows must be ignored, using the preview to see the actual text in each row within a specific column.
For each column, specify one of the following: “Text” if the column must be translated and contains regular text, “Text + HTML” if the column must be translated and contains HTML code, or “Do not process” if the column must not be translated. Excel File Splitter automatically detects columns that contain embedded HTML, so “HTML + Text” is automatically selected for these columns.
Click OK when you are done.

Processing configuration editor

If you have selected several files and they all contain identical column structure, there is no need to create a new processing configuration: simply select a configuration which you already created.

If you need to make changes to the processing configuration for a specific file, you will need to choose New... again.

Click Next after you have configured all the files.

4. Configure splitting options: specify the number of parts for each file, which units (words, characters, or characters with spaces) must be used to determine the size of each part, and whether to keep header rows in part 2 and subsequent parts.

Splitting options

The file list shows the number of words, characters and characters with spaces for each file, so you can use this information to determine the number of parts. You can hover the mouse over a file to see the approximate size of each part after the file is split.

Click Next to advance to the next step.

5. Specify how to name the parts: choose from one of the suggested options for naming the generated parts. Then click Next to go to the next page.

Template for file names of each file part

6. Review how the files will be split: check whether all the settings are correct before splitting the file(s). Click Split Files button to finish the operation:

Review splitting settings

7. Check the results of the splitting operation: review the results of the operation on the final page.

Review operation results

If any problems were encountered during splitting, they will be described on the final page of the wizard.

At any step, you can go back to the previous step to change some parameters.

Joining previously split files

1. To join previously split file(s) after the parts have been translated, select “Join previously split files” and click Next:

Select operation (join files)

2. Select all the translated parts using the Add Files button.

As you add the files, they will be sorted automatically based on the information stored inside the files. At this time Excel File Splitter can only join files which were produced by the splitting operation, and you will see a warning message if you try to join files which were produced manually.
When you add the files, the program will automatically suggest the location and name of the final file. If you would like to change the suggested path, click Select button.
You must select all the parts of one previously split file. If you want to create several final files which were split before translation, you will need to repeat the operation for each group of parts.

Click Join Files button after all the parts have been added:

List of files to be joined

3. Check the results of the joining operation: review the results of the operation on the final page.

Review operation results

If any problems were encountered during splitting, they will be described on the final page of the wizard.

Current limitations

Currently, Excel File Splitter has a number of limitations, most of which will be addressed in future versions:

  1. Only one sheet per workbook: you can select only one sheet which contains the translatable rows for splitting. When Excel File Splitter splits the file into parts, it will split this sheet among all the parts, but all the other sheets will be copied to each part without modification.

    For example, if your workbook contains 2 sheets, Sheet1 and Sheet2, with 100 rows in each sheet, and you choose to split Sheet1, Part 1 will contain Sheet1 (with rows 1 through X) and exact copy of Sheet2, and Part 2 will contain Sheet1 (with rows X+1 through 100) and exact copy of Sheet2 (see the schematic below):
    How each part is composed
  2. Filtered or invisible rows: if the sheet you want to split contains filtered rows, i.e. rows that are not visible because they do not meet the conditions of the current filter, or rows that are not visible because their height was set to 0, these rows will still be considered when calculating the number of words or characters in order to decide how to split rows among several parts. So, the file will be split as though these rows were visible. However, these rows will remain hidden.

    To find out whether the current workbook contains filtered rows, pay attention to the following:
    1. Blue row numbers: if a sheet is filtered, row headers will be blue:
      Blue row headers in filtered sheets
    2. Special dropdown lists will be displayed in the header row of the sheet:
      Dropdown lists in filtered sheet header row

    Invisible rows are more difficult to spot. You will see that some row numbers are missing in the row headers:
    Invisible rows

    It will be possible to ignore filtered and invisible rows in the next version of Excel File Splitter.
  3. Equal parts: currently, it is only possible to split a file into equal parts. So, if the original sheet contains 10,000 words, and you split into 3 parts, each part will contain approximately 3333 words. More options will be added in a future version of the program.
  4. Equal number of parts for all selected files: if you select multiple files, each file will be split into the same number of parts. Capability to configure how to split each file will be added in the future.
  5. Regular word-breaking rules: if you deal with a language that does not have visible word boundaries, e.g. Chinese or Thai, you will need to select Characters in Split Files Based On section of Step 3. Future versions of the program will detect a language like Chinese or Thai and suggest you to select Characters instead of Words.
  6. Join previously split files only: the program can only join files which were produced by Excel File Splitter as a result of a splitting operation. If you split a file manually, the program will not be able to re-join it. This capability will be added in a future version of Excel File Splitter.

Trial version and documentation

If you want to see how Excel File Splitter will make you more productive, download the trial version and documentation in the Download section.

Developed by Stanislav Okhvat, 2007–2019

Microsoft Word®, Excel®, PowerPoint® and Visio® are registered trademarks of Microsoft Corporation.
Autocad© is copyright of Autodesk, Inc.
SDL Trados® (including SDL Trados Studio, Trados Workbench, TagEditor and Microsoft Word Addin) is a registered trademark of SDL plc.
memoQ is copyright of Kilgray Translation Technologies.
Wordfast© is copyright of Yves Champollion.

Software disclaimer