Need excel expert

kashif.special2005 · Apr 29, 2011

Hi,

I am Kashif,

In a cell I have formula like =45+78+68 after press enter the result is 191.

I want word before + or after plus like in above example there are 3 word

More Examples:-

=2+8+9+7 answer in another cell should be 4
=45+897+635+9856+32145 answer in another cell should be 5 like wise.

Thanks & Regards,
Kashif.

MattC00 · Apr 29, 2011

Are the numbers actually in seperate cells and you just want to count the number of values but not sum them? I'm confused if not as it reads like your trying to count within a formula which wouldnt make sense?

If you are just looking to count the values in an "end column" then use

=COUNT(A1,B1,C1,D1,etc)

kashif.special2005 · Apr 29, 2011

Hi,

It is formula in a cell, like in cell A2 the formula is

=2+8+9+7 after press enter in cell A2 the result is 26, but in cell B2 I want answer should be 4 because there are 4 character before or after "+" sign.

another example in cell A3 the fomula is

=45+897+635+9856+32145 after press enter in cell A3 the result is 43578, but in cell B3 I want answer should be 5 because there are 5 character before or after "+"

Robert Mika · Apr 29, 2011

Use UDF from this site

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

and then in B1 use this formula

=LEN(GetFormula(A1))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(GetFormula(A1),"+",""),"-",""),"*",""),"/",""),"=",""))
[FONT=&quot]As you see it will substitute all arithmetic function, if you need more substitution you will need to wrap it in another SUBSTITUTE[/FONT]

texasalynn · Apr 29, 2011

you will need a udf in order to do this, add this in a vba module
Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function

then try this

Excel Workbook

E

F

2

43578

5

Sheet1

Excel 2003

Cell Formulas
Range	Formula
E2	=45+897+635+9856+32145
F2	=LEN(getformula(E2))-LEN(SUBSTITUTE(getformula(E2),"+",""))+1

MickG · Apr 29, 2011

Try this UDF:-

Code:

Function nCount(rng As Range)
With CreateObject("VBScript.RegExp")
        .Pattern = "\D"
        .Global = True
        nCount = UBound(Split(.Replace(rng.Formula, ","), ","))
End With
End Function

Mick

kashif.special2005 · Apr 29, 2011

Hi,

Thanks for reply, can I do this without vba function, because I don't want to use vba function, I wanted this only by formula or can be Array formulas.

Thanks

kashif.special2005 · May 1, 2011

Hi,

Formula is not working the error message is showing #NAME?.

Krishnakumar · May 1, 2011

Hi,

If your data is in A1, select A1, hit CTRL + F3

Define CellFormula

Refers to: =GET.CELL(6,Sheet1!$A4)

adjust the sheet name.

In B1:

=ISNUMBER(A1)+LEN(CellFormula)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CellFormula,"+",""),"/",""),"-",""),"*",""))

HTH

Robert Mika · May 1, 2011

Did you enter any of provided UDF as Modul in the VBA?
The error may suggest that Excel does not recognise the function you want to use.
Can you post your formula here?

Need excel expert

kashif.special2005

Active Member

Excel Facts

MattC00

New Member

kashif.special2005

Active Member

Robert Mika

MrExcel MVP

texasalynn

Well-known Member

MickG

MrExcel MVP

kashif.special2005

Active Member

kashif.special2005

Active Member

Krishnakumar

Well-known Member

Robert Mika

MrExcel MVP

Similar threads

Forum statistics

Share this page

Need excel expert

Active Member

Excel Facts

New Member

Active Member

MrExcel MVP

Well-known Member

MrExcel MVP

Active Member

Active Member

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock