Hi,



Is it possible to sum values within one cell of Excel. I appreciate its probably not good practice BUT its an export from a 3rd party application and rather having to juggle the data a sum from that single cell would be ideal?

Example: A1 - 0.1, 1.5, '0.5, '1.2 B2 - Sum of A1 (excluding the ' and ,)

Cheers

Just to clarify, it sounds like you're saying that A1 contains not a number but a character string "0.1, 1.5, '0.5, '1.2". Is that right?

If so, you can get Excel to split the character string up into individual numbers and then add the numbers together. But the SUM function, if you use it at all, won't come in until the very end. First you have to explain to Excel how to break up the string into pieces. Do you want to continue?

You could use a UDF for this:
Simply add the above function to a normal code module in the workbook, then use a formula like:
=SUMCELLNUMS(A1, ",")
where A1 is the cell reference and "," is the delimiter.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Perfecto!! Thanks

Could you elaborate on this for me please? The reason I ask is although the solution provided by BobBridges works the application I am trying to use it with doesn't support xlsxm files only xls or xlsx so I was wondering if your solution supports this?

Thanks

Huh? What solution did Bob post?

The solution I posted works with xls and xlsm files, just not xlsx files...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Sorry not bob it was you. If you create an xlsx and you apply the VB as soon as you exit and go back into it you haven't got the code anymore (because its now a xlsx) and so the calculations don't work - unless Im missing something?

Again apologies for the confusion I really should read what I write

Right, it was macropod's solution. But Paul, I don't see why you told him it would work with .xlsx files. Your solution is a user-written function, which by definition (right?) means it has to be stored in an .xlsm. Now, I'm all for that; these days I sometimes store a new workbook as .xlsm even though I haven't yet written or imagined any code for it, just because experience leads me to believe I will sooner or later. But how would your macro work with an .xlsx?

And shabbaranks, if you really can't use .xlsm, why not store it as an .xls? That'll allow embedded VBA.

Bingo bango bongo - works a treat thanks. Just for my own sake - why xls and not xlsx? Thanks again

In the old versions of Excel, up to 2003, workbooks were named .xls and macros were stored directly in the workbooks. But with increasing concern about malicious software, Microsoft introduced a distinction between workbooks that can contain VBA code and those that can't. The plain-vanilla workbook is now .xlsx. If you want to write a macro for one of your workbooks, it won't be saved in an .xlsx; you have to store it with extension .xlsm. This provides a little extra warning about the content to a user opening a workbook, in addition to the warnings that pop up.

I graduated straight from 2003 to 2010, so I don't know whether this distinction started with 2007 or 2010.

Does that help? If I skipped over an important part of the explanation, feel free to keep asking.

If you two would take the time to read my replies, you just might save yourselves some confusion and me some time!
Now, compare what I wrote against your posts...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Oops. I read it, I just read it too fast and wrong; I thought you wrote "...works with xls and xlsm files, not just xlsx files". My bad.

I also read it wrong....

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

That is what I wrote...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Not unless you changed it later. I thought you wrote "...not just .xlsx files"; what appears there now is "...just not .xlsx files". I'm glad I'm not the only one whose eyes found it easy to reverse the two words.