Substitute function with wildcards

[Dr. Zathras]

Is it possible to use the Substitute text function with wildcards?

I want to take a field containing completely unpredictable text and replace every occurrence of a number preceded by a "$" with the same number but with a comma as the thousands separator. For example, replace:

The cost of the job will be $5678456 give or take 10%.

with

The cost of the job will be $5,678,456 give or take 10%.

The words before and after the dollar amount will change. Also, there may be more than one dollar amount to substitute.

I tried using $#### as a Substitute search string but it took the #'s literally which wasn't what I wanted.

Any suggestions would be much appreciated.

Thanks.

Colin

[mr_vodka]

what version of FM are you using?

[Dr. Zathras]

FileMaker Pro 9 client on Mac OS X 10.5.

[Vaughan]

The Substitute() function is not the tool here. What you want to do is far more complex than it can achieve.

If there is only one substitution in each field, you could get by with a calculation that:

Remembers the text up to the $ character

Remembers the text after the $number to the end of the string

Calculate a new string by concatenating the text up to the $ character, then inserting the $number formatted with thousands commas, then adding the text after the number to the end of the string.

If the text has more than one $number in it then the calculation will need to be recursive, and for that it'll need to be a custom function. You'll need FMP Advanced to create the cf.

[Lee Smith]

Take a look at this thread, and the calculation posted by [color:orange]LaRetta, originally posted by [color:orange]comment

Link

Edited May 13, 2009 by Guest

[Vaughan]

That calculation will put the commas into the dollar figure, but it won't do the rest of the work.

The calc -- assuming one dollar value per field -- would look like this when it includes the calculation posted above:

Let( 



[

string =  ; 

lengthstring = Length( string ) ; 

posdollar = Position( string ; "$" ; 1 ; 1 ) ; 

posspace = posdollar + Position( Right( string ; lengthstring - posdollar ) ; " " ; 1 ; 1 ) ; 

preamble = Left( string ; posdollar - 1 ) ; 

moneynum = GetAsNumber( Middle( string ; posdollar + 1 ; posspace ) ) ; 

postamble = Right( string ; lengthstring - posspace + 1 ) ; 



money = Let ( [

// USER DEFINED:

input = moneynum ;

precision = 2 ;

currency = "$" ;

separator = "," ;

decPoint = "." ;



// DO NOT MODIFY FROM THIS POINT ON

inputRound = Round ( input ; precision ) ;

Amt = Abs (inputRound ) ;

Dollars = Int ( Amt )



] ;



// PREFIX

Case ( inputRound < 0 ; "-" )

& currency

&

// BILLIONS

Choose ( Dollars < 10 ^ 9 ; Right ( Div ( Dollars ; 10 ^ 9 ) ; 3 ) & separator )

&

// MILLIONS

Choose ( Dollars < 10 ^ 6 ; Right ( Div ( Dollars ; 10 ^ 6 ) ; 3 ) & separator )

&

// THOUSANDS

Choose ( Dollars < 10 ^ 3 ; Right ( Div ( Dollars ; 10 ^ 3 ) ; 3 ) & separator )

&

// UNITS

Right ( Dollars ; 3 )

&

// FRACTION

Choose ( precision < 1 ; decPoint

&

Right ( 10 ^ precision & Amt * ( 10 ^ precision ) ; precision ) )

)





] ; 



preamble & money & postamble 



)

[Lee Smith]

Funny, it works for me?

[Vaughan]

That calc you linked to puts thousands commas into a number field. The original poster wanted to put commas into the dollar value while it was in a text field, and retain the text before and after.

At least, that was my interpretation. :

I'm not suggesting the calc isn't good -- it is very good -- but it did not do all that the task requires.

My calculation separates out the three pieces of text and puts them back together again with the commas in the money value.

[Vaughan]

I just test the calc by itself and it strips out any text, just leaving the number portion.

It also only works for 12 digits of numbers, after that it starts to drop off the leading digits.

[comment]

I'm not suggesting the calc isn't good -- it is very good

It has become rather outdated, after discovering the NumToJText function.

In any case, in the absence of Advanced, this will need to be scripted.

[Dr. Zathras]

Vaughan, you are correct when you said I want to be able to put commas into a dollar value while it is in a text field while retaining the text before and after, and the modified script you kindly provided will certainly accomplish that. Unfortunately there will be instances where there are two or more occurrences of a dollar value (not necessarily the same value) needing commas and your script only seems to catch the first instance.

Is there a way to do what I need purely in a script or will I need a recursive custom function? I have a colleague with FileMaker Pro 9 Advanced so this is not a deal breaker, although my complete inexperience with custom functions might very well be.

Again, any help would be much appreciated.

Thanks.

Colin

[comment]

If you can have someone install a custom function for you, you could use something like:

FormatAmounts ( text )

Let ( [

mark = Position ( text ; "$" ; 1 ; 1 ) ;

word = LeftWords ( Right ( text ; Length ( text ) - mark ) ; 1 ) 

] ;

Case ( 

mark and GetAsNumber ( Left ( word ; 2 ) ) ;

Left ( text ; mark ) & NumToJText ( word ; 1 ; 0 ) & FormatAmounts ( Right ( text ; Length ( text ) - mark - Length ( word ) ) ) ;

 

text

)

)

Or you could write a script that follows a similar logic.

[Dr. Zathras]

Thank you very much. I have a colleague with FMP 9 Advanced and will ask for his help with creating the custom function and using it to run recursively on the text field.

Thank you to everybody for your help.

Colin

[Lee Smith]

Hi Colin,

We invite your Friend to become a member of the FM Forums too if they are not a member now. That way we can assist them with how to implement this if they need the help.

HTH

Lee