How to maintain thousand separators when converting number to text?

Get Help
1

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))
    )
  )
)
2

I would suggest using regex

3

@interr0bangr I found your post trying to do the same thing. And per Chris’s suggestion, I got it working:

ReplaceRegex(ToText([Source Field]),“(?<=\d)(?=(\d{3})+(?!\d))”,“,”)

BUT it didn’t handle long floating numbers (inserting commas after the decimal too), so I refactored to only keep 2 decimal places:

ReplaceRegex(ReplaceRegex(ToText([Source Field]), “(.\d{2})\d*”, “\1”),“(?<=\d)(?=(\d{3})+(?!\d))”,“,”)

Hope that helps. *Now if you figure out how to right-align these text results in tables or cards to look like numbers, please let me know. :sweat_smile: