Rich Text Formula Field - Comma for Currency #?

Question

Hey Community,I'm using a Rich Text Formula field called NPV(Color) to show the NPV value in red when it's negative. It's working well but now I just need to format it - I can't seem to add commas to the currency number in the formula field. Could you help?the formula is below.If([NPV]&lt;=0,"&lt;font color=\"red\"&gt;$"&amp;[NPV]&amp;"&lt;/font&gt;","&lt;font color=\"black\"&gt;$"&amp;[NPV]&amp;"&lt;/font&gt;")​------------------------------Jack Woods | Low Code 4 Lyfe------------------------------

markshnier__you · Answer

try thisvar text Price = [NPV];var text Value =&nbsp;"$" &amp; ToFormattedText([Price],"comma_dot",3) &amp; If(not Contains(ToText([Price]),"."),".00",Length(Right(ToText([Price]),"."))=1,"0");If([NPV]&lt;=0,"&lt;font color=\"red\"&gt;$" &amp; $Value &amp; "&lt;/font&gt;","&lt;font color=\"black\"&gt;$" &amp; $Value &amp;"&lt;/font&gt;")​------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

jackwoods · Answer

I understand the format and know what you're getting at. I'm not sure if I put it in correct.

It tells me I have additional characters past my formula...

Please see screen grab is that right?

------------------------------
Jack Woods | Low Code 4 Lyfe
------------------------------

markshnier__you · Answer

Please copy and paste your code, so I can see it all and edit it.
Also please post the error message when you save.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

jackwoods · Answer

ERROR MESSAGE:There are extra characters beyond the end of the formula.
"$" &amp;ToFormattedText([NPV],"comma_dot",3) &amp;If(not Contains(ToText([NPV]),"."),".00",Length(Right(ToText([NPV]),"."))=1,"0")&nbsp;&nbsp;;If([NPV]&lt;=0,"&lt;font color=\"red\"&gt;$" &amp; $Value &amp; "&lt;/font&gt;","&lt;font color=\"black\"&gt;$" &amp; $Value &amp;"&lt;/font&gt;");FORMULA:"$" &amp;ToFormattedText([NPV],"comma_dot",3) &amp;If(not Contains(ToText([NPV]),"."),".00",Length(Right(ToText([NPV]),"."))=1,"0"); If([NPV]&lt;=0,"&lt;font color=\"red\"&gt;$" &amp; $Value &amp; "&lt;/font&gt;","&lt;font color=\"black\"&gt;$" &amp; $Value &amp;"&lt;/font&gt;");------------------------------Jack Woods | Low Code 4 Lyfe------------------------------

markshnier__you · Answer

Can you use the whole formula I posted?&nbsp; You left out part of it.&nbsp; But in any event here is a better version to use.var text Value =&nbsp;"$" &amp;ToFormattedText([NPV],"comma_dot",3) &amp;If(not Contains(ToText([NPV]),"."),".00",Length(Right(ToText([NPV]),"."))=1,"0");If([NPV]&lt;=0,"&lt;font color=\"red\"&gt;" &amp; $Value &amp; "&lt;/font&gt;","&lt;font color=\"black\"&gt;" &amp; $Value &amp;"&lt;/font&gt;")​------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Rich Text Formula Field - Comma for Currency #?

Related Content

Color Code Date Field based on Formula

Snapshot Fields

Format Formula URL field

Merging 2 Fields into a Formula-Text Field for Reporting Purposes

formula help

Recent Discussions

Expanded URL from Reports

Pipeline Error --> "Quickbase reported an error: No error"

View query record if single record otherwise view query list

How do I unhide an app that I have no idea what it is named?

Gnatt plugin