If there’s a value of 123456 in a field and I turn on the thousands separator, it gets displayed as 123,456, which is great. However, when that field is converted to text with the ToText() function, the commas are ignored and it gets converted back to 123456.
Is there a way to maintain or recreate the thousand separator commas when a number is converted to text without the worlds ugliest collection of redundant if statements, length, right() & replace() functions?
I have it working (up to 999 million) but it’s pretty inefficient.
If(
Length(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
)
) >= 7,
Replace(
Replace(
If(
Length(
Middle(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") + 1,
2
)
) <= 1,
ToText(Round([Number Value], 2)) + "0",
ToText(Round([Number Value], 2))
),
Middle(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
Find(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
Right(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
3
)
) - 3,
3
),
"," +
Middle(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
Find(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
Right(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
3
)
) - 3,
3
)
),
Right(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
3
),
"," +
Right(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
3
)
),
If(
Length(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
)
) > 3,
Replace(
If(
Length(
Middle(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") + 1,
2
)
) <= 1,
ToText(Round([Number Value], 2)) + "0",
ToText(Round([Number Value], 2))
),
Right(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
3
),
"," +
Right(
Left(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") - 1
),
3
)
),
If(
Length(
Middle(
ToText(Round([Number Value], 2)),
Find(ToText(Round([Number Value], 2)), ".") + 1,
2
)
) <= 1,
ToText(Round([Number Value], 2)) + "0",
ToText(Round([Number Value], 2))
)
)
)