2 of 2 people found this helpful
this will also work
Left ( textField ; Position ( textField ; "|" ; 1 ; 1 ) -1 )
2 of 2 people found this helpful
Left ( Field1; Position ( Field1; "|"; 1; 1)-1)
This worked except, it did not bring over the data that did not have the text I did not want. Is there another operation I can use to bring in the other correct data?
Thanks, but I don't quite understand this formula. I am a self taught File Maker user.
Still learning Blanche
This is why the community is so beneficial. always multiple ways to accomplish a task. Definitely helps people with a wide range of conceptual understanding. Pick which method works best, but more importantly one that you understand and are comfortable with.
It sounds from your posting and your Field2's "Filtered Results" that you only want the LEFT portion of the field's data. Any of the proposed postings here gives you that data.
For example, using bigtom's suggestion:
LEFT("AMA|P|0|RA|0", Position ( "AMA|P|0|RA|0"; "|"; 1; 1)-1) ==> AMA
Same as your stated requirement:
In your actual application, you would have the field name that has the data where I hardcoded "AMA|P|0|RA|0" in the two places above.
Problem solved, right?
mine puts field A into this format
then you select which "line" you want with the getvalue(field A; #)
the Left() formula looks for the separator and the pulls the text relative to left side
What is the problem exactly?
1 of 1 people found this helpful
My original post with the Let() function makes it more complicated looking, but is really two functions put together. You can use this one below if it makes more sense. I highlighted the only number you would have to change if you you wanted any other value from 'Field A'
Field A = AMA|P|0|RA|0
GetValue( Substitute (field A; "|"; ¶) ; 1) = AMA
GetValue( Substitute (field A; "|"; ¶) ; 2) = P
GetValue( Substitute (field A; "|"; ¶) ; 3) = 0
the calculations already proposed do exactly what you asked for.. what is it that you really want? Define "the other correct data" and what you expect from the calculation...
this formula .. Left ( textField ; Position ( textField ; "|" ; 1 ; 1 ) -1 )
uses two functions..
Left() and Position()
You can look up how they work in the help documentation.
Left() requires two arguments.. the text to work with and an integer that represents the number of characters to return from the text.
Left ( "this text" ; 3 ) will return "thi"..
In order to extract the portion of text from the start of the text to the first | char you need to find the position of the first | . That is what the position function does.
Your statement with three negatives is quite confusing, but after reading through it numerous times, I think what you are saying is that the calculation suggested by bigtom and coherentkris work only when there is a pipe character followed by a list of other characters (that you want to filter out). If there is no pipe character and other characters to filter out, then that calculation doesn't work.
In that case you need to apply that calculation only if there is a pipe character, so:
If ( Position ( textField ; "|" ; 1 ; 1 ) ; Left ( textField ; Position ( textField ; "|" ; 1 ; 1 ) -1 ) ; textField )
Alternatively, you can use @brianb's calculation which should still work regardless of whether there is a pipe character or not.
You spot on. I love this site.