Extracting from an unusual string

[catman60]

Ok. First post here guys so go easy on me please. I have a flat file sent to me today that contains a number of fields with the same issue. As an example, I have a field named Coordinators from a table named Audit and the data within it is formatted as follows:

Mark Hollings;#14664;#Judy Thompson;#10169;#Tammy Wilson;#3608;#Tim Levy;#2785;#David Simpson;#1251

Is there any way to extract only the names while leaving in either the ";" or replacing it with a ","? My desired output would look like this:

Mark Hollings;Judy Thompson;Tammy Wilson;Tim Levy;David Simpson

This field can be poplulated with one or more names so that's a variable in all of this as well.

Thanks!

 

[CJ_London]

you can use the replace function

dim mystr as tring
mystr=replace("Mark Hollings;#14664;#Judy Thompson;#10169;#Tammy Wilson;#3608;#Tim Levy;#2785;#David Simpson;#1251","#","")

if you need to split it into an array (for example to add to a table) use the split function

dim myArray() as string
myArray=split(mystr)
msgbox "there are " & ubound(myArray) & " elements"

 

[catman60]

CJ, many thanks. Seems I'm having no difficulty in removing the #, however, I'm left with:

Mark Hollings;14664;Judy Thompson;10169;Tammy Wilson;3608;Tim Levy;2785;David Simpson;1251

Any suggestions for extracting only the text or now replacing numeric content that can vary? Thanks

 

[CJ_London]

use the split function as already mentioned and then code like this

 dim I as integer
 dim newstr as string
 for I=0 to ubound(myArray)-1
     if not isnumeric(replace(myarray(I),";","")) then newstr=newstr & myarray(I)
 next I