Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to find the number of of +'s in a formula inside a single cell in excel.

If it were a normal cell with text inside, this would be quite easy. But due to the restrictions of the file I'm working with, I need to keep the formatting of the cell as it is.

In short:

  • Cell A1: =123,12+234,45+790,44
  • Cell A2: =2, as there were two +'s found in cell A1
share|improve this question
    
Is a VBA solution okay? –  jonsca Jul 4 '11 at 10:27

3 Answers 3

up vote 3 down vote accepted

You can use a custom vba function (put this code in a module) :

Option Explicit

Function countChar(cell As Range, pattern As String)
    countChar = UBound(Split(cell.Formula, pattern))
End Function

And then use this function in your Worksheet :

=countChar(A1;"+")
share|improve this answer
    
Good timing, and very succinct hehe! –  jonsca Jul 4 '11 at 11:49
    
@jonsca: indeed, nice shot :) –  JMax Jul 4 '11 at 12:15

VBA

Create a function using this code:

Function GETFORMULA(c As Range) As String
    If c.HasArray Then
        GETFORMULA = "{" & c.Formula & "}"
    ElseIf VarType(c) = vbString And Not c.HasFormula Then
        GETFORMULA = c.Value
    Else
        GETFORMULA = c.Formula
    End If
End Function

The ff. formula will display the number of occurences of "+" in it.

=LEN(GETFORMULA(cell))-LEN(SUBSTITUTE(GETFORMULA(cell),"+",""))  

Non-VBA

Highlight the cells that contain formulas. If you only need to deal with one cell, just add a ' before the = and skip the next step.

Press Ctrl + H. Enter = into the Find what and '= into Replace With. This will convert the formula to text.

Use this formula in a blank cell to count the number of occurences of "+".

=LEN(cell)-LEN(SUBSTITUTE(cell,"+",""))

To revert the cells back to the way they were, highlight them, press Ctrl + H and replace '= with =. Doing so will cause the formula above to show 0.

share|improve this answer

This function will take the formula of the cell that is passed to it (that is you'd call it by =NumPlus(A1) on your worksheet) and walk along it and count the plus signs. You could modify it slightly to take a character that you want to count as an argument.

Option Explicit
Function NumPlus(cel As Variant) As Variant
    Dim formstr As String
    Dim i As Integer

    NumPlus = 0
    formstr = cel.Formula

    For i = 1 To Len(formstr)
        If Mid(formstr, i, 1) = "+" Then
            NumPlus = NumPlus + 1
        End If
    Next i
End Function
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.