Formula show #ERROR! when Substitute from a Number Field

[LastSamuraj]

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!

[BillH]

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

[LastSamuraj]

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!

[BillH]

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

Glad it worked eventually.

[BillH · Accepted solution]

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