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

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.

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



In article ,
paper cutter <paper wrote:

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.

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!
==============================================


"paper cutter" <paper wrote in message
...
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.