Concatenate in a Cell (&) and working with the numbers.

[GEM]

I have this function on E1,

=TEXT(B1-A1),"$ #,##0.00")&" - "&TEXT(D1/C1),"0.00%")

I cannot work with the numbers that E1 provides because they are formatted
as text.

Is there a way to format the numbers in two different ways ($,%), just like
my function, but to actually be able to work with these??

 

[Dave Peterson]

You may be able to parse your string into pieces, but I'd just use two cells to
hold the formulas and then format those cells nicely--not using the =text()
function.

If you absolutely have to use that formula that results in a string, then how
about adding a couple more cells with just the plain old formulas in them.

You could even insert a couple of rows/columns and hide them so they're out of
the way.

 

[GEM]

Ok, thank you very much!! But is there a way to use them inside the same cell
and leave them as a working number not as text??

 

[Jim Thomlinson]

One cell can only hold one number. You can make into text as you have done
and then represent the 2 numbers but now if you want the numbers back you
either have to recalculate the values or parse out the numbers (parsing out
the numbers will be a lot more work than it is worth).

So the short answer to your question is no...

 

[Dave Peterson]

You can use a complex formula to parse the values that you want. But you'll
have to use that complex formula in every cell that needs that number.

It's not worth the trouble in my opinion.