Technical forums » Office applications »
Macro for Excel splitting documents every certain number of words
Thread poster: Juan Pablo Sans
Juan Pablo Sans
Juan Pablo Sans  Identity Verified
Mexico
Local time: 14:27
English to Spanish
+ ...
Jun 4, 2015

Hello guys,

I hope you are OK. I am starting as a Project Manager in a company, and I would like to speed processes up. I would like to know a Macro in Excel that will allow me to split the Excel file into smaller files every 1000 words or so. Additionally, I wouldn't like my text to be cut off or the cells to be divided, just to split the original file into smaller 1000 word-long chunks. Please let me know if that is possible.

Cheers
 
Rolf Keller
Rolf Keller
Germany
Local time: 20:27
English to German
Um ... Jun 4, 2015

[quote]juanpablosans wrote:[quote]

You question seems to take for granted that each cell contains only one word and that there is only one sheet with only one column OR one row.


[Bearbeitet am 2015-06-04 14:04 GMT]
 
Juan Pablo Sans
Juan Pablo Sans  Identity Verified
Mexico
Local time: 14:27
English to Spanish
+ ...
TOPIC STARTER
Yes I know Jun 4, 2015

Hello,

Yes, I know it is a very strange macro to run. I wanted to see if Excel can do it.

Appreciate your time.
 
Selcuk Akyuz
Selcuk Akyuz  Identity Verified
Türkiye
Local time: 21:27
English to Turkish
+ ...
don't know how to do that in Excel but Jun 4, 2015

Deja Vu X3, and possibly some other CAT tools can split projects based on word count and other criteria.
 
Juan Pablo Sans
Juan Pablo Sans  Identity Verified
Mexico
Local time: 14:27
English to Spanish
+ ...
TOPIC STARTER
With Trados? Jun 4, 2015

Oh OK,

Thank you very much. Do you know if I could do it with Trados?

Cheers
 
Andriy Yasharov
Andriy Yasharov  Identity Verified
Ukraine
Local time: 21:27
Member (2008)
English to Russian
+ ...
Kutools Jun 6, 2015

I have used Kutools for Excel for some time and it proved quite useful. It can split and merge files.
There's a trial version with full functionality.
http://www.extendoffice.com/product/kutools-for-excel.html
 
Dan Lucas
Dan Lucas  Identity Verified
United Kingdom
Local time: 19:27
Member (2014)
Japanese to English
It can do it, but why? Jun 6, 2015

juanpablosans wrote:
Yes, I know it is a very strange macro to run. I wanted to see if Excel can do it

Excel is pretty powerful in its own domain. In VBA pseudo-code, it would probably go something like this:

Code:
Set running total variable to zero

Set "words" variable to an empty string

Step through each sheet in active workbook

    On each worksheet that is not a chart sheet

        Start at row 1

            Start at column 1

                Read cell contents at current row, current column

                    Count words in the cell

                    Add word count to the running total

                    If word count is greater than x words

                        Create new workbook

                        Concatenate current cell contents to "words" variable

                        Paste "words" variable to cell A1 of new workbook

                        Save workbook under auto-generated name

                        Close newly saved workbook

                        Set running total variable to zero

                        Set "words" variable to an empty string

                    Otherwise

                        Concatenate current cell contents to "words" variable

                    end if

                    Add 1 to column counter

               Loop (column)

               Add 1 to row counter

        Loop (row)

    Loop (sheet)

Tidy up and quit

But the thing to do would be to avoid dumping everything in Excel in the first place. It wouldn't be the ideal repository for this kind of data and I would imagine that the details are going to get nasty. You'll start finding little edge cases that complicate matters, formatting issues and so on. Can you not get the data in a different format?

It might well be easier to walk through the Excel workbook, programmatically saving each sheet as a comma or tab-separated text file. Then you could use command line utilies to join the files from each worksheet together, then split them at an arbitrary word count. There are many ways to tackle such a task.

Either way, this would be a non-trivial project for most beginners, so if it's important get somebody else to do it. And if it's work, pay somebody or buy a utility to do the same. If it doesn't pay enough to cover the costs, don't take on the project.

If you plan to do it yourself, I recommend a good book on VBA. Or there are hundreds of good resources on the net.

Regards
Dan

[Edited at 2015-06-06 07:45 GMT]
 


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

Moderator(s) of this forum
Maya Gorgoshidze[Call to this topic]
Prachya Mruetusatorn[Call to this topic]

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

Macro for Excel splitting documents every certain number of words






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 »
Wordfast Pro
Translation Memory Software for Any Platform

Exclusive discount for ProZ.com users! Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value

Buy now! »