INSTR() not returning the correct number of occurrences.

[MoshiM]

I'm attempting to import comma delimited data into an array but need to replace the 187th element of each line if the total number of commas in the string is equal to 192. The problem being that I can't determine the number of commas in the string.

Sample .txt file : https://www.dropbox.com/s/12oi5njdexstt74/Sample.txt?dl=0

When I use the code below and the sample file above, N_OC returns 37 when there are a total of 192 commas in the string.

    MyData = Input(LOF(fileNumber), [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL] )


    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL] 
    
    strData = Split(MyData, vbCrLf) 
    
For Each Item In strData    
        N_OC = InStr(1, Item, ",")
    
 Next Item

However, when I further decide to delimit the string into an array using the following code, the correct number of elements are created.

Str_Array = Split(strData(i), ",")

Thanks in advance

 

[Domenic]

I haven't looked at your text file, but you can count the number of commas as follows...

N_OC = Len(Item) - Len(Replace(Item, ",", ""))

 

[MoshiM]

Thank you, that worked perfectly.

 

[Rick Rothstein]

Thank you, that worked perfectly.

Just so you know for future coding, the InStr function returns the location of the item searched for, not the count of that item. So for this line of code...

N_OC = InStr(1, Item, ",")

the 37 you reported that it assigned to N_OC meant the first comma it came to starting at the 1st character in the text (which is what the 1 in the first argument told it) occurred at character position 37 within the text string.

 

[John_w]

I haven't looked at your text file, but you can count the number of commas as follows...

N_OC = Len(Item) - Len(Replace(Item, ",", ""))

Very neat!