Macro for Excel splitting documents every certain number of words
Thread poster: juanpablosans

juanpablosans  Identity Verified
Mexico
Local time: 04:33
Member (2011)
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


Direct link Reply with quote
 

Rolf Keller
Germany
Local time: 05:33
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]


Direct link Reply with quote
 

juanpablosans  Identity Verified
Mexico
Local time: 04:33
Member (2011)
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.


Direct link Reply with quote
 

Selcuk Akyuz  Identity Verified
Turkey
Local time: 06:33
Member (2006)
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.

Direct link Reply with quote
 

juanpablosans  Identity Verified
Mexico
Local time: 04:33
Member (2011)
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


Direct link Reply with quote
 

Andriy Yasharov  Identity Verified
Ukraine
Local time: 06:33
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


Direct link Reply with quote
 

Dan Lucas  Identity Verified
United Kingdom
Local time: 04:33
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]


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 »

Macro for Excel splitting documents every certain number of words

Advanced search






Déjà Vu X3
Try it, Love it

Find out why Déjà Vu is today the most flexible, customizable and user-friendly tool on the market. See the brand new features in action: *Completely redesigned user interface *Live Preview *Inline spell checking *Inline

More info »
Across v6.3
Translation Toolkit and Sales Potential under One Roof

Apart from features that enable you to translate more efficiently, the new Across Translator Edition v6.3 comprises your crossMarket membership. The new online network for Across users assists you in exploring new sales potential and generating revenue.

More info »



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