text in a formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
=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
 
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.
 
="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
 
Back
Top