Jump to content
Sign in to follow this  
Dr. Zathras

Substitute function with wildcards

Recommended Posts

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

Share this post


Link to post
Share on other sites

what version of FM are you using?

Share this post


Link to post
Share on other sites

FileMaker Pro 9 client on Mac OS X 10.5.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Link

Edited by Guest

Share this post


Link to post
Share on other sites

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 



)

Share this post


Link to post
Share on other sites

Funny, it works for me?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.