Technical forums » Office applications »
Word count for Excel comments
Thread poster: Jane Phillips
Jane Phillips
Jane Phillips  Identity Verified
France
Local time: 13:19
Member (2013)
French to English
Jan 26, 2016

This isn't about counting words in Excel cells, that I can do and there are already plenty of threads about it, this is about comments. I've just finished translating an Excel spreadsheet set up as a form to fill in and just before I finished I realised that the client had put comments for virtually every heading (to explain the terms used). I'd already quoted for the job based on the Trados wordcount, but Trados (Studio 2014) hadn't noticed the comments either ... See more
This isn't about counting words in Excel cells, that I can do and there are already plenty of threads about it, this is about comments. I've just finished translating an Excel spreadsheet set up as a form to fill in and just before I finished I realised that the client had put comments for virtually every heading (to explain the terms used). I'd already quoted for the job based on the Trados wordcount, but Trados (Studio 2014) hadn't noticed the comments either .

The translations were not complicated to do but obviously I hadn't included them in my word count so it was a bonus for the client and they were a pain to check through to make sure I'd done them all (on certain sheets the form was repeated up to 12 times to cover different types of information, so repetition of headings and comments as you scrolled down the page).

Spent half an hour with friend Google who didn't come up with a solution for counting words in comments.

Anyone else come across this and found the answer?

I have this horrible feeling it's really simple and I'm just not seeing it! ▲ Collapse
 
Ben Senior
Ben Senior  Identity Verified
Germany
Local time: 13:19
German to English
Use a macro Jan 26, 2016

Hi Jane,

You can either export all the words in the comments to Word or you can count them in directly in Excel. With the first method you can see what was in the comments and decide if that is what you want to count, sometimes the author's name is at the start of each comment and should not be counted. With the second method you just get a message box with the word count.

Here's the two macros that you need:

Sub CopyCommentsToWord()
Dim xComment ... See more
Hi Jane,

You can either export all the words in the comments to Word or you can count them in directly in Excel. With the first method you can see what was in the comments and decide if that is what you want to count, sometimes the author's name is at the start of each comment and should not be counted. With the second method you just get a message box with the word count.

Here's the two macros that you need:

Sub CopyCommentsToWord()
Dim xComment As Comment
Dim wApp As Object
On Error Resume Next
Set wApp = GetObject(, "Word.Application")
If Err.Number 0 Then
Err.Clear
Set wApp = CreateObject("Word.Application")
End If
wApp.Visible = True
wApp.Documents.Add DocumentType:=0
For Each xComment In Application.ActiveSheet.Comments
wApp.Selection.TypeText xComment.Parent.Address & vbTab & xComment.Text
wApp.Selection.TypeParagraph
Next
Set wApp = Nothing
End Sub

Sub CountCommentWords()
' Ben Senior 26 Jan 2o16

Dim iWordCount As Integer
Dim iCommWords As Integer
Dim sComment As Comment
Dim xlBook As Workbook
Dim xlSheet As Worksheet

iWordCount = 0
iCommWords = 0

For Each xlSheet In ActiveWorkbook.Sheets
For Each sComment In xlSheet.Comments
iCommWords = UBound(Split(sComment.Text, " "), 1) + 2
iWordCount = iWordCount + iCommWords
Next sComment
Next xlSheet

MsgBox "Total words in comments: " & CStr(iWordCount), , _
"Comment word count"

End Sub


Regards,
Ben ▲ Collapse
 


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 »

Word count for Excel comments






CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

Buy now! »
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! »