Excel formatting cell with formula and text

evotime, Mar 9, 11:10pm
Hi all I'm sure there's a simple solution.

Cell c1 has 400 in it.
Cell b2 has 13 in it
cell c2. =($c$1/b2)-10&" msw"
The problem is when I add the &"msw" it treats it as a text cell so takes away my formatting which as a result gives me the decimals I don't want.
How do I fix this?
Thanks all

gyrogearloose, Mar 9, 11:48pm
Wrap a TEXT command around the numerical calculation

c2 = text(($c$1/b2)-10, ???000.0???) & " msw"


allan_mac, Mar 9, 11:50pm
You won't be able to do any further calculations on that result.

try using =round(($C$1/b2)-10,n)&&qu-
ot;msw"
where n = the number of decimals you want

evotime, Mar 10, 12:31am
Both ways brings an error saying too few arguments for this function

king1, Mar 10, 12:40am
you need to change the quotes around 000.0 to normal ones in gyrogearloose formula.

another option is to stick the msw into C3 as a separate string, depends on the context though

evotime, Mar 10, 12:48am
Oh also I'm using a work computer that's in Dutch format to run a different Dutch excel program. So decimals are commas
I only have one set of quotes on here I hadn't noticed they were different.
I have currently got msw in c3 as a separate string.
I am just playing around learning excel so I wanted to learn the correct way if there is one

king1, Mar 10, 12:59am
basically if you need to use the resulting number in another formula it is best not to mix numbers with text.

the 'value' function will convert text to a number but not if it is mixed text/numbers eg
http://spreadsheets.about.com/od/Text-Functions/ss/2011-05-26-excel-convert-text-to-numbers.htm

evotime, Jun 3, 8:56pm
No probs I will just leave it as it is cheers

Share this thread

Buy me a coffee :)Buy me a coffee :)