none
Problem with using formula for word count of specific cell

    Question

  • Hi!

    I'm trying to use a formula to count words in a specific cell. I use 

    =LEN(F2)-LEN(SUBSTITUTE(F2 ",""))+1

    However, nothing happens: when I use 'general' as format for F2 and 'general' for the cell with the formula, I get this error:

    The formula you typed contains an error.

    • For information about fixing common formula problems, click Help
    • To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library Group).
    • If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark ('). 

    

    When I use 'general' or 'text' as format for F2 and 'text' for the cell with the formula, the cell doesn't display the result, only the formula. When I evaluate the formula using the Formalas tab, it says: The cell currently being evaluated contains a constant.

    When I use 'number' as format for the cell with the formula, I get the same notification "The formula you typed contains an error, etc."

    What am I doing wrong??


    Friday, March 17, 2017 9:42 AM

All replies

  • Substitute should have three parameters, separated by commas- yours only has one comma.

    Without seeing a data sample, I'm just guessing- but it looks like maybe you have a comma separated list, and are trying to remove the space after each comma? if so, (extra spaces put in for readability)

    substitute(F2     ,          ", "       ,         ",")

                   text              old                 new

    This assumes that your source cell doesn't have [accidental] double spaces after any items, for example from manual entry errors.

    Friday, March 17, 2017 10:35 AM
  • Thanks for your reply. I noted the wrong formula in my previous message (I changed it a bit to see what might make a difference and copied the wrong formula). Here's the one I used:

    =LEN(F2)-LEN(SUBSTITUTE(F2 , " ", ""))+1

    This should do what I want: count the words in cell F2. The result should be 90. This cell contains 1 comma. I want to use the same formula on other cells as well; some of them contain multiple commas.

    Friday, March 17, 2017 10:53 AM
  • >I'm trying to use a formula to count words in a specific cell

    Given your revised formula, the commas should no longer matter (I was just guessing they mattered based on your first formula)

    The formula in your most recent post should work as expected, and you should be able to just replace F2 with the appropriate cell reference for your other cells containing text.

    The cell containing the formula should be formatted as 'general'. The format of the cell containing the text shouldn't matter (unless it also contains a formula and generates your text from data in other locations).

    p.s. just to check the syntax, I pasted your revised formula as-is in my worksheet and it worked just fine. I'm using A1 cell notation, but even if you are using R1C1 I'd just expect the formula to return an error, or Excel to try to propose a fixed formula that is in A1 notation...

    Friday, March 17, 2017 4:50 PM
  • Hi Peter,

    According to your description, do you mean you want to count the number of the characters in Cell F2 but do not want the result to contain the number of commas?

    If this is your case, please try using the formula below to see if it can meet your requirement:

    =LEN(SUBSTITUTE(F2,",",))

    As per the formula mentioned in your second reply, we can understand it step by step as follows:

    =LEN(F2)-LEN(SUBSTITUTE(F2 , " ", ""))+1

    1. SUBSTITUTE(F2 , " ", ""): replace all spaces(" ") in F2 with none("");
    2. LEN(SUBSTITUTE(F2 , " ", "")): calculate the number of none space characters in Cell F2
    3. =LEN(F2)-LEN(SUBSTITUTE(F2 , " ", ""))+1: calculate the number of spaces in Cell F2, then plus 1 (not sure why you added 1 here).

    For more information about how to use SUBSTITUTE function, you may read the article below:

    https://support.office.com/en-us/article/SUBSTITUTE-function-6434944e-a904-4336-a9b0-1e58df3bc332

    Hope the above information can be helpful. Please feel free to let me know if you have further concerns on this.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, March 20, 2017 7:54 AM
  • Thanks again for your answer, KERATL. I used the setting 'general' for both cells. Strangely enough, the formula still doesn't work, neither in the document I want to use it in, neither in another documennt. I'm using Excel 2010, so this should not be the problem...
    Monday, March 20, 2017 10:01 AM
  • Hi Peter,

    If possible, could you please share a sample file about your query via OneDrive (https://onedrive.live.com/) and paste a link here then we can help you better?

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, March 21, 2017 1:35 AM
  • Hi Yuki Sun,

    I don't have OneDrive, so I put the file up on WeTransfer: https://we.tl/k28SXXtyOZ  Column G should display the amount of words in the corresponding cell in column F, so G2 should read 90 for F2, G3 should read 92 for F3 etc.

    Thanks in advance!


    Tuesday, March 21, 2017 1:02 PM
  • Hi Peter,

    Thanks for your updates.

    I tested with your file in my environment (Excel 2016, English version) and the formula you mentioned above can work as expected:

    What's the language version of your Excel? 
    Can you use other functions in your Excel properly?
    What do you mean by "the formula still doesn't work"?

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Wednesday, March 22, 2017 2:19 AM
  • Thanks for trying the formula in the test data I sent. My language version is English (although I use it for a Ducth database), but I still can't fill in the formula the way you did. Strangely enough, =LEN(F2) does give a result, but the formula as a whole does not.

    It might be an idea if you put up the file you made (including the formula) on WeTransfer as well, so I can see if I can open it and if the formula gives results in my own version of Excel as well...

    Wednesday, March 22, 2017 10:51 AM
  • Hi Peter,

    I shared the file via OneDrive, you may sign up for free with your Microsoft account to download the file:

    https://1drv.ms/x/s!AuCU_Opwmf6jgWJEsEeX-9a8EDub

    Hope this can be helpful.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    18 hours 26 minutes ago