Is there a consistent pattern on how the data is stored in the one field that contains all of the data? Giving an example of how this data is stored would be helpful in answering your question.
So an example the current field contains "Int J Cancer. 2014 Mar 18. Feb;144(1):93-101 [Epub ahead of print]"
I want to be able to have another filed name Journal "pull out" Int J Cancer only.
Then another field named Year "pull out" 2014
and another field name Citation "pull out" 144(1):93-101
The next record contains "Genet Mol Res. 2013 Jan 4;12(3):2594-600 doi: 10.4238/2013. January.4.15."
I want to be able to have the Journal field "pull out" Genet Mol Res only.
Then another field named Year "pull out" 2013
and another field name Citation "pull out" 4;12(3):2594-600
As the number of characters differ for each record, I can't just use LeftText etc
You can use Position to find the position of the period and then use this to find the title. Left(field1;Position(field1;".";1;1)-1)
PatternCount() and Position() are often used to get the information (such as the location of the ";" in your strings) to be used with other text functions such as Left(), Right() & Middle(). That's why Roy asked if there was a 'pattern' to the data.
Do you know how to use Regex to get this kind of data? While there is no "native" regex in FileMaker, a combination of functions may be used to extract "parse" data. You can push 'chunks' into variables for additional parsing as needed.
Position() has the parameter of "occurrence" , so the PatternCount() may be useful to find out how many of a particular character (or phrase) is in your string to be parsed.
The "word" functions may not be as helpful as your # of words appear to be variable. So we look for other patterns. But you can often pull substrings and then get "word(s)" from them using LeftWords(), RightWords() & MiddleWords().
Journal appears to be everything before the first "."
Year appears to be the first WORD after the first "."
See if there is a "map" of how the data was concatenated before you got it. It may give you a clue as to how to separate the data for your usage.
The "value" functions may also be helpful, but you would need to first change other "delimiters" to the return character - Char(13) before the data is separated into return-delimited substrings.
The year would be Middle (field1;Position(field1;".";1;1)+2);4)
Thanks so much it works!!
Can you just tell me what do the 1's refer to in the ...Position(field1;", "; 1;1)-1)
Highly recommend going to the built in FileMaker help for the best description of these functions. The first 1 is the starting position, the second 1 is what occurrence of the period do you want to find, and I use the minus 1 because I don't want the period in the final result. BTW, in your examples you used two different examples for the citation. One had a semi-colon and one did not. Is that correct?
What you are doing is parsing text and people above told you about several useful functions like Position, PatternCount, Left, Middle, Right, LeftWords, etc. The challenge is that when I try it, I rarely get it right on the first try and I ahve to experiment to get it right and to test different records to make sure it is working on all records. I use the Data Viewer in FileMaker Pro Advanced to write the formulas and see the test results. That way I can play with the formula and see live results until I get the formula correct and then I copy the formula from the Data Viewer into a calcluation field.
Glad it's working. Just want to point out that if any of your journal names have a period in them, you'll have a problem. One way to identify these by getting a count [ PatternCount ( <field> ; "." ) ] of the "."s in the field. Your sample has 6 periods, for example. If any records have a different count of periods, you may have to adjust the calc for them.
Thanks all. Lots of good tips which I will need to spend some time digesting.