Count numbers separated by plus sign in formula

[YasserKhalil]

Hello everyone
I need to count the numbers in a formula that looks like that

=20+30+50

The numbers are separated by plus sign
I tried this UDF but it doesn't deal with formulas

Function COUNTTEXT(ref_value As Range, ref_string As String) As Long
    Dim I As Integer, Count As Integer
    Count = 0
    If Len(ref_string) <> 1 Then COUNTTEXT = CVErr(xlErrValue): Exit Function
        For I = 1 To Len(ref_value.Value)
            If Mid(ref_value, I, 1) = ref_string Then Count = Count + 1
        Next
    COUNTTEXT = Count
End Function

 

[Marc L]

Hi !

No need a loop : just use Spllt function and UBound + 1 is the result …
​

 

[YasserKhalil]

I tried this and it is ok

Function CountNumbers(Rng As Range)
    Dim Arr
    If Rng.HasFormula = True Then
        Arr = Split(Rng.Formula, "+")
    End If
    CountNumbers = UBound(Arr) + 1
End Function

 

[moroccan]

Or just

len(X)-len(replace(X,"+",""))

 

[YasserKhalil]

Thanks Mr. Moroccan
Can you provide the full example please?

 

[moroccan]

Function CountNumbers(Rng As Range) As Integer

If InStr(Rng.Formula, "+") Then CountNumbers = Len(Rng.Formula) - Len(Replace(Rng.Formula, "+", "")) + 1
End Function[CODE]

 

[YasserKhalil]

That's great Mr. Moroccan
Thank you very much for help

 

[Marc L]

I knew the Excel formula via Replace but I doubt than Yasser could catch it,
the reason why I told him to use Split and UBound functions
'cause he already knows how to use them …

Anyway, well done moroccan !
​

 

[Asheesh]

Just for fun..if you are using Excel 2013 or above then you can try this as well

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

 

[YasserKhalil]

Thanks Mr. Asheesh
I tried that formula and it resulted in 6 but the result would be just three

 

[Asheesh]

YaseerKhalil - use the below one

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

 

[YasserKhalil]

Thank you very much Mr. Asheesh for this simple and great formula
It works now very well
Regards

 

[Asheesh]

you welcome mate