7 Replies Latest reply on Sep 12, 2011 9:48 AM by FMNewbie

    TextToNum function?



      TextToNum function?


      I need to extract a number from a text string INCLUDING the (-) sign if it's a negative number.  I'm using MiddleWords function to extract the number but the negative parenthasis is dropping off. 

      I've read through a number of forums and there is a lot of mention of the TextToNum function which will keep the (-) but I can't find anything about the proper syntax for it.  Does this even exist in FMP11?  Is there a better way to extract this number from text?


        • 1. Re: TextToNum function?

          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.

          • 2. Re: TextToNum function?

            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? 

            • 3. Re: TextToNum function?

              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.

              • 4. Re: TextToNum function?

                " 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.

                • 5. Re: TextToNum function?

                  Ahhh.. thanks to both of you for your responses.   I hadn't thought of the two-word names issue... that would be a problem.


                  • 6. Re: TextToNum function?


                    Let (
                    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. ;-)

                    • 7. Re: TextToNum function?

                      Ha!!!  Let ( list = Substitute ( string ; "," ; ¶ ) ; Trim ( GetValue ( list ; 1 ) ) ) works PERFECTLY!!  Thanks LaRetta.  :)