text in a formula?

C

CD13

Hi, first post here...looking to show a conversion ratio in my formula
results ona spreadsheet. I want the results formatted to show Ex: 1 in 90,
where the 90 is calculated by a formula and "1 in" is text. Please assist :)
Thanks!
 
D

Dave F

=concatenate("1 in ",[your formula]).

The concatenate function joins two strings together, in this case, a text
string and a claculation.

Example: =CONCATENATE("1 in ",(A1/B1)) would return [1 in (A1/B1)] where
A1/B1 is your calculation.

Make sure that the "1 in ", part of your function is exactly as I put it
above, otherwise you'll either get an error, or else, will have spacing
issues between the "1 in" part and the calculation.

Dave
 
D

Dave Peterson

Maybe you could modify your existing formula to look like:

="1 in " & yourexistingformula
or
="1 in " & text(yourexistingformula,"#,##0.00")
if you need formatting.

You could also use Format|cells|Number tab|custom category:
"1 in "General
or
"1 in "#,##0.00
(if you always have non-negative numbers)

The first method (modifying the formula) will result in a text string in the
cell. The second method will keep the value a number--but will make the display
look the way you want.
 
B

Bernard Liengme

="1 in "&A1
where A1 is the cell with the computed 90
But if the value is not exactly 90 (maybe 90.05 but displayed as 90) you
will need
="1 in "&TEXT(A1,"#")
best wishes
 
Top