Coercing data type to text

[WilliamPorter]

I need to concatenate a bunch of values from different fields to create a string result like this:

1.5 hours @ $125

The formula I’m trying to use is

HowMany & " " & What & " @ $" & Rate~

I’m able to get everything except the “125” part of that result. The “1.5” part is pulled from a data-entry number field called “HowMany”. It works okay. The “125” comes from a formula field called Rate~ that uses a formula like this:

VALUE( someNumeral )

The Rate~ field displays the correct value and moreover is used successfully in a simple math calculation formula. I thought that the VALUE() function converted a text numeral into a number. But when I add the reference to that field (the one that contains “125”), the string concatenation fails.

It doesn’t matter whether I use CONCATENATE() or ampersands.

Any suggestions? At the moment the only thing I can think of is that, for some reason, an entered number value in a number field is somehow flexible enough that it can be used in a concatenation formula, while a calculated result that has been coerced to data type number by the VALUE() function is NOT flexible enough to be included in a text string.

If VALUE() converts a text numeral to a number, Is there a function that does the same thing in reverse?

William

[Jeremy_Oglesby · Accepted solution]

Hi @WilliamPorterTech,

This isn’t a problem of coercion failing, you just need to put curly braces around that field name “Rate~” because it contains a special character.

So your formula needs to look like this:

HowMany & " " & What & " @ $" & {Rate~}

[WilliamPorter]

Aha! Fantastic. I’ve noticed that before and forgot it here. THANK YOU, Jeremy!

William