Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

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

Hi everybody,

Microsoft Excel LEN() function returns the number of characters in the selected cell. So far so good.

But how to get the number of characters in the selected portion of text within a cell?

For instance, assuming my cell contains the flwg lines:

aaaaa
bbb
cccccccccccccccc

and I highlight only the first line, can I write a VBA macro that would return the accurate number of characters actually included in my selection?

Many thanks in advance for any help.

Cheers.

PG
0
pg111
Asked:
pg111
  • 8
  • 5
  • 2
  • +1
1 Solution
 
Rory ArchibaldCommented:
You won't be able to run a macro while editing the cell, so it's a moot question. :)
0
 
pg111Author Commented:
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
0
 
Rory ArchibaldCommented:
You can't run a macro whilst you are in edit mode, I'm afraid.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pg111Author Commented:
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
0
 
Rory ArchibaldCommented:
Yes, I'm really sure.

Excel is a completely different beast to Word.
0
 
pg111Author Commented:
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.
0
 
Rory ArchibaldCommented:
What do you want it for? You could just copy and paste to a separate cell and use LEN if it's important.
0
 
pg111Author Commented:
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).
0
 
Elton PascuaCommented:
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

Open in new window

0
 
Rory ArchibaldCommented:
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.
0
 
pg111Author Commented:
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
0
 
Elton PascuaCommented:
Then I think this would do? Assuming the line break character is 10.

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

Open in new window

0
 
Robert SchuttSoftware EngineerCommented:
(specifically one line of text)
How about this then, show all lengths per line.

Make a function in a Module:
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 & Len(lin) & " - " & lin
    Next
    GetLineLengths = ret
End Function

Open in new window

In the sheet, for example the texts are in column A, then put in B1 (and copy down as necessary):
=GetLineLengths(A1)

Open in new window

Make sure the vertical alignment is the same and Wrap is checked (otherwise the output gets shown all on 1 line, must be a mistake I made but can't find how to correct it...)

This is how it looks:
capture
0
 
pg111Author Commented:
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
0
 
pg111Author Commented:
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

Open 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
0
 
Robert SchuttSoftware EngineerCommented:
ok, thanks!
0
 
pg111Author Commented:
Thanks a lot for your input and your help!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now