If you need to use numbers in a text string you typically need to use either the TEXT function the DOLLAR or the FIXED function. To make it easier we can create our own function.
The problem with using the TEXT function is that it requires the use of symbols and quotation marks. See example below in row 3.
You can use the FIXED function if you don’t need the dollar sign. The problem with the FIXED function is that if you omit the second argument it rounds to two decimal places. That is also the problem with the DOLLAR function, it also defaults to two decimal places.
To make creating text numbers easier we can create our own function.
In the image below you can see the test LAMBDA function on row 2.
The test formula in cell B2 is.
=LAMBDA(val,[typ],[dec],IF(typ="","","$")&FIXED(val,IF(dec="",0,dec)))(A2)
If you are unfamiliar with the LAMBDA function check out the link below which gives you a brief introduction into using the LAMBDA function to create custom functions.
Rows 3, 4 and 5 in the above image show the custom function fnTEXTNUM in use. The second and third arguments are optional.
The default is to return no $ sign and 0 decimal places. Entering a 1 as the second argument will display the dollar sign. The last argument control the number of decimal places.
The range name dialog for the custom function is shown below. It has the description and the formula.
Please note: I reserve the right to delete comments that are offensive or off-topic.