2 Replies Latest reply on Jul 13, 2018 2:11 PM by Deepak Rai

# HELP! Conversion of Numbers to String Issue

When formatting a number to a string after doing division, I do not always get consistent results. I have a calculation that "dynamically" formats the value of a financial field. I often have vast differences of amounts to format so I can't use a field format for all of them.

My formula checks to see if the values are greater than a million, thousand, then the rest.

IF LEN(STR(ROUND([Sales 2018],0))) > 6 THEN

"\$" + LEFT(STR(ROUND([Sales 2018] / 1000000,2)),4) + "MM"

ELSEIF LEN(STR(ROUND([Sales 2018],0))) > 3 THEN

"\$" + LEFT(STR(ROUND([Sales 2018] / 1000,2)),4) + "K"

ELSEIF LEN(STR(ROUND([Sales 2018],0))) <= 3 THEN

//Removes decimal places

"\$" + LEFT(STR(ROUND([Sales 2018] / 1,2)),3) + ""

ELSE

"TBD"

END

The formula in the image "2018 Sales (Pre Format)" provides me the expected results prior to converting it to a string.

IF LEN(STR(ROUND([Sales 2018],0))) > 6 THEN

ROUND([Sales 2018] / 1000000,2)

ELSEIF LEN(STR(ROUND([Sales 2018],0))) > 3 THEN

ROUND([Sales 2018] / 1000,2)

ELSEIF LEN(STR(ROUND([Sales 2018],0))) <= 3 THEN

//Removes decimal places

[Sales 2018]

END

The formula in the image "2018 Sales (STR)" does not provide me the expected results when converting it to a string.

STR(IF LEN(STR(ROUND([Sales 2018],0))) > 6 THEN

ROUND([Sales 2018] / 1000000,2)

ELSEIF LEN(STR(ROUND([Sales 2018],0))) > 3 THEN

ROUND([Sales 2018] / 1000,2)

ELSEIF LEN(STR(ROUND([Sales 2018],0))) <= 3 THEN

//Removes decimal places

[Sales 2018]

END)

Attached is a mockup of what I put together.

• ###### 1. Re: HELP! Conversion of Numbers to String Issue

Hi Will,

You need to Aggregarte your data in This Case to make it Work

Thanks

Deepak

If It helps, Pl mark it Helpful and CORRECT to Close Thread

1 of 1 people found this helpful
• ###### 2. Re: HELP! Conversion of Numbers to String Issue

Hi Will,

If it Worked for you, can you close the Thread by Marking my Reply as CORRECT, That will help others. The CORRECT button is under my Reply in Your main Thread, not in email.

Thanks

Deepak