Counting Commas in a String

[SQLJoe]

I have a string of alpha-numeric codes separated by commas and need to count how many commas are in the string to determine how many codes are listed (one more than the number of commas).

I know instr() will return the first instance of a character, but how can I count how many of them?

For example, given this string;

EI1184,OL9443,GG4657,DF7745

I need code that would count the commas and return the number 3 (I would then add 1 to get the number of codes listed = 4).

 

[Andrzejek]

One way:

Dim str As String
Dim a() As String

str = "EI1184,OL9443,GG4657,DF7745"

a = Split(str, ",")

MsgBox UBound(a) + 1

Another way, you can loop thru your string, character by character, and count the commas.

Yet another way - if you look up in VBA Help InStr, you will find that you can set the starting point of where to start the look. You can use it in the loop to count the commas.

Have fun.

---- Andy

 

[PHV]

Another way:
myStr="EI1184,OL9443,GG4657,DF7745"
intHowManyCode=Len(myStr)-Len(Replace(myStr,",",""))+1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SQLJoe]

Cool. Thanks!