does the INSTR( have a way to you tell you the location of the second delimiter

does the INSTR( have a way to you tell you the location of the second or third item in a string

for example
first,last,address,city,state

I want to grab the text left of the second ,

I know i can do a split on it but I want to skip that

If you know the position of the first delimiter you can find the second, and if you know the second you can find the third, but what's wrong with using Split (and perhaps Join to put it back together)?

Try to interpret the CSV as an ADO data source (table in this case), and learn SQL. You then won't need to repetitively perform string manipulation on each row.

This works...

Code:

---

    strSource = "first,last,address,city,state"

    strFirst = Left$(strSource, InStr(1, strSource, ",") - 1)
    strLast = Mid$(strSource, InStr(1, strSource, ",") + 1, InStr(InStr(1, strSource, ",") + 1, strSource, ",") - InStr(1, strSource, ",") - 1)
    strAddress = Mid$(strSource, InStr(InStr(1, strSource, ",") + 1, strSource, ",") + 1, InStr(InStr(InStr(1, strSource, ",") + 1, strSource, ",") + 1, strSource, ",") - InStr(InStr(1, strSource, ",") + 1, strSource, ",") - 1)

---

But this is much easier...

Code:

---

    strSource = "first,last,address,city,state"

    strFirst = Split(strSource, ",")(0)
    strLast = Split(strSource, ",")(1)
    strAddress = Split(strSource, ",")(2)

---

Code:

---

strsource = Replace(strsource, Left(strsource, InStr((InStr(1, strsource, ",") + 1), strsource, ",")), vbNullString)

---

Quote:

---

Originally Posted by reinholdmesner

Code:

---

    strSource = "first,last,address,city,state"

    strFirst = Split(strSource, ",")(0)
    strLast = Split(strSource, ",")(1)
    strAddress = Split(strSource, ",")(2)

---

---

That is a good way:thumb: but do that only when you want to grab only a single part such as you want to grab the "Last" only:

Code:

---

    strLast = Split(strSource, ",")(1)

---

For more than one parts, you should use a temp var to split just once, because the Split() is quite expensive.

Code:

---

    strSource = "first,last,address,city,state"

    arrParts = Split(strSource, ",")
    strFirst = arrParts(0)
    strLast = arrParts(1)
    strAddress = arrParts(2)

---

Quote:

---

Originally Posted by Zach_VB6

Code:

---

strsource = Replace(strsource, Left(strsource, InStr((InStr(1, strsource, ",") + 1), strsource, ",")), vbNullString)

---

---

The einholdmesner's method is much shorter and simpler.

I have seen your many posts. Combined too many statements into one is not a good practice of coding unless they are simple statements because it will be hard to debug, that makes your brain and eyes works harder to understand the statement.

** If this is a student homework and it requires to use Instr() to find the positions of commas then:

Code:

---

strSource = "first,last,address,city,state"

i = Instr(strCource, ",")
If i > 0 Then
  j = Instr(i+1, strCource, ",")
  strLast = Mid$(strSource, i+1, j-i-1)
'Else
  '...
End If

---

Here's another way, with not too much crammed into one line:

vb Code:

strSource = Replace(strSource, Split(strSource, ",")(0), vbNullString)

Nice, Thank You