• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

[SOLVED] Need Help With Live Updating of Word Count

H

ham123

Member
Greetings experts,

I have this function which counts the number of words in a cell.
=IF(LEN(TRIM($B$22))=0,0,LEN(TRIM($B$22))-LEN(SUBSTITUTE($B$22," ",""))+1)

However, as I type into the cell, the word counting is not live. It is only after I press enter that I can see the word count.
I need it to be updated live for the user as I have placed a character limit on cell B2.

Any help is much appreciated! :)
 
Ham123

Firstly, Welcome to the Chandoo.org Forums

The issue is that the words aren't in the cell until you press Enter, Ctrl+Enter or Ctrl+Shift+Enter and so can't be accessed by formula until you have pressed one of those key combinations

So I am not sure what you want in possible

How many cells do you want that to apply to?

You could apply a Conditional Formatting code as soon as Enter is pressed so that it colors the cell if there are to many words?
or
Could you use a VBA Solution that put a Dialog for you to enter into ?
 
I believe what is asked here is a kind of form control behaviour that counts the words while typing - 5 words used/10 characters left f.e. Now the question has some ambiguity. There cannot be a link to the number of used words if the only thing you check is the number of characters used. Expecting this from a formula demonstrates some knowledge lack on how Excel is basically working.
Don't see how this is accomplished without switching to VBA and forms.
 
As Hui has mentioned.
You can't access what's being entered into cell until it's confirmed.
Even using VBA, code will not execute while cell is in edit mode and will not be able to capture the length as it's typed.

You'll need Userform + VBA as mentioned by GraH and Hui.

Ex: Assuming TextBox1 and Label1 in userform...
Code: 
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
With Me
    .Label1 = IIf(Len(Trim(.TextBox1.Value)) = 0, 0, Len(Trim(.TextBox1.Value)) - Len(Replace(.TextBox1.Value, " ", "")) + 1)
End With
End Sub
 
Hi, thank you for all your replies! :)

I believe I understand the limitations of what I was previously trying to do. Is there a way to link the cell to a VBA and show the live updating of words in a developer text box? :)
 
You must log in or register to reply here.
Back
Top