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

mimih23

Board Regular
Joined
Oct 14, 2010
Messages
89
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!!
:confused:
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Cindy Ellis

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
Does your data have asterisks in it? Or are those just showing here to separate the data?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,515
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
try:
Code:
=PRODUCT(REPLACE(A1,1,1,""),REPLACE(B1,1,1,""))
This will shed the asterisks.
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,146
Office Version
  1. 2007
Platform
  1. Windows
With the given data (minus the asterix), it works for me:
Excel Workbook
ABC
1
238.36760.0195480.750009845
338.36760.1303184.999988897
439.45760.0190080.750010061
5*39.45760.1267180.126718
639.5307*0.01897339.5307
7*39.5307*0.1264840
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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

Board Regular
Joined
Oct 14, 2010
Messages
89

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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):
Code:
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:
=code(A1)
Hope that helps,
Cindy
 

mimih23

Board Regular
Joined
Oct 14, 2010
Messages
89
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!!!!
 

Forum statistics

Threads
1,141,019
Messages
5,703,776
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top