need to know the number of characters used in sum formula

[DILipandey]

Hi need to know the number of characters used in sum formula for example:-

In A1 we have =4+5+9
then I need "3" in B1.
Note: 3 is because there are 3 digits getting adding up in A1..

--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Bob I]

and what is the answer if the A1 contains =4+15+239

 

[JE McGimpsey]

One quick and dirty way, using a User Defined Function:

Public Function NumSumArgs(rng As Range) As Variant
Dim vResult As Variant
Dim sTest As String

With rng(1)
If Not .HasFormula Then
vResult = CVErr(xlErrRef)
Else
sTest = Mid(.Formula, 2)
vResult = Len(sTest) - Len(Replace(sTest, "+", "")) + 1
End If
End With
NumSumArgs = vResult
End Function


call as

B1: =NumSumArgs(A1)


IF you're not familiar with UDF's, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[DILipandey]

Hi JE McGimpsey,

Thanks. It is working fine.
Can I have same results by excel functions?
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[DILipandey]

if the A1 contains =4+15+239
then still need 3 as answer because only 3 numbers are getting added.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Roger Govier]

Hi

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

 

[Bob I]

Roger has the answer.

 

[JE McGimpsey]

One way:

Use Insert/Name/Define:

Names in Workbook: MyFormula
Refers to: =GET.CELL(6,Sheet1!$A$1)

Then use

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

There are some issues with using the XL4M GET.CELL(), especially with
older versions of XL.

 

[DILipandey]

It is giving #Name error.
I think it is because of the function LENA.
Thanks
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Shane Devenshire]

Hi,

Your problem may be more complicated if you are considering formulas that
include things like =4+5-222

But a more important comment - if you are really building formulas of this
type you are defeating the purpose of Excel. To change the results you must
edit the formula, in Excel the goal is flexibility, so you should reference
cells, not hard code numbers. You formulas should be more like =A1+C5+D6 or
if possible =SUM(A1:A3).

 

[Roger Govier]

sorry - my typo.
that should have read
=LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1

 

[Bernie Deitrick]

If A1 is a formula, LEN with return the length of the answer, not of the formula. Only VBA can
handle this.

Bernie
MS Excel MVP

 

[Roger Govier]

Quite right Bernie, my bad.
I was ignoring the fact that the OP said his cell started with = and thought
he just had 4+5+9

 

[DILipandey]

Thanks JE McGimpsey,
It is working perfectly fine. Thanks again.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[DILipandey]

Thanks Shane,

I am also a rationale user of excel and I know the criticallity and features
of excel. But the issue is that the users entered the numbers manually and
adds them and hence we need to know how many are getting added. Thanks.


--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India