What does the '--' double minus in front of the function do?
Also, to make it work I had to change the formula requested to =--mid(a1, 1, actual length of character field in a1, ie. $1.23 is =--mid(a1,1, 5). Using =--mid(a1, 2, 255) as you suggested resulted in #VALUE and does not work. Thank you.
In this example the double negative coerces text to a value, you can also +0 as in Ricks SUMPRODUCT or *1.
If you start at character 1 in your string you will return $1.23, which cannot be coerced using that method, you need to start at position 2. There then lies the issue of negative numbers so perhaps you'd need to use 3 etc. If you need to sum a range then I would suggest going with Ricks option.
Here is an example based on your data in your last post
Hi Rick, it did not. I wish I could but I don't know how to post a snapshot of part of my screen in Excel 2013 to show you.
As an example, in cell A1 and A2, I have:
=sumproduct(0+a1:a2), results in #VALUE error in A3, note that the values in A1 and A2 are left aligned because they are character values. Thanks for the quick replies.
Dave3009, it did not work for me because there is a complication and I think I know what the problem is. The text characters in the Excel field were cut n' pasted from an online source, a bank statement on a website (presumably HTML). So while it is non-numeric when pasted into an Excel chart, it's actually also a non-character (maybe it's in HTML?) which is why your formula did not work. On the other hand, it works when I enter the value `$1.23 in Excel (note the back apostrophe).