Skip to main content

Hello community,

what do I wrong? 🙂 

I have a field with #Numbers f.e. 80 or 80,1

In another field I try to format this field to a url format, that means I would like to replace the , with an -.

For this I use the Substitute formula:

SUBSTITUTE({Numbers}, ",", "-")
 
No matter how I format the Numbers field, I always get #ERROR!
 
Can someone help me with this 🤗 
Many thanks in advance!

Formatting doesn't change the contents, only the way the contents are displayed.  You are getting an error because you are using a text formula on a number.   If you try SUBSTITUTE({Numbers&""}, ",", "-") I believe it will work.  (You are casting the number field to a text value.)

 


Formatting doesn't change the contents, only the way the contents are displayed.  You are getting an error because you are using a text formula on a number.   If you try SUBSTITUTE({Numbers&""}, ",", "-") I believe it will work.  (You are casting the number field to a text value.)

 


Hi BillH, 

Thank you for your tip. It was almost right, your formula is just not quite correct. The characters & "" must be outside the {} in this case. So like here: SUBSTITUTE({Numbers} &"",",", "-")

Now it works for me too and I no longer get Error! Thanks again!


 

Sorry for the typo, I wasn't paying attention during my cutting and pasting lessons.  

Glad it worked eventually.


Formatting doesn't change the contents, only the way the contents are displayed.  You are getting an error because you are using a text formula on a number.   If you try SUBSTITUTE({Numbers&""}, ",", "-") I believe it will work.  (You are casting the number field to a text value.)

 


Sorry, suggested formula should be SUBSTITUTE({Numbers}&"", ",", "-")


Reply