eYou would need, I suspect, to show us the range of text that it could need extracted from. For example, if it was always letters and numbers like this:
then a simple TextToNum function will return 123. Or even having a calculation field = that field and the return type as 'Number'.
If any '-' character in the string is only because it is a negative number then you can search for that with PatternCount and add a '-' to the number.
So ABC-123XYZ would be easy.
But if the text could be any of:
A-BC-123XYZ; A-B-C-123-XYZ; ABC123XY-Z then it would be more fun.
It's actually a calculated text field we'll call LOCATION which is made up of CITY, AIRPORT CODE, LATITUDE and LONGITUDE. So for example a typical text string might read "TORONTO, CYYZ, 43.6775, -79.6308333333".
To extract it I'm currently using:
table::CITY = LeftWords (LOCATION; 1)
table::CODE = MiddleWords ( LOCATION ; WordCount ( LOCATION ) - 4 ; 1 )
table::LATITUDE = MiddleWords (LOCATION ; WordCount ( LOCATION ) - 1 ; 1 )
table::LONGITUDE = MiddleWords ( LOCATION ; WordCount ( LOCATION ); 1 )
This is working except that the result I get for any negative number has no "-" symbol (i.e. in the example above the longitude returns as 79.6308333333).
Also, I'm not sure why the MiddleWords locations need to be WordCount (LOCATION) -4, -1 AND 1 respectively, but it seems to work that way. I expected it to be WordCount (LOCATION) -2, -1 and 0... but then maybe that's why the "-" is dropping off??
Any thoughts? How does the TextToNum function work... what's the syntax for that?
I suspect that the - is being treated as an additional "word" here.
Also, note that if "San Francisco" or "New York" is the city name, simply counting the words will cause problems for you anyway.
I suggest using the position function to look for the commas instead. The Trim function can also be useful when you do this to trim off any leading and trailing spaces.
getAsNumber ( text )
Is the function that will take whatever is in "text" and return just the numeric part of it as a number.
All these text functions can be looked up in FileMaker help to learn the syntax and their options.
" How does the TextToNum function work... what's the syntax for that?" - ah, yes, I remember it well, summer '42, I think it was... You'd need to consult the Help for Filemaker 3.1 or thereabouts for that. Phil is of course absolutely correct - it's been GetAsNumber since we moved on from Latin.
Ahhh.. thanks to both of you for your responses. I hadn't thought of the two-word names issue... that would be a problem.
start = Position ( string ; RightWords ( string ; 1 ) ; 1 ; 1 ) - 2 ;
Trim ( Right (string ; Length ( string ) - start ) )
Phil spotted the problem with two-word cities. And the dash is a word separator so it is excluded by xWords. In all cases where possible, it is best to use those commas to determine the safest split-point. You could turn the comma to carriage return then just grab the appropriate value, something like:
Let ( list = Substitute ( string ; "," ; ¶ ) ; Trim ( GetValue ( list ; 1 ) ) )
... would grab the first value. Change bold red part to 2 to grab the CODE etc ... it is even easier than writing a calc such as my first example. ;-)
Ha!!! Let ( list = Substitute ( string ; "," ; ¶ ) ; Trim ( GetValue ( list ; 1 ) ) ) works PERFECTLY!! Thanks LaRetta. :)