Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Formula show #ERROR! when Substitute from a Number Field

Topic Labels: Formulas
Solved
Jump to Solution
2112 4
cancel
Showing results for 
Search instead for 
Did you mean: 
LastSamuraj
5 - Automation Enthusiast
5 - Automation Enthusiast

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!
1 Solution

Accepted Solutions

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

See Solution in Thread

4 Replies 4
BillH
9 - Sun
9 - Sun

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
9 - Sun
9 - Sun

 

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

Glad it worked eventually.

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