Help

Formula show #ERROR! when Substitute from a Number Field

Topic Labels: Formulas
Solved
Jump to Solution
629 4
cancel
Showing results for 
Search instead for 
Did you mean: 
LastSamuraj
4 - Data Explorer
4 - Data Explorer

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
BillH
8 - Airtable Astronomer
8 - Airtable Astronomer

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

See Solution in Thread

4 Replies 4
BillH
8 - Airtable Astronomer
8 - Airtable Astronomer

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
4 - Data Explorer
4 - Data Explorer

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
8 - Airtable Astronomer
8 - Airtable Astronomer

 

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

Glad it worked eventually.

BillH
8 - Airtable Astronomer
8 - Airtable Astronomer

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