need to know the number of characters used in sum formula

D

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.)
[email protected]
[email protected]
New Delhi, India
 
J

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
 
J

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.
 
S

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).
 
B

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
 
R

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
 
D

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.)
[email protected]
[email protected]
New Delhi, India
 
Top