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!
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!
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 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!
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!
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},"!")
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!
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…
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!
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.
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 oreasonably, I think] robust routines for ‘pretty-printing’ numbers and currency into user-friendly strings.
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})))))
If the number’s lenght is 7, the middle part should start from 2 to have the right number:
MID(CONCATENATE({Final Sales},""),2,3)
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})))))
Thank you so much, this works perfectly!
If the number’s lenght is 7, the middle part should start from 2 to have the right number:
MID(CONCATENATE({Final Sales},""),2,3)
What if you have 8 and 9 figures?
Also I’m trying to create ‘Min Budget to Max Budget’ so its ‘90,000,000 to 100,000,000’
I’ve been using CONCATENATE({Min Budget}, " - ", {Max Budget},)
I can just replace your {final sales} with ‘{Min Budget}, " - ", {Max Budget}’ I’m hoping…
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.