Formula returns #VALUE for no obvious reason?

DavidHillyer

New Member
Joined
Aug 19, 2012
Messages
22
I have copied some cells from a Google Drive spread sheet into MS Excel. In Excel I have formatted the cells as numbers with comma separators. When I do a simple formula it returns a result #VALUE -- I can't see why this happens. If I enter the numbers into two fresh cells, and create the formula, it works? Any ideas?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
In Excel I have formatted the cells as numbers with comma separators.
If you change the cell format to one without comma separators, do the commas remain? If so then the cells are actually text and the numbers are not valid.
 
Upvote 0
I have copied some cells from a Google Drive spread sheet into MS Excel. In Excel I have formatted the cells as numbers with comma separators. When I do a simple formula it returns a result #VALUE -- I can't see why this happens. If I enter the numbers into two fresh cells, and create the formula, it works? Any ideas?
Maybe the imported data isn't numbers despite your formatting. Test this by entering in an empty cell: =ISNUMBER(A1). Change the cell address to be one of your imported data cells. What does the test formula return?
 
Upvote 0
Hi Joe -- that's a helpful suggestion.
When I apply that formula to one of the cells, it returns FALSE.
The other thing I've done is to open up Format Cell for one of the problem cells. Though it opens as a Number with comma separators, when I remove the comma separator instruction, they remain. I think you are correct, this copy and paste from a Google Drive sheet has copied the numbers out as text. Is there any way I can force to text to convert to number?
 
Upvote 0
The other thing I've done is to open up Format Cell for one of the problem cells. Though it opens as a Number with comma separators, when I remove the comma separator instruction, they remain. I think you are correct, this copy and paste from a Google Drive sheet has copied the numbers out as text.
That is why I suggested it as a test, it's one of many easy ways to check if numbers are proper or text.

You could try using find and replace (ctrl h) to replace the commas with nothing. Make sure that you select the range with the values first, otherwise it will fail if it picks up any formulas that have commas in them elsewhere in the sheet.
 
Upvote 0
Hi Jason -- I've tried that, using (ctr h) enabled me to strip out the commas, but a simple subtraction calculation on two cells still returns a #VALUE response. When I review the cell status.Very clearly, copying out from Google Drive sheets can taken with it some stubborn formatting! When I review the comma-removed cells in Format Cells, it says they are Numbers, not Text.
 
Upvote 0
When you review the cell it shows the format of the cell, not the contents. You can still have text in cells that are formatted as numbers.

I've just done a quick test by entering some comma separated numbers into google sheets then copying the cells and pasting to excel. They are retaining a valid numeric format, which I thought should be the case anyway but thought best to check.

At the moment, is seems most likely that the original numbers in google have some corrupt formatting, possibly 0 width characters. Try using an =LEN(A1) formula to get a character count for one of the problem cells to see if it exceeds the number of visible characters.
 
Upvote 0
Have a look at post 3 in this thread, see if doing as suggested there clears the problem.
 
Upvote 0

Forum statistics

Threads
1,223,919
Messages
6,175,368
Members
452,638
Latest member
Oluwabukunmi

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
Back
Top