Is there a Text() function in Numbers

Question

Level 1

126 points

Is there a Text() function in Numbers

When presenting calculation results together with text in Excel I am used to using the Text() function to format the numbers exactly as I need. For example

="The answer is "&TEXT(1234.567,"$#,##0.00") would give me The answer is $1,234.57

I cannot find an equivalent in Numbers, is there one?

I have found that when using the & concatenation operator Numbers uses the format of the data being concatenated which is ok until you want a different format. I have found a workaround using a hidden (white on white) intermediary cell but a Text() function would be so much easier.

Thank you for your help.

iMac 27", macOS 10.15

Posted on Mar 29, 2020 1:59 AM

Reply

Answer 1

Barry

Level 9

60,464 points

Mar 29, 2020 11:27 AM in response to iwaddo

No function by that name available.

My version is not current, so it is possible that one has been added. To check, select any empty cell, type = to open the Function Browser, then type text in the browser's search box.

Feature enhancement requests can be made directly to Apple using the Provide Numbers Feedback menu item i the Numbers menu.

Regards,

Barry

Reply

Answer 2

Barry

Level 9

60,464 points

Mar 30, 2020 1:51 PM in response to iwaddo

"I have found that when using the & concatenation operator Numbers uses the format of the data being concatenated which is ok until you want a different format."

Fairly easy to change the format of the data being concatenated, and you can set a Custom format for that data.

Note the actual entry, shown in the box at bottom left.

Formatting dos not interfere with the actual entered value, so arithmetic formulas san still us that value. If the cell to which the format is applied contains a formula whose result is a number, the format is appiled to that number:

Regards,

Barry

Reply

Answer 3

iwaddo Author

Level 1

126 points

Mar 30, 2020 11:32 PM in response to Barry

Barry

Thank you, that is one way of tackling the problem, another, as I said in my original post is to use a 'hidden' cell (this could be white on white or even on another table out of the way) and to then format the hidden cell as required.

Some of my formulas that concatenate data are if statements to vary the text, something like

=IF(E8=B2,"Overall we have same total we started with",IF(E8<B2,"Overall we have lost "&(B2−E8)&", "&E9,"Overall we have gained "&(E8−B2)&", "&E9))

In Excel, I would have used the Text() function for format the data, in Numbers I am using hidden cells to give me different number formats to the original data.

Thank you for your help

Reply

Answer 4

iwaddo Author

Level 1

126 points

Mar 30, 2020 7:43 AM in response to Barry

Thank you for trying to help

Reply

Is there a Text() function in Numbers

Similar questions