# Counting a character in MS-Excel (Mac)

Andy Law Big Nose Andy.Law at bbsrc.ac.uk
Tue Aug 29 07:27:44 EST 1995

In article <yeagerm-2808951353200001 at stubbs1.tjh.tju.edu>,
yeagerm at jeflin.tju.edu (Mark Yeager) wrote:

> In article <v01510100ac62011a3143@[143.48.11.21]>, lodhi at CSHL.ORG
>
> > Does anyone know how to count a particular character ,e.g., 'A' or
'C' from
> > an oligo sequence in an Excel cell?  I am trying to develop a sheet to
> > calculate Tm, concentration , etc. of oligos that we use in our primer
> > walking.  If someone already has anything like that for Mac, I 'll
> > appreciate for the pointers.  Thanks in advance
>
> I have exactly what you need. The function for counting characters is for
> Excel 5, but you could adapt it for Excel 4 as a macro. The sheet also
> calculates extinction coefficient, volumes of water and stock primer to
> make up appropriate concentrations, and Tm.
>
> If you have Excel 5, you can use the following Visual Basic function in
> the sheet to count the numbers of each base. You do so by selecting the
> menu item "Insert ... Macro ... Module"
> ----------
>
> ' Counts number of string character 'base' in string 'oligo'
> ' Usage example:    =numbases(B7,'t')
> '           where 'B7' is an example cell with the oligo sequence
> '           and 't' is the character to count
> ' M. Yeager, yeagerm at jeflin.tju.edu
>
> Function NumBases(oligo, base)
> oligo = LCase\$(oligo)
> base = LCase\$(base)
> sumbase = 0
> For I = 1 To Len(oligo)
> If Mid\$(oligo, I, 1) = base Then
>     sumbase = sumbase + 1
> End If
> Next I
> NumBases = sumbase
> End Function
> --------------------
>
> If you wish, I'll email you or anyone a binhexed copy of the entire sheet.
>
> --
> --  -.--
> Mark Yeager ->   yeagerm at jeflin.tju.edu
> Dept. Pathology and Cell Biology, Thomas Jefferson Univ., Phila., PA

Mark,

This is not the most efficient way of solving this problem (In my
[probably not very humble] opinion). The problem is that Excel is pretty
slow at doing these macro thingies, and if you wanted to calculate the Tm
based on four defined bases you would have to step right through the oligo
sequence four times. If you want to get fancy and allow for ambiguities as
well, you have to step through the sequence again for every alternative
base. I reckon the best solution would be to code a macro that either
returned an array with the count for each character in it, or which used a
lookup table coded into the macro sheet that calculated the Tm directly
i.e. something along the lines of:-

Function Tm(oligo)
oligo = LCase\$(oligo)
meltTemp = 0
For I = 1 to Len(oligo)
meltTemp = meltTemp + Value_From_Array(Mid\$(oligo, I, 1))
Next I
Tm = meltTemp
End Function

Where Value_From_Array pulls a pre-defined temperature increment from an
array based on the base passed in as the argument. That way, you only
traverse the oligo once.
--
Andy Law

( Andy.Law @ bbsrc.ac.uk )
( Big Nose in Edinburgh )