Hi all

I face a problems that is i need to count how many w in column B and write into column C frequently in Excel .how to write VBA code to automatic
count and write total w as follow column c. Because i need to estimate the performance of thousand of students frequently

Column A / column B / column C
peter / good;(2w2w)slow/ 4w
mary/ good /
john/ not bad(1w1w1w1w)/ 4w
alxe /good/
tom /bad(2w4w5w3w) / 14w
simon/ good(1w2w3w)/ 6w

Please kindly note Mary and Alxe column b do not contain w , so column c don't contain w also

Thanks
Tom

You can do this with a forumula:
=(LEN(B2)-LEN(SUBSTITUTE(B2,"1w","")))*1+(LEN(B2)-LEN(SUBSTITUTE(B2,"2w","")))*2+(LEN(B2)-LEN(SUBSTITUTE(B2,"3w","")))*3+(LEN(B2)-LEN(SUBSTITUTE(B2,"4w","")))*4+(LEN(B2)-LEN(SUBSTITUTE(B2,"5w","")))*5+(LEN(B2)-LEN(SUBSTITUTE(B2,"6w","")))*6+(LEN(B2)-LEN(SUBSTITUTE(B2,"7w","")))*7+(LEN(B2)-LEN(SUBSTITUTE(B2,"8w","")))*8
where the data are in B2.

The above formula caters for up to 8w - you can add more expressions if you need.

Note that I haven't bothered adding the 'w' to the formula results. You could do that with a custom number format (0w).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

sorry paul
after put the formula in column c, peter show 8(actual is 4) john show 8(actual is 4) tom show 28(actual is14)
thanks

Hi Paul, thank you missing /2 .... now the formula workable
thank for your excellent knowledge

=(LEN(B2)-LEN(SUBSTITUTE(B2,"1w","")))/2*1+(LEN(B2)-LEN(SUBSTITUTE(B2,"2w","")))/2*2+(LEN(B2)-LEN(SUBSTITUTE(B2,"3w","")))/2*3+(LEN(B2)-LEN(SUBSTITUTE(B2,"4w","")))/2*4+(LEN(B2)-LEN(SUBSTITUTE(B2,"5w","")))/2*5+(LEN(B2)-LEN(SUBSTITUTE(B2,"6w","")))*/2*6+(LEN(B2)-LEN(SUBSTITUTE(B2,"7w","")))/2*7+(LEN(B2)-LEN(SUBSTITUTE(B2,"8w","")))/2*8

Simpler:
=((LEN(B2)-LEN(SUBSTITUTE(B2,"1w","")))*1+(LEN(B2)-LEN(SUBSTITUTE(B2,"2w","")))*2+(LEN(B2)-LEN(SUBSTITUTE(B2,"3w","")))*3+(LEN(B2)-LEN(SUBSTITUTE(B2,"4w","")))*4+(LEN(B2)-LEN(SUBSTITUTE(B2,"5w","")))*5+(LEN(B2)-LEN(SUBSTITUTE(B2,"6w","")))*6+(LEN(B2)-LEN(SUBSTITUTE(B2,"7w","")))*7+(LEN(B2)-LEN(SUBSTITUTE(B2,"8w","")))*8)/2

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

yes it is more simpler Thanks Paul