Use Split() function to separate multiple values in a string separated by a semicolon

[robert693]

HI,

I want to create a formula that will split a field with different values separated by a semicolon, such as "Jimmy;Bobby;Joe;Patty" and have it read out as:
Jimmy
Bobby
Joe
Patty

The field can have one or many values separated by a semicolon. So, there may be one name, or ten. I tried to use the Split() function in the formula in may different ways, but all it returned was the first value. Is there a way to do what I want?

Any help is greatly appreciated!

 

[lbass]

Create a formula:

stringvar array x := split({table.field},";");
numbervar i;
numbervar j := ubound(x);
stringvar y := "";
for i := 1 to j do(
y := y + trim(x)+chr(13)
);
left(y,len-1);removes the final return

Format the formula to "can grow".

-LB

 

[robert693]

Thank you! It split the string. Your help is very appreciated!

 

[pmax9999]

For what it's worth, (and I'm certainly not suggesting that lbass' solution isn't perfectly good), the same result could actually be achieved more simply using the Replace() function, ie:

Replace({Table.Field}, ';', CHR(13))

Cheers
Pete

 

[robert693]

How can I get the results to print out to new rows instead of within the same text box?

 

[lbass]

1-What is maximum number of values that would be found in the field?

2-Do you need to add other fields to the new rows?

Since the data is coming from one row, the only way I think you can do this is by inserting additional details sections, and then adding formulas like this:

Stringvar array x := split({table.field},”;”);
If ubound(x)>=1 then
x[1]

Repeat for each detail section, changing “1” in both lines to 2, etc. Then format each detail section to “suppress blank section”.

-LB