Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

How to convert a number to a string?

25106 12
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a Formula field that gives me a number. How can I convert it to a String text? I have viewed the entire reference article but I don’t find it.

Thanks!

12 Replies 12

I realize you’ve already figured this out, but as a breadcrumb for future searchers, to convert a numeric value to a string, simply concatenate it with the empty string (""):

CONCATENATE({NumericValue},"")

or

{NumericValue} & ""

I didn’t remember how I solved this nor in what base I needed :grinning_face_with_sweat: Anyway thanks for the tip! :raised_hands:

daybreak
4 - Data Explorer
4 - Data Explorer

Is there a way to preserve the currency formatting when converting a number to a string? For example, I’d like to keep the dollar sign and the comma when converting the dollar amount $10,000 to text.

Existing Number/Currency Field
$10,000

Desired Result/Text Field
"You won $10,000!"

Thank you!

Say field with the amount is called “Money”, the formula may be:
CONCATENATE(“You won $”,{Money},"!")

daybreak
4 - Data Explorer
4 - Data Explorer

Hi Andre,

Thanks for your quick reply! This works - but I lose the comma in the 10,000. The result is this:

“You Won $10000!”

Any idea how to preserve the comma?

Thanks Again!

As I recall, I was never able to preserve the comma but had to manually parse and reformat the output. I just spent a half-hour looking for my base with the code to do that – and I’m stumped. If I manage to dig it up, I’ll post it…

You can use this:
CONCATENATE(“You won $”,LEFT(MoneyTxt,2),",",RIGHT(MoneyTxt,3),"!")

First I created a field “MoneyTxt”, where the amount “10000” was created.
Of course the formula should go a little deeper thinking of every instance where the amount may be 7 figures or 3, 4 and so on. But this works with 5 figures.

Alex_Morcego
4 - Data Explorer
4 - Data Explorer

If your numbers can be of various lengths you can do if statements, and the formula can get very long, I did it for up to 7 figures:

(IF(LEN(CONCATENATE({Final Sales},""))=4,(LEFT(CONCATENATE({Final Sales},""),1) & "." & RIGHT(CONCATENATE({Final Sales},""),3)),IF(LEN(CONCATENATE({Final Sales},""))=5,(LEFT(CONCATENATE({Final Sales},""),2) & "." & RIGHT(CONCATENATE({Final Sales},""),3)),IF(LEN(CONCATENATE({Final Sales},""))=6,(LEFT(CONCATENATE({Final Sales},""),3) & "." & RIGHT(CONCATENATE({Final Sales},""),3)),IF(LEN(CONCATENATE({Final Sales},""))=7,(LEFT(CONCATENATE({Final Sales},""),1) & "." & MID(CONCATENATE({Final Sales},""),4,3) & "." & RIGHT(CONCATENATE({Final Sales},""),3)),{Final Sales})))))

See my recent post in ‘Show and Tell’ for some [reasonably, I think] robust routines for ‘pretty-printing’ numbers and currency into user-friendly strings.