How to VBA count how many characters in selected portion of text in an Excel cell?

You won't be able to run a macro while editing the cell, so it's a moot question. :)

Thanks rorya.
Why not? Can't I add a context menu item linked to a macro whenever text is selected in the cell? I have been able to custom the context menu for selecting the whole cell. Is it impossible to do the same whenever just a portion of the cell is selected?

Many thanks in advance for clarifying.

Cheers.

PG

You can't run a macro whilst you are in edit mode, I'm afraid.

Are you really sure?

For instance, in Word 2003, I'm perfectly able to run a macro on selected text. Is it different with Excel?

PG

Yes, I'm really sure.

Excel is a completely different beast to Word.

OK. Thanks for your help. I accept your advice. So absolutely no means to know how many characters are currently highlighted in the active cell? That's a pity.

What do you want it for? You could just copy and paste to a separate cell and use LEN if it's important.

I'm a freelance translator and frequently have to translate software UI strings that are contained in Excel cells. Those strings have character-limitations constraints. So I need to know how many characters my translation has. It is not a problem with the whole contents of the cell, but it is when the constraint applies to a portion of it (specifically one line of text).

I'm not sure if I'm understanding it correctly. I think you're mainly concerned in determining the count of a certain part of the cell. Below is a possible solution, you can make some of the text bold and then the function counts it.

*Note*: The function returns incorrect value if there are multiple formats in the cell. Probably something you can improve on.

Function FindBold(theValue)
    
    Dim c As Integer
    Dim i As Integer
    
    Application.Volatile
        
    For i = 1 To theValue.Characters.Count
        If theValue.Characters(i).Font.FontStyle = "Bold" Then
            c = c + 1
        End If
    Next i
    
    FindBold = c

End Function

Select allOpen in new window

Didn't get notified of your reply for some reason.

If you copy the selection, it ought to be possible to directly determine the length of the text on the clipboard. If you need a live count it might be possible with a COM addin.

Thanks techfanatic.

Interesting. Might be a track. Actually formatting is not an issue since as I mentioned most of involved strings are supposed to be software UI strings.
However what makes me hesitate is I have to first bold the selected text (or whatever else formatting).

Just to make things clearer, here is an example of such string:

Room temperature is too high.
After cooling the room, allow machine
to cool down to room temperature.
Please retry later.

What I'm concerned in knowing is how many characters are contained in the first line ("Room temperature is too high.").

So if I follow you, I first must select and bold the first line, then
apply your macro, display and record the count of chars, and unselect and unbold the line.

Another question is: Will I be able to call the macro from context menu by right-clicking the selected (presumably bold) text?

Many thanks in advance for your answers.

PG

Then I think this would do? Assuming the line break character is 10.

=FIND(CHAR(10),A1,1)

Select allOpen in new window

Thanks, robert_schutt. Looks promising. I'll try it out.
Now I'm obliged to leave folks but I will revert tomorrow.

Thanks to each of you.

Cheers.

PG

Hi, robert_schutt.

Thanks a lot for your suggestion.
Actually I integrated it into my existing macro that was already counting the actual number of characters contained in the active cell. I slightly tweaked your function to my requirements as I need to get a message box displayed rather than writing the requested info into a cell.

So my procedure and your modified function would look like the flwg:

Sub ActiveCellCharCount()
If IsNumeric(ActiveCell.Value) = True Or IsDate(ActiveCell.Value) Then End

On Error GoTo Terminate

charcnt = ActiveCell.Characters.Count

Dim lin

     For Each lin In Split(ActiveCell.Text, Chr(10))
        lincnt = lincnt + 1
    Next

m = MsgBox("The active cell contains " & charcnt & " char(s)," & vbCrLf & "on " & lincnt & " line(s):" & vbCrLf & vbCrLf & GetLineLengths(ActiveCell), vbOKOnly, "Actual number of chars in the active cell")

Terminate:
End Sub

Public Function GetLineLengths(ByRef rng As Range)
    Dim ret As String
    ret = ""
    Dim lin
     For Each lin In Split(rng.Text, Chr(10))
        If ret <> "" Then ret = ret & vbCrLf
       ret = ret & lin & vbTab & " [" & Len(lin) & " char(s).]"
    Next
    GetLineLengths = ret
End Function

Select allOpen in new window

Allow me time to test it in real life and I will revert to you here to grant credits for your help.

Cheers.

PG

ok, thanks!

Thanks a lot for your input and your help!