Formula returns #VALUE for no obvious reason?

[DavidHillyer]

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?

 

[jasonb75]

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.

 

[JoeMo]

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?

 

[DavidHillyer]

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?

 

[jasonb75]

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.

 

[DavidHillyer]

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.

 

[DavidHillyer]

Sorry, that last post was confusing -- omit the middle sentence: 'When I review the cell status'.

 

[jasonb75]

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.

 

[DavidHillyer]

Thank you -- using the =LEN(A1) formula on A1 returns: 8 -- cell A1 is: 472,500 -- that's seven characters!

 

[jasonb75]

Have a look at post 3 in this thread, see if doing as suggested there clears the problem.

Special Character - Remove Nonbreaking Space

Hi i have downloaded some data to excel. it has got leading Nonbreaking Spaces as shown below CLEANSERS LOTIONS/MILKS/CREAMS OTHER Can anyone help me in removing this i tried find and replace method in excel to remove space and also tried trim function but it didn't work thanks

www.mrexcel.com