I am trying to figure out how to divide a field that uses ";" as a divider in it. Example: In my paperwork the Color field is filled in with something like R134;L228. These two numbers reference colors for lighting. I have a field that is Color 1 and another that is Color 2. I would like the R134 to end up in Color 1 and L228 to end up in color 2. I understand the basic logic of the calculation, but I don't know how to write the calculation for Color 1 field. I feel it need to be something like {if color contains ";" use data before ";"} but in proper syntax.

Could anybody help me with this? Thanks in advance.

Try something like:

Trim ( GetValue ( Substitute (Color ; ";" ; ¶ ) ; 2 ) )

to extract the second value (change 2 to 1 to extract the first).

Thanks Comment,

I tried your calculation, but it didn't seem to work.

I had a bit of a brainstorm though and come up with something that works, sort of....

If ( Color ; LeftWords ( Color ; 1 ) ; " " ) and for color 2 I replaced left with right.

It works, but what I need added now is the part where if there is no ";" then color 2 shouldn't be calculated. Right now Color 2 fills in with the field because it doesn't know anything about a ";".

For color1

Case(

PatternCount ( color ; ";" ) = 0;color;

LeftWords ( color ; 1 )

)

or simpler:

LeftWords ( color ; 1 )

For color2:

Case(

PatternCount ( color ; ";" ) = 0;"";

RightWords ( color ; 1 )

)

Well, it works for me. "didn't seem to work" is not a good description of a problem.

Your calc works for me, too.

You mean you hadn't even tried Michael's before you posted your calc? Funny Daniele!

Okay, I admit ... I would have CREATED and tested a calc maybe (ROF) and even posted it if I thought it was more efficient ... but I certainly wouldn't have posted it until I compared the two! Cracks me up ...

:laugh2:

Sorry to post the "doesn't work with me" post. I should have followed it by NFG, because that's what all my guys tell me when I ask what is wrong with that piece of equipment.

I really should have known better. So here is what happened when I put the formula in.....it erased what I had put in there and replaced it with nothing. I imagine this has something to do with an affiliated field that is also gathering info off this one piece of info.

Daniele, I will try your suggestion on Tuesday, my time, when I am back at work. In the meantime, if anyone has any more suggestions, let me know.

Daniele, you're in Rome, if so, I am as well. heading out to Florence tomorrow. Small world, eh?

I am afraid I still don't follow. IIUC, you should have 3 fields:

Color - a text field, contains for example "R134;L228"

Color1 - a calculation field (result is Text) =

Trim ( GetValue ( Substitute (Color ; ";" ; ¶ ) ; 1 ) )

Color2 - a calculation field (result is Text) =

Trim ( GetValue ( Substitute (Color ; ";" ; ¶ ) ; 2 ) )

This splits whatever you type into Color into the the 2 calc fields, based on ";" as the boundary (there's an assumption here you won't be typing carriage returns into the Color field).

Hi LaRetta

yes, I didn't try Michael's calc before !

I was concentrating on the problem of not having a ";" between the two colors.( like when the color is only one)

So I prefer to use the PatternCount() function.

BTW: LeftWords ( color ; 1 ) is simpler than Trim ( GetValue ( Substitute (Color ; ";" ; ¶ ) ; 1 ) ) and more reliable too (what about a "carriage return" wrongly inserted ?).

And LeftWords() or RightWords() don't need to be trimmed ! So, yes, I think that my calc is better.

What about a space wrongly (or rightly) inserted?

This is always the problem when dealing with examples, not knowing what the real data will look like. I can easily imagine input like "R134+filter;L228 (check again!)". If anything, your calc makes MORE assumptions than mine - I only assumed there won't be carriage returns in the field - you assumed there won't be ANY word delimiters.

To answer the original question - 'how to divide a field that uses ";" as a divider in it' - without ANY assumptions:

Let ( [

text = ";" & Color & ";" ;

start = Position ( text ; ";" ; 1 ; n ) + 1 ;

end = Position ( text ; ";" ; 1 ; n + 1 )

] ;

Middle ( text ; start ; end - start )

)

where n is the number of the desired division.

Hi Michael

my reply was for LaRetta's thougts!

I agree that more than often we can only make ASSUMPTIONS.

Hello again.

Thanks Daniele,

Works like a charm. Exactly what I needed. Thanks for the help!!

I just want to clarify that the way I represented the example is the only way that it can or will be inserted into the field. You will have either "R45" inserted or if 2 filters are required, "R57;L495". The Letters and numbers refer to Gel Manufactures and the number the color. It's pretty much industry standard to use the ";".

Again thanks for all your help.