How do I count the number of brackets within a formula (cell)?

[Guest]

I am reviewing vehicle usage. An example of a formula under review is as
follows: =(5-2)+(6-2)+(8-3). This will add up the kilometers. The 2nd
column (the problem), I want to calculate is the number of trips. Each
bracket is considered one trip. In the above example that would be a total
of 3 trips and total of 12 km.

 

[JE McGimpsey]

One way, using a UDF:

Public Function NumTrips(rng As Excel.Range) As Variant
With rng
If Not .HasFormula Then
NumTrips = 0 ' or CVErr(xlErrRef)
Else
NumTrips = Len(.Formula) - _
Len(Application.Substitute(.Formula, "(", ""))
End If
End With
End Function


If you're unfamiliar with UDFs, see

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

 

[RagDyer]

John's UDF is probably safer, but this is another approach, which may or may
not be more trouble then it's worth, where you "Get" the cell contents,
whether it's a formula or data, and then parse it to count your trips.

Say the column with your kilometer formula is 2 columns to the left of the
column where you would like to use a new formula to return the number of
trips.

km formula in Column A - Trips in Column C

Select cell C1, then:

<Insert> <Name> <Define>

In the "Names In Workbook" box, enter any name, say
FORM
short for formula.

Then, click in the "Refers To" box, hit <F2> to enter the edit mode, and
enter this formula:

=GET.CELL(6,Sheet1!A1)

Then click <OK>

You now have a relative formula, which will access a cell 2 columns to the
left of *wherever* it's entered.

With your km formula in A1, enter
=form
in C1
And you'll get
=(5-2)+(6-2)+(8-3)
in C1

We can now parse *IN* C1 to count the parenthesis and return your number of
trips:

Enter this formula in C1:
=LEN(form)-LEN(SUBSTITUTE(form,"(",""))

Like I said, maybe more trouble then it's worth, but interesting none the
less.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================