Newbies lightboy Posted May 14, 2006 Newbies Share Posted May 14, 2006 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. Link to comment Share on other sites More sharing options...
comment Posted May 14, 2006 Share Posted May 14, 2006 Try something like: Trim ( GetValue ( Substitute (Color ; ";" ; ¶ ) ; 2 ) ) to extract the second value (change 2 to 1 to extract the first). Link to comment Share on other sites More sharing options...
Newbies lightboy Posted May 14, 2006 Author Newbies Share Posted May 14, 2006 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 ";". Link to comment Share on other sites More sharing options...
Raybaudi Posted May 14, 2006 Share Posted May 14, 2006 For color1 Case( PatternCount ( color ; ";" ) = 0;color; LeftWords ( color ; 1 ) ) or simpler: LeftWords ( color ; 1 ) For color2: Case( PatternCount ( color ; ";" ) = 0;""; RightWords ( color ; 1 ) ) Link to comment Share on other sites More sharing options...
comment Posted May 14, 2006 Share Posted May 14, 2006 Well, it works for me. "didn't seem to work" is not a good description of a problem. Link to comment Share on other sites More sharing options...
Raybaudi Posted May 14, 2006 Share Posted May 14, 2006 Your calc works for me, too. Link to comment Share on other sites More sharing options...
LaRetta Posted May 14, 2006 Share Posted May 14, 2006 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: Link to comment Share on other sites More sharing options...
Newbies lightboy Posted May 15, 2006 Author Newbies Share Posted May 15, 2006 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? Link to comment Share on other sites More sharing options...
comment Posted May 15, 2006 Share Posted May 15, 2006 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). Link to comment Share on other sites More sharing options...
Raybaudi Posted May 15, 2006 Share Posted May 15, 2006 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. Link to comment Share on other sites More sharing options...
comment Posted May 15, 2006 Share Posted May 15, 2006 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. Link to comment Share on other sites More sharing options...
Raybaudi Posted May 15, 2006 Share Posted May 15, 2006 Hi Michael my reply was for LaRetta's thougts! I agree that more than often we can only make ASSUMPTIONS. Link to comment Share on other sites More sharing options...
Newbies lightboy Posted May 16, 2006 Author Newbies Share Posted May 16, 2006 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6526 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now