Quick Excel Question

Lets say I want to add a number in Cell A1 + a number in Cell b1 together in cell C1, of course the formula is A1+b1.

Now my question is what if cell A1 contained numbers and text, for example '9 Days' and Cell B1 contained just the number '5', is there a way to add these 2 cells together with out getting an error.
Any way to leave the word 'Days' in cell A1 and just add the 2 numbers?

Or is that just impossible?

Thanks!

 

Yes, press ctrl-1 when in cell A1 to bring up the cell format menu, on the number tab scroll down to the custom format option - here you can change it so that in cell A1 is the number 9 with a suffix of "days" afterwards rather than actually typing in the word days, thus you can add the two numerical values together.

Enter 0 "days" in the type field - replacing the text between the " marks with whatever you want.

All you have to do is enter the numbers and add them up.

 

You will have to modify your value in cell C1, by changing it to something like:
=B1+LEFT(A1,FIND(" ",A1,1))

And Ravenmorpheus, I do not believe the method you stated would allow you to carry out arithmetic operations on the cell.

 

Really well I think you find it will, it certainly does sum() calculations and is far easier than your method.

But then I'm using Excel 07 so it could be a new feature but I remember it working that way in 03 as well... Try taking a look at the attached zipped excel sheet.

 

Hmm can you also get that to work left to right, I'm having a problem even after doing what you explained

 

Sorry don't follow - what do you mean left to right?

It adds up left to right and does sum() function left to right and both top to bottom.

Take a look at the attached test.zip file.