#value! problem with data after CSV import in excel

Question

Hi guys, 

I imported a csv from shopify and all numbers are ok to work with except the ones that are percentage (like conversion rate %). They are not stored as text but numbers and if I want to multiply that number with let's say 100 (=that cell * 100) the value is always #value!

Any suggestions how to get csv number that are percentage in the first place to be able to work with them?

I am using win10 and office 365.

Thank you,

Alan

Answer 1

Alan wrote:

They are not stored as text but numbers

They probably are indeed text that Excel cannot interpret as numbers.  The format of the cell does not matter.  And you cannot rely on visual inspection.  Use =ISTEXT(A1) to confirm.

The next question is:  why?

We are not likely to be able to answer that question without seeing the actual Excel file (and perhaps the CSV file).  A screenshot is useless.  (Geesh!)

If you cannot figure it out on your own, upload an example Excel file that demonstrates the problem to a file-sharing website, and post the public/share URL in a response here.  Be sure the URL can accessed when you are not logged in.  The file should be redacted (remove private information).

The following is not likely to remedy the problem, but it might be worth a try.

=--TRIM(SUBSTITUTE(A1,CHAR(160),""))

The double-negate converts the resulting text to a number, if that is possible.  Otherwise, you will get another #VALUE error.

Answer 2

Hi Alan,

Could you please provide the affected file? Do you have any updates?

Regards,

Qing

Answer 3

Hi Alan,

Thanks for posting the question on our forum.

I have created a new CSV file and test the same steps, I didn’t meet the issue.

I want to confirm if the issue only occurs when you import from shopify.

If so, could you please provide the affected file with me, I will test this on our side.

If your video contains any privacy or confidential info that is unable to share in public, you can directly share with us via PM to proceed.

I've sent you a private message to collect the information. You can access it via private message.

Besides, could you please provide the following information for further analysis?

1. The version of Office. (Click File>select Account)
2. The screenshot of the Excel sheet.
3. Confirm if all csv files meet the issue.

Regards,

Qing