Split Function Help

1. Ely
Junior Member
Join Date
Dec 1969
Posts
3

Split Function Help

Hello,<BR><BR>I have a problem with the Split Function<BR><BR>I have a string pulled from a csv file using the FSO. The string looks something like:<BR><BR>395,"S","28TH","AV","ADAM","185,000"< BR><BR>I use the split function like so:<BR><BR>array = split(string, ",")<BR><BR>However, that does not return the desired result since there is a comma in the last field (185,000).<BR><BR>Anyone know any tricks to get around this?<BR><BR>Thanks,<BR>Ely

2. Bobby C Guest

RE: Split Function Help

Wrap the string in one pair of quotes and make sure you remember that arrays start at zero <BR><BR>example:<BR><BR>arraystring = "CA, NJ, ID"<BR>arraystring(0) = "CA"<BR>arraystring(1) = "NJ"<BR>arraystring(2) = "ID"

3. Senior Member
Join Date
Dec 1969
Posts
96,118

RE: Split Function Help

Split isn&#039t smart enough for this.<BR><BR>There are a few tricks you could pull, but none of them are foolproof.<BR><BR>For example, you could do:<BR><BR>array1 = Split(string,Chr(34)) &#039 split on the " mark<BR><BR>and then toss out all the elements that consist of only a comma.<BR><BR>But that won&#039t work if you have a quote embedded inside of one of the strings. Such as<BR><BR>19,"N","37th","Rd","he said ""hi there!""","987,111"<BR><BR>**************<BR><BR>S o the best you can probably do is something like this:<BR><BR>&#060;%<BR>array1 = Split( str, "," ) &#039 split on the comma<BR>Dim array2()<BR>ReDim array2(UBound(array1)) &#039 more than big enough<BR>c1 = 0<BR>c2 = -1<BR>Do While c1 &#060;= UBound(array1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&#039 get current "in" element<BR>&nbsp;&nbsp;&nbsp;&nbsp;cur = Trim(array1(c1))<BR>&nbsp;&nbsp;&nbsp;&nbsp;If Left(cur,1) = Chr(34) Then <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&#039 starts with a quote...fix up the value<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;Do While Right(cur,1) &#060;&#062; Chr(34) &#039 look for matching quote<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;c1 = c1 + 1<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&#039 tack on next "in" element...<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cur = cur & "," & array1(c1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;Loop &#039 and see if we have a trailing " mark yet!<BR>&nbsp;&nbsp;&nbsp;&nbsp;End If<BR>&nbsp;&nbsp;&nbsp;&nbsp;c2 = c2 + 1 &#039 to next spot in output array<BR>&nbsp;&nbsp;&nbsp;&nbsp;array2(c2) = cur &#039 cur now valid...store it<BR>&nbsp;&nbsp;&nbsp;&nbsp;c1 = c1 + 1 &#039 next spot in input array<BR>Loop<BR>ReDim Preserve array2(c2) &#039 chop it to the right size<BR>%&#062;<BR><BR>That code will fail if you have an embedded quote just before a comma, such as in:<BR><BR>17,"an ""example"", that will fail","xyz"<BR><BR>because it will see the "" after example as a valid end of an element. Ehhh...how fancy do you want to get?<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

He is reading the string from a .csv file. He does not have control over the contents of the string.<BR><BR>The quote marks he showed in his example are *in the file* and he *will* see them *inside* his array elements!<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118