Loop / Trim left comma delimited fields

[lahddah]

I am trying to loop through a comma delmited field (peru, ama, ecu,) and perform a function for each of the words between the commas. The loop continues while the field LEN > 0. Before looping after the function, I want to remove everything left of the first comma, including the comma so that when function has been performed for each word, the field will be empty and the loop will end.

I can't seem to find a the correct TRIM function to remove everything left of the comma including the comma.

Can anyone help with this?

Thanks!

~ lahddah


~ lahddah

 

[ChrisHirst]

why not use split() and then work on the array contents

or

the_string = mid(the_string,instr(the_string,",")+1)

(not tested BTW)

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[lahddah]

well, I thought of that, but the field in question is going to be matching up w/ another comma delimited field. So, rather than work with two arrays, I just thought it would be easier to perform the function, then trim both fields, and perform the function again.

I'm creating HTML files based on names / content in these fields...so:

field1 = (filename1, filename2, filename3,)
field2 = (content1, content2, content2,)

The final result should be three HTML files named filename1, filename2 and filename3 which contain the content associated with each.

Make sense?


~ lahddah

 

[Genimuse]

The Trim command only removes spaces from the outside edges, not commas.

 

[Tarwn]

I think two arrays would be easier to work with (and more efficient) then constantly resigzing your strings, but you could do something like:

Do While Len(field1) > 0
   'do stuff here


   Shrink(field1)
   Shrink(field2)
Loop

Function Shrink(aStr)
   If InStr(aStr,",") > 0 Then
      'if there is still a comma in it, then eat the word and first comma
      aStr = Right(aStr,Len(aStr) - InStr(aStr,","))
   Else
      'No commas left, eat the rest of the string
      aStr = ""
   End If
End Function

It may be possible to trim ths down but in the end the execution between the two array method and the string shortening method is something like (n is the number of elements):
2Array: 2 splits + 1 UBound + n UBounds
Shortening: n Len's + 2*n InStr's + 2*(n-1) InStr's + 2*(n-1) Len's + 2*(n-1) Right's

Looking back at the function you cold probably reduce it to:

Function Shrink(aStr)
   Dim pos
   pos = InStr(aStr,",")
   If pos > 0 Then
      'if there is still a comma in it, then eat the word and first comma
      aStr = Right(aStr,Len(aStr) - pos)
   Else
      'No commas left, eat the rest of the string
      aStr = ""
   End If
End Function

Which means 2*(n-1) less InStr's. But your still dynamically re-sizing your strings on every loop with all the other functions.

The n*Ubound in my array function assumes your array loop would look something like:

arr_1 = Split(field1,",")
arr_2 = Split(field2,",")
For i = 0 to UBound(arr_1)
   'if the second array is out of values, exit early
   If UBound(arr_2) > i Then Exit For

   'do whatever
Next

Of course that could be reduced to:

arr_1 = Split(field1,",")
arr_2 = Split(field2,",")
top = UBound(arr_1)
If top > UBound(arr_2) Then top = UBound(arr_2)
For i = 0 to top
  '...do stuff
Next

So now your two array method is 3 * Ubound + 2 * Split. I'm fairly certain that that is not only smaller and more efficient, but also safer since the above shortening function did not include any additional logic to make sure tat both strings had the same number of elements.

-T