+ Reply to Thread
Results 1 to 9 of 9

How to count no. of "+" in cell?

  1. #1
    Forum Contributor
    Join Date
    09-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    375

    How to count no. of "+" in cell?

    How to count no. of "+" in cell A1:
    =2+3+4+5+6

    The formula below in cell B1 can give the answer:
    Please Login or Register  to view this content.
    As FORMULATEXT cannot be used in excel 2003, is there any replacement?

    It is better not to use:
    1. Module code
    2. VBA

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to count no. of "+" in cell?

    The best way would be to format cell as text and then paste formula =2+3+4+5+6
    Use formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 =2+3+4+5+6 4
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: How to count no. of "+" in cell?

    I have found a way using a named range and little known trick =GET.CELL
    My first attempt is rather limiting and so I have started a thread targeting my specific query

    Workbook attached
    - it's a solution that works
    - the contents of cells in column B are easy enough to follow!
    - but perhaps someone reading this can add the final magic dust to make it more flexible

    EDIT
    - this has been "enhanced" in post#4
    Attached Files Attached Files
    Last edited by kev_; 11-23-2017 at 02:00 PM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: How to count no. of "+" in cell?

    This provides a more flexible solution than post#3 and does not require VBA
    - see attached workbook

    Formula in Cell A2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In cell B2
    this is the reference to the cell containing the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula in cell C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Returned value in cell C2 (= number of "+")
    4 (which is the correct count)

    Named Range "MyFormula" has this in RefersTo box
    Please Login or Register  to view this content.
    I cannot think of a way to avoid using a helper cell

    Explanation:
    - named range MyFormula is looking to the value in "helper cell" (ie one cell to the left of where =FORMULA is placed)
    - a valid cell reference must be entered in the helper cell
    - the INDIRECT function looks to the text value of that helper cell (value = A2)
    - GET.CELL(6,ref) provides the formula text of referenced cell
    - the formula in the named range can be distilled down to GET.CELL(6,A2)

    The file must be saved as macro enabled - because it makes use of the old style of macros superceded by VBA
    Attached Files Attached Files
    Last edited by kev_; 11-23-2017 at 02:30 PM.

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: How to count no. of "+" in cell?

    As requested, non-VBA solution was offered in post#4

    Personally, I would use a simple one line User Defined Function (UDF)
    - it contains VBA but the adavantage is that a UDF can then be used like any other Excel formula

    In general module:
    Please Login or Register  to view this content.
    Formula in B2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Which is much simpler than any non-VBA solution
    (no helper cells, no intermediate steps, simple to follow, like any other formula)

    Test in attached workbook
    Attached Files Attached Files
    Last edited by kev_; 11-23-2017 at 02:31 PM.

  6. #6
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to count no. of "+" in cell?

    Not work in 2003 (delete)
    Last edited by José Augusto; 11-23-2017 at 02:21 PM. Reason: I think that is for Office 365.

  7. #7
    Forum Contributor
    Join Date
    09-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    375

    Re: How to count no. of "+" in cell?

    As formula in cell A1 may be changed after setting B1,
    it is better to make a formula at B1 like
    Please Login or Register  to view this content.
    that B1 may be varied upon change in A1.

    As the excel will be sent to other users that may not good at excel and using excel 2003,
    it is better to keep it simple and no VBA and UDF.

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: How to count no. of "+" in cell?

    @london7871
    In post#1 you asked for a "replacement" to FORMULATEXT without VBA

    Perhaps we should have explained better
    What you want is NOT possible with Excel 2003
    - the functionality to directly extract formula text is simply not available
    - repeating the question will not change that

    The best that can be offered are workarounds
    - 3 of those have been provided to you
    - each one gives you the correct result and counts "+"

    AlKey has provided the simplest (post#2)
    - it involves using a helper cell which requires pasted value (from cell containing formula) plus a simple formula

    Kev's UDF solution is exactly the same as using a formula (post#5)
    - the user will not notice the difference

    Kev's other solution does not use VBA (post#4)
    - but it is more difficult for the user
    - it requires a formula inside a named range, a helper cell, and a formula inside a cell
    Last edited by kev_; 11-24-2017 at 01:35 AM.

  9. #9
    Forum Contributor
    Join Date
    09-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    375

    Re: How to count no. of "+" in cell?

    Thank kev_ to advise that it is NOT possible with Excel 2003.

    Also thanks to Alkey for suggestion.

+ 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. Replies: 1
    Last Post: 02-20-2015, 01:13 PM
  2. [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
  3. [SOLVED] Cell Formula to count time with "Greater Than or Equal to" and "Less Than"
    By chriswhite1982 in forum Excel General
    Replies: 3
    Last Post: 06-16-2013, 12:30 AM
  4. [SOLVED] Count "A" and "V" as separate characters in a single cell
    By sportboy712 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-04-2013, 12:26 PM
  5. [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
  6. Replies: 1
    Last Post: 07-16-2010, 02:44 AM
  7. Replies: 7
    Last Post: 05-13-2006, 05:02 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