Quote:
(Original post:) I think the term 'isn't hard' might be understated in my case ;-). I inserted "5 *" in a cell. Apart from the problem of the quotes being visible, it doesn't add when duplicated in an adjacent cell. The sum = 0 not 10. I removed the " (which is what I had tried before posting) and also get a sum of 0.
You must be doing an additional or modified step that you didn't describe?
|
No, you're quite right. That's what I meant when I said that adding the '*' makes the value a character string rather than a number—it means you can no longer do arithmetic with it. When the contents of a cell are already a character string (as when you add &" *" to the end of a function) then it doesn't matter; but when you want to take the contents of the cell and do further arithmetic with it, you either cannot have the asterisk at the end, or whatever uses that cell has to convert it back to a number before continuing. Usually it's not worth the bother.
Quote:
|
(Later:) Your answer presumes I know how to work with strings--unfortunately not. I looked up strings and I've no idea which of the many types you think I could use in conjunction with the 5 * example.
|
Well, let's back up. A numeric value is something you can do arithmetic with, like 5 or 1000 or pi. A character string is non-numeric: 5 + 1000 = 1005, but "Mark" + "Excel" is just nonsense. You can't do arithmetic with characters.
Even "5" + "1000" (note the quote marks) is nonsense; the
values 5 and 1000 can be added, but the
characters "5" and "1000" cannot. Excel is willing to evaluate ="5"+"1000" and return 1005, but it does it by checking the character strings, noticing that they can be converted meaningfully into numbers and doing so before doing the addition. Try saying ="5"+"1xxx"; when Excel sees the plus sign it'll try to convert both strings into numbers, but when "1xxx" cannot be converted it'll end with the #VALUE! error. That's what I get when I try ="5 *"+1000, too.
Now, I gather you're creating formulae that generate numeric values, and then adding a space-and-asterisk combination to the end. Excel does the same thing there only in the opposite direction; it converts the numeric result to a string, and then concatenates " *" at the end. After that, the contents of that cell are a character string, and you can't add anything to "5 *" any more than you can to "Mark" or "1xxx".
So the short answer is "sure, you can add an asterisk to the end of a non-formula value. But you can't use it for arithmetic afterward".
The longer answer is "...unless you first strip off the asterisk". For instance, if you know that A1 has "1005 *" in it, then in B1 it would be pointless to say =A1*3. But you could take away the asterisk and
then multiply:
Code:
=LEFT(A1,LEN(A1)-2)*3
That turns "1005 *" into "1005". It's still a character string, but when Excel sees that you want to multiply it, it'll convert it into a valid number and return 3015.
That works great if you already know there's a space-asterisk at the end of the value. If there might or might not be, it doesn't help (because if there isn't, it'll convert 1005 into 10 before doing the arithmetic). And for all I know you might also have space-asterisk-asterisk out there, too. Even that can be got around, but it gets to be more and more trouble and eventually you will (I predict) decide that it's too much trouble.
But we can still talk, if you want to look into the complications.