Weird...simple product formula not working...

[mimih23]

So I copied and pasted some data from an email into excel. When I attempt to do a simple product formula......it all results in 0. What could be happening? I've attempted a funky trim macro that I learned on here and have formatted the text as general and as number to see if that could help. I've even tried to just paste value....nothing. You will see how simple this should be...see below:


*38.3676 *0.019548 0 <-formula is =PRODUCT(A2,B2)
*38.3676 *0.130318 0
*39.4576 *0.019008 0
*39.4576 *0.126718 0
*39.5307 *0.018973 0
*39.5307 *0.126484 0

What gives!?? Any help would be greatly appreciated!!

 

[Cindy Ellis]

Does your data have asterisks in it? Or are those just showing here to separate the data?

 

[JoeMo]

try:

=PRODUCT(REPLACE(A1,1,1,""),REPLACE(B1,1,1,""))

This will shed the asterisks.

 

[drsarao]

With the given data (minus the asterix), it works for me:

Excel Workbook
	A	B	C
1
2	38.3676	0.019548	0.750009845
3	38.3676	0.130318	4.999988897
4	39.4576	0.019008	0.750010061
5	*39.4576	0.126718	0.126718
6	39.5307	*0.018973	39.5307
7	*39.5307	*0.126484	0
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C2	=PRODUCT(A2,B2)

A result of zero is produced IF AND ONLY IF BOTH A1 and B1 are text.
And text entries are automatically left aligned.
Check your data. Trim, get rid of spaces, asterisks etc.

 

[mimih23]

How weird is that. No the original data does not have astericks in it and I did not notice that it displayed that way. It gets even weirder......I will check out some of the solutions given and let you know how it pans out. Thanks!!!

 

[Cindy Ellis]

I just did a quick test based on a hunch. Non-breaking spaces before or after your numbers will cause the exact behavior you've described, including displaying as asterisks here on the forum, and not being fixable with "trim".
I tried a couple of things, but the simplest was to highlight then copy the single invisible character in any one of the cells to the clipboard, open up the find/replace dialog, paste it into the "find" text box with the replace text box blank, and choose "replace all".
If this is data that you will receive via email on an ongoing basis and you want to automate it, the following bit of code in a macro will do the trick. (You would put this in a personal macro workbook or a workbook that you open whenever you need to use the macro):

Sub mycleaner()
    Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

If the offending character is something other than a non-breaking space (character 160), and the code doesn't work, you can discover the correct character code by putting the following formula into a cell (pointing to one of your cells with data rather than A1):

=code(A1)

Hope that helps,
Cindy

 

[mimih23]

Hey there Cindy!
We are right on the same page. Yes, I just completed another trim macro and it still yielded the same result. I double clicked in one of the cells just to see if there was anything there that I couldn't see and low and behold!...non breaking space before the numbers...as soon as I deleted it from my test cells it worked. I just did the find & replace feature as you mentioned and wala!!! It worked! Thanks guys!!!!