Jump to content
View in the app

A better way to browse. Learn more.
FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Number to text

Neville
in Calculation Engine (Define Fields)

Featured Replies

I know this one is dead easy but...

I want to convert number eg

87200

to Eighty seven thousand and two hundred dollars for printing on documents

I had the code to do it (Apple in those days, about 6 years ago)) faxed it to me but I didn't use it at the time.

I need it now and went to get the fax out of the file

(we all know what happens to thermal faxes - They go blank over years don't they!)

So dont know how to do it

I'n not sure if it was a field or a scrip step

help appreciated.

Neville

Hi,

I dredged through my filemaker files and found this solution courtesy of Bob Cusick of www.clickware.com.

If you're using FileMaker to print checks, you can have FileMaker automatically convert the dollar (number) amount of the check into text (words). To do this, follow this procedure:

Define a new field, Amt, as a number field containing a dollar amount. If your database already contains a dollar amount field that you want to use, first rename it to Amt. Then define the remaining fields in the list. You should type in the field names and definitions exactly as shown with one exception: some field definitions are shown containing a caret (^). Instead, you should type in spaces where these occur in the formulas. The caret is used for display purposes only.

These fields will convert to text any amount under one million dollars.

Field Name Field Type Formula / Entry Option

Amt Number

Tn Calc (Number) = Int (mod (Amt,100))

Hd Calc (Number) = Int (mod (Amt,1000) / 100)

TTh Calc (Number) = Int (mod (Amt,100000) / 1000)

HT Calc (Number) = Int (Amt / 100000)

TnTx Calc (Text) = "^^^^^^^^^One^^^^^^Two^^^^^^Three^^^^Four^^^^^Five^^^^^Six^^^^^^Seven^^^^Eight^^^^Nine^^^^^Ten^^^^^^Eleven^^^Twelve^^^Thirteen^Fourteen^Fifteen^^Sixteen^^SeventeenEighteen^Nineteen ^"

TenTx Calc (Text) = "^^^^^^^^^^^^^^Twenty^Thirty^Forty^^Fifty^^Sixty^^SeventyEighty^Ninety^"

AllNumberText Calc (Text) = if (HT, trim (middle (TnTx, HT * 9 + 1, 9)) & if (TTh, "^Hundred^", "^Hundred Thousand^"), "") & if (TTh, if (TTh > 19, trim (middle (TenTx, Int (TTh / 10) * 7 + 1, 7)) & "^" & trim (middle (TnTx, mod (TTh, 10) * 9 + 1, 9)), trim (middle (TnTx, TTh * 9 + 1, 9))) & "^Thousand", "") & "^" & if (Hd, trim (middle (TnTx, Hd * 9 + 1, 9)) & "^Hundred^", "") & if (Tn > 19, trim (middle (TenTx, Int (Tn / 10) * 7 + 1, 7)) & "^" & trim (middle (TnTx, mod (Tn, 10) * 9 + 1, 9)), trim (middle (TnTx, Tn * 9 + 1, 9))) & "^and^" & round ((Amt - Int (Amt)) * 100, 0) & "/100"

[REMEMBER: for clarity, a caret (^) is used to indicate when you should type a space.]

HTH.

quote:
Originally posted by Neville:

I know this one is dead easy but...

I want to convert number eg

87200

to Eighty seven thousand and two hundred dollars for printing on documents

I had the code to do it (Apple in those days, about 6 years ago)) faxed it to me but I didn't use it at the time.

I need it now and went to get the fax out of the file

(we all know what happens to thermal faxes - They go blank over years don't they!)

So dont know how to do it

I'n not sure if it was a field or a scrip step

help appreciated.

Neville

It's very simillar to Andy's post

code:


 





If(Mod(Int(cash/10^5),10),MiddleWords(upTo20,Mod(Int(cash/10^5),10),1) & " hundred ","") & 











If(Mod(Int(cash/10^4),10),





If(Mod(Int(cash/10^4),10) >= 2, MiddleWords(tenths,Mod(Int(cash/10^4),10),1) & 











If(Mod(Int(cash/10^3),10),MiddleWords(upTo20,Mod(Int(cash/10^3),10),1) ,""),  





If(Mod(Int(cash/10^3),10),MiddleWords(upTo20,Mod(Int(cash/10^3),10)+10,1),"ten")), 





If(Mod(Int(cash/10^3),10),MiddleWords(upTo20,Mod(Int(cash/10^3),10),1),""))





& " thousand " & 





If(Right(cash, 3), " and ", "") & 





If(Mod(Int(cash/10^2),10),MiddleWords(upTo20,Mod(Int(cash/10^2),10),1) & " hundred ","") &





 





If(Mod(Int(cash/10),10),





If(Mod(Int(cash/10),10) >= 2, MiddleWords(tenths,Mod(Int(cash/10),10),1) & 











If(Mod(cash,10),MiddleWords(upTo20,Mod(cash,10),1) ,""),  





If(Mod(cash,10),MiddleWords(upTo20,Mod(cash,10)+10,1),"ten")), 





If(Mod(cash,10),MiddleWords(upTo20,Mod(cash,10),1),""))





& " dolars"











 


where upTo20 is global text field with value:



one two three four five six seven eight nine ten eleven twelwe thirteen fourtheen fiftheen sixtheen seventheen eightheen nintheen 







and tenths  is global text field with value:



ten twenty thirty fourthy fifthy sixty seventy eigthy ninety







and cash is obviously your number







Check my spellings







HTH







Dj



			

			
		


		

	

	
	


	



                    
                    
					
					
					

					
					
					
				

					

					
					








	
	

	



	

		

			
				
			
			
			
    
				
				
				
				
                
				
			

			
			
		

		

		

		

		
		


			
			

				

The solution becomes *much* easier if you can express the number in more simple terms rather than get it to describe the number.







I mean "eight seven two zero zero" instead of "eighty seven thousand two hundred."



			

			
		


		

	

	
	


	



                    
                    
					
					
					

					
					
					
				

					

					
					








	
	

	



	

		

			
				
			
			
			
    
				
					
  • Author
    • 
				
				
				
				
                
				
			

			
			
		

		

		

		

		
		


			
			

				

Hi Andy Gaunt



Thanks for your help



I have it almost right but...



Somthing is slightly wrong eg everything ends with "and 0/100"



such as 170,000 converts to 



One Hundred Seventy  Thousand  and 0/100



Looking at your example the calc ends with "and 0/100" so don't see how it will do anything else.



The code is above me so it is a bit of "leap of faith" as I don't understand how it works.



Am I doing somthing wrong??



I copied and pasted the code directly so I wouldn't make a typo and changed the carrots to spaces carefully.



The code in the cale is (copy & paste from FM)



 If(HT  , Trim(Middle(TnTx  , HT  * 9 + 1, 9)) & If(TTh  , " Hundred ", " Hundred Thousand "), "") & If(TTh  , If(TTh  > 19, Trim(Middle(TenTx, Int(TTh  / 10) * 7 + 1, 7)) & " " & Trim(Middle(TnTx  , Mod(TTh  , 10) * 9 + 1, 9)), Trim(Middle(TnTx  , TTh  * 9 + 1, 9))) & " Thousand", "") & " " & If(Hd  , Trim(Middle(TnTx  , Hd  * 9 + 1, 9)) & " Hundred ", "") & If(Tn  > 19, Trim(Middle(TenTx, Int(Tn  / 10) * 7 + 1, 7)) & " " & Trim(Middle(TnTx  , Mod(Tn  , 10) * 9 + 1, 9)), Trim(Middle(TnTx  , Tn  * 9 + 1, 9))) & " and " & Round((Amt - Int(Amt)) * 100, 0) & "/100"



The code seems to assume that there will always be some number after the thousands but this is not always the case. eg Is the code smart enough such that if there is trailing zeros (ex X,000 or xx,000, or xxx,000 it will "quit" after the thousands and not put in "and" 



NEARLY THERE!



Thanks in anticipation



			

			
		


		

	

	
	


	



                    
                    
					
					
					

					
					
					
				

					

					
					








	
	

	



	

		

			
				
			
			
			
    
				
				
				
				
                
				
			

			
			
		

		

		

		

		
		


			
			

				

That's the part that should display the cents. For example 170356.45 should display as "one hundred seventy thousand three hundred fifty-six and 45/100 dollars" Since the last part should display leading zeroes, the last part of Andy's formula should be changed  from:



round ((Amt - Int (Amt)) * 100, 0) & "/100"



to:



Right("00"&round ((Amt - Int (Amt)) * 100, 0),2) & "/100"



			

			
		


		

	

	
	


	



                    
                    
					
					
					

					
					
					
				
			
			



		

		
	

	
	
	
	
		

			
				
				



	
	
		

			
				
Create an account or sign in to comment

			
	
			

				
				
			

		

	


			
		

	

	
		

			
				


    
    
        

            

                
                
                
                     https://fmforums.com/topic/3621-number-to-text/
                
                
                
                    
                
            

        

    

			
			
			


		

	





	

		
			
				Go to topic listing
			
		
	

	






 




									



    
    






								

								


	

							

							
								

							
							
							
						

					

				

				
					

						
						
					

				
				









	




	







	

		

			

				
Important Information

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

			

			

                
					

						 I accept
					

                
            

		

	




			

		

		
		
			

	
















	
	

		
		
		



	
	
		

			

				
				
			

			

				

					

						
						
					

				

				

					
					

						
						
					

				

				

					
					

						
						
					

				

				

					
					

						
						
					

				

			

		

	



		

	
	

		

			
Configure browser push notifications

			
		

		

			
				

					
					
					
					
				

				


	
	


			
			

				

					

						

							
Chrome (Android)

							
    
								
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
    4. 
							

						

						

							
Chrome (Desktop)

							
    
								
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.
    4.