macro to parse and count the entities in a field

Hi

I have to write a macro to count the number of items in one field and display the count in other field.

Suppose

A1 has a4,a2,a3,a5,a6

A2 shows 5

the logic behind this is eliminating , and showing the count... or comma +1. if there is comma before or after this string, eliminate the comma at the end and the beginning and display the count.

I would appreciate any help in this as I am new to macros....


Thanks

hi thanks for this.


Please let me know if it is more than one field, then how should I proceed, that is


I have to ask the user the range and then insert the answer in the corresponding range


so suppose the user says that the data is in cells c7 to c37 and to display it into e5 to 35


I have created a userform, and with that I am collecting the data, so the value from the textbox will go into the formula,

the only problem I am facing is with more than one cell


I don't know how to select a cell/range and then insert the formula


Please let me know

thanks
Kunal

its not only c7. read carefully, its c7:c37

Compile error

Expected: end of statement



I copy pasted as you suggested

Thanks

it works great, also could you tell me why you put the extra ""

also if there are multiple comma, we have to eliminate that also

example

the cell is 1,2,3,,4,5,6

it should read 6 and not 7

also if it is

1,2,3,,,4,5,6

it will read 6 and not 8

also if it is
1,2,3,4,,5,6,

it will read 6 and not 7

also what would we do if the separator is whitspace and if we have multiple space, it would remove the extra space and then count

thankyou

if it is 1 2 3 4 it should return 4


in this case space is a separator


it is a different case


if it is 1 2 3

it must return 3


so instead of , it is space

also do you know

if I input the column field from the user




the user inputs something like g1 and g6

I am inputting in userform

input1=userform.textbox1.value
input2=userform.textbox2.value

display1=userform.textbox3.value
display2=userform.textbox4.value

in input1:input2 the data is there to read

and in diplay1:display 2 the data has to be display


that is the formula will now become


Sub count()
Range("display1:display2") = "=LEN(MID(input1,2,LEN(input1)-2))-LEN(SUBSTITUTE(MID(input1,2,LEN(input1)-2),"","",""""))+1"
End Sub


so in this it is giving me global error..

i think excel is not accepting these variables

please help

you have been very helpful which I greatly appreciate!