need to know the number of characters used in sum formula

  • Thread starter Thread starter DILipandey
  • Start date Start date
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.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India
 
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
 
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
 
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
 
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.
 
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
 
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).
 
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
 
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
 
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
 
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
 
Back
Top