just one remarks,
what if i add cell A5 is blank, then A6 is still P2. now with A1:A6, then
the formula should be???

once again thxxxx

" wrote:

"formula" wrote:
in cell A1, i hv G6
cell A2: P3
cell A3: G1
cell A4: G2
cell A5: P2
how can i formulate G=9 or P=5???

="G=" & SUMPRODUCT( (LEFT(A1:A5)="G") * RIGHT(A1:A5,LEN(A1:A5)-1) )

Similarly for "P=5".