Calculation to parse text between semi-colons from one field into multiple fields

[Joe_Schmo]

I'm trying to take text from one field and break it into several other fields. The source field contains several words separated by semi-colons, kind of like the format of a CSV file. Each semi-colon represents the start of a new field. The problem is that the values between the semi-colons are not a standard number of characters or words so calculations like LeftValues, MiddleWord, etc don't seem like they will work.

I need a calculation that looks at all the characters from the first one in the field to the first semi-colon of the source field. Then the second calculation would take the values between the first and second semi-colons from the source field, and so on.

So the source field would be something like:

Blue 255; John; Apple Banana Orange

And the three new fields would each have a calculation that referenced the source field but only pick out one section

Blue 255

John

Apple Banana Orange

And it needs to work regardless of the number of words/characters in each section. Could someone point me toward the right function please?

[Joe_Schmo]

Ok, hopefully there is a much easier way, but I think I have an idea of how to approach this. I could use the position function to get the number of characters from the left of the first, second and third semi-colons, find the differences to calculate the number of characters in each field, then use the Left, Right and Middle functions to get the text. Is this the only way to go about this?

[eos]

Your idea is correct, but this might be easier:

Substitute ( myField ; "; " ; ¶ ) converts each semicolon and the leading blank into a carriage return, which lets you use the MiddleValues function:

Substitute ( MiddleValues ( myField ; 1 ; 1 ) ; ¶ ; "" ), Substitute ( MiddleValues ( myField ; 2 ; 1 ) ; ¶ ; "" ) etc.

[Joe_Schmo]

I follow you on the first part, but could you explain what the second line is doing there please?

the syntax for MiddleValues is: MiddleValues ( text ; startingValue ; numberOfValues )

so it looks like the text you have "( myField ; 1 ; 1 )" in place of "text" , ¶ for the starting value and "" for the numberOfValues. I don't understand what that is doing.

[EDIT]

Nevermind, I just reviewed the help page for MiddleValues and see that it parses text between ¶s. I thought it could only take the middle # of characters specified between a numeric value for the first and last characters.

[eos]

I follow you on the first part, but could you explain what the second line is doing there please?

the syntax for MiddleValues is: MiddleValues ( text ; startingValue ; numberOfValues )

so it looks like the text you have "( myField ; 1 ; 1 )" in place of "text" , ¶ for the starting value and "" for the numberOfValues. I don't understand what that is doing.

The result of MiddleValue has trailing carriage return, which you need to get rid of. This is achieved by wrapping the MiddleValues function inside

a Substitute, since FileMaker resolves a calculation from innermost to outermost.

The calculation may be more easy to read like this:

Let (

[

myVariable1 = MiddleValues ( myField ; 1 ; 1 ) ;

myVariable2 = substitute ( myVariable1 ; ¶ ; "" )

] ;

myVariable2

)

EDIT: Courtesy to raybaudi, who reminded me of GetValue, which has no trailing CR, simply make it GetValue ( myField ; 1 ), GetValue ( myField ; 2 ) etc.

[Raybaudi]

The result of MiddleValue has trailing carriage return

So why not use GetValue ( ) ?

[eos]

So why not use GetValue ( ) ?

Why not indeed? :hmm:

[Joe_Schmo]

even better! Thank you both

the substitute method from eos and the GetValue Rayaudi suggested will work quite easily. Thanks guys!