How do I join result of two formulae in 1 cell?

[Carty]

Hi. I have a formula that returns a value A (e.g. 2.3E-03) and a different formula that returns value B (e.g. 4.5E-03). I would like the cell to read (2.3E-3 - 4.5E-03). I know that text can be CONCATENATED but this doesn't work for numbers/formulae. Is there away to do this?

Thanks

Paul

 

[wideboydixon]

Like this?

Book1
	A	B	C
1	2.30E-03	4.50E-03	(2.3E-03 - 4.5E-03)
Sheet1

Cell Formulas
Range	Formula
C1	="("&TEXT(A1,"0.0E+00")&" - "&TEXT(B1,"0.0E+00")&")"

WBD

 

[Carty]

Hi WBD

The result in column C is exactly what I am looking for, however the result of the formulae do not appear in it's own cell. In column C I would like to be able to write something like this:

print "(",formula A, " - ", formula B,")"

Paul

Just thought differently - can I substitute the formula where you have the cell reference or does the &TEXT prevent this?

 

[wideboydixon]

Replace A1 and B1 in my formula with the formulas that produce the results.

="("&TEXT({first formula here},"0.0E+00")&" - "&TEXT({second formula here},"0.0E+00")&")"

WBD

 

[Joyner]

Hi WBD

.. I would like to be able to write something like this:

print "(",formula A, " - ", formula B,")"

Hello what version of Excel do you have? Are you looking for something like this:

=FORMULATEXT(A1)&" - "&FORMULATEXT(B1)

 

[Carty]

WBD - Thank you. That works perfectly

Joyner - Thank you. I am using Excel 2013. When I substituted the formula for A1 an B1 it returned a #VALUE error. Of course the error could be mine!

 

[Carty]

WBD - How do I remove the scientific notation formatting to display (1234 - 5678) for instance?

Paul

Edit: I just change the formatting of the cell, doh

2nd edit: Changing the cell format works if I am using only 1 formula but not if I am trying to join the formulae together

 

[wideboydixon]

You control the display of the numbers using the second part of the TEXT() function

="("&TEXT({first formula here},"cell format here")&" - "&TEXT({second formula here},"cell format here")&")"

https://support.office.com/en-us/ar...pId=xlmain11.chm60096&ui=en-US&rs=en-US&ad=US

WBD

 

[Carty]

Thanks WBD. Great link too.