+ Reply to Thread
Results 1 to 5 of 5

Count the number of elements in a formula OR the occurence of "+" sign

  1. #1
    Registered User
    Join Date
    07-01-2020
    Location
    England
    MS-Off Ver
    LibreOffice Calc / MS Excel 2003
    Posts
    37

    Unhappy Count the number of elements in a formula OR the occurence of "+" sign

    Hi,

    Is it possible to write a formula in B1 which counts the elements of the formula put into A1?

    Example:

    A1 contains the following:

    =34+142+35+50

    Therefore, the formula in B1 must return 4

    I was thinking one way to do it would be to count the occurrence of the + sign, and add 1 to that.


    However, it all seems impossible, because Excel refuses to acknowledge the formula itself, only its product. Once the = sign goes in front of the expression, it's, well, game over man, game over. You can no longer count the parts of that formula. Or can you?

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Count the number of elements in a formula OR the occurence of "+" sign

    If you wanted to count the number of "+" signs, you could use this:

    =LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(FORMULATEXT(A1),"+",""))

    You could then add 1 to it for your example.

  3. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Count the number of elements in a formula OR the occurence of "+" sign

    The OP looks like he has Excel 2003 if his profile is correct. Formulatext() was not available in that version of Excel.

    You could do it with a VBA user defined function (UDF) - something like:
    Please Login or Register  to view this content.
    Then use this UDF instead of formulatext() in Greg's solution above.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  4. #4
    Registered User
    Join Date
    07-01-2020
    Location
    England
    MS-Off Ver
    LibreOffice Calc / MS Excel 2003
    Posts
    37

    Re: Count the number of elements in a formula OR the occurence of "+" sign

    Thanks.

    Here is the formula l settled on, based on your reply (l am writing this for LibreOffice, "formulatext" wasn't recognised):

    =(LEN(FORMULA(A1))-LEN(SUBSTITUTE(FORMULA(A1),"+","")))+1

    Thank you.

  5. #5
    Registered User
    Join Date
    07-01-2020
    Location
    England
    MS-Off Ver
    LibreOffice Calc / MS Excel 2003
    Posts
    37

    Re: Count the number of elements in a formula OR the occurence of "+" sign

    Thanks. I've never tried VBA l think, but it's good to know!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count row of numbers and exclude " - " sign
    By ada2028 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-04-2019, 01:09 PM
  2. Replies: 4
    Last Post: 06-02-2017, 02:17 AM
  3. Replies: 2
    Last Post: 10-10-2013, 11:09 AM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. [SOLVED] Expanded and collapsed the rows by toggle "+" or "-" sign on left of the text
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-24-2013, 02:49 PM
  6. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  7. How do i create macro to count number of "Y" or "N" in different worksheets
    By tehseen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2009, 12:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1