Search for first letter in cells

  • Thread starter Thread starter seb_soum
  • Start date Start date
S

seb_soum

I need to search for a certain letter in a commission list I'm workin
on...

Row J contains the customer reference numbers (IE. J1 - B123456, J2
B123457, J3 - B123458). All the reference numbers on the list givs th
sales person a certain commission. Easily done. Just count how man
times the person's ID appears.

But, occassionally, there'll be a different reference number(IE, J4
G01254). This gives the salesperson a higher commission....

How do I go through the entire J column and find/count the amoun o
G's
 
Hi
not sure this is what you're looking for but if the letter 'G' can only
appear once in a cell you can use the following to count the number of
'G' in your column:
=SUM(IF(ISERROR(FIND("G",J1:J1000)),0,1))
enter this as array formula (CTRL+SHIFT+ENTER)
 
you could use the command
=if(right(1;left(6;CELL))="G";"High commesion;Low commesion)

Okay.. thats not the vba answer.... but it will work.

If you need it in vba just convert it.
 
To count them:-

=COUNTIF(J:J,"G*")

To flag them:-

Select all of Column J and do format / conditional formatting / change 'cell
value' to 'formula is' and put in

=LEFT($J1)="G"

Hit the format button choose a nice pastel pattern and hit OK twice till you are
out.
 
Back
Top