Why does Excel read CSV numeric values as text?

Shopify Discussion
1

Hello,

I used to be able to get my report in Excel with numeric value. Now, when I change the CSV format to excel, the numeric value (such as price) is read as a text and thus cannot be uploaded to my accounting system as it can’t be read.

Is there a solution out there?

We never had any issues with our reports up to sometimes in December and it took a long time to be resolved…but there are still some issues obviously.

2

Hi @Denis63

Thank you for your question.

I might guess that there is a format error. Is there a green triangle at the top left part of the cell? If it does, you can easily convert numbers to text by following these steps:

  • Select all the cells that you want to convert from text to numbers
  • Click on the yellow diamond shape icon that appears at the top right. From the menu that appears, select ‘Convert to Number’ option.

Screenshot_2.png
Screenshot_2.png285×265 26.6 KB

This would instantly convert all the numbers stored as text back to numbers. You would notice that the numbers get aligned to the right after the conversion (while these were aligned to the left when stored as text).

The second method you can use to convert text to numbers is by Changing Cell Format

Here are the steps:

  • Select all the cells that you want to convert from text to numbers.
  • Go to Home –> Number. In the Number Format drop-down, select General.

I hope that would helpful for you.

3

Hello AvadaCommerce

I tried both options to no avail…since Shopify changed the way to do our reports it is becoming a nightmare.

Can you suggest something different?

with thanks

Denis

4

Hi @Denis63

Sorry to hear that did not work.

Could you send me a sample of the price value in both the original CSV file and the converted Excel file? I hope that I can come up with a solution that could help you a bit after receiving them.

5

hi Avada

Thanks you for looking into it.

When I try to multiply the quantity sold by the retail price I always get an error message. I tried what you suggested above but it won’t work.

I have the similar problem with the other reports I used. So a solution will be very useful.

See attached documents: CSV and XLSX files

thank you again

Denis

6

Hi @Denis63

Thank you for getting back to me.

After reviewing both CSV and Excel files, I think the problem is Excel did not detect the error. So you could copy the data into another sheet and you can see the green triangle at the top left part of the cell which alerts you about the format error as I told you in the previous message.

You could follow these steps below to convert them into the numeric value:

  • Select all the cells that you want to convert from text to numbers
  • Click on the yellow diamond shape icon that appears at the top right. From the menu that appears, select the ‘Convert to Number’ option.

Screenshot_1.png
Screenshot_1.png1251×706 57.5 KB

I’m attaching a picture that shows a result after converting.

Screenshot_2.png
Screenshot_2.png743×594 37.6 KB

I hope that would be helpful.

7

Hello Avada

Could you give me the complete instruction from the CSV file to Excel to numeric.

I tried what you suggested and it is still not working, so there is something I don’t do obviously that is important.

With thanks

Denis

8

Hello Avada

I tried on a different computer and it worked.

Thank you so much!

Denis

9

Hi @Denis63

Thank you for letting me know.

I’m happy to hear that it worked for you.