Excel

Casterina · 26 Jul 2015 at 15:21

I've a .CSV file exported from a stock check app on my iPad but when I opened it in Excel, some of the barcodes display with full stops or plus signs and sometimes a 0 is missing at the start of a number which I assume can be fixed somewhere in the Excel settings?

As seen in the screen below, the barcode number displayed in C2 is different to the barcode I have highlighted in the red square box which is the correct barcode.

Caged · 26 Jul 2015 at 15:37

Change the cell format to text

Agent Smith · 26 Jul 2015 at 15:39

Hi,

1. Regarding the number being displayed differently then right click on the cell and format as "Number" and remove the two decimal places.

2. If you are concerned about retaining the zero in front of the first number that excels drops then right click on the cell and format as "Custom" and type in 13 zero's 0000000000000. 13 has been selected as I counted the number in the above number + 1 for the missing zero, this will force the 0 in front of the 885875520020, therefore it will read 0885875520020.

3. The third option would to to format the cell as point 1 then create another cell and use the following in the new cell, this would point to the cell with the numbers in.
=TEXT(A1,"0000000000000") ** A1 is your C2 on the screen shot.

Ta
Shaun

Casterina · 27 Jul 2015 at 14:52

Agent Smith said:
1. Regarding the number being displayed differently then right click on the cell and format as "Number" and remove the two decimal places.

Thanks, that has fixed the numbers and is now displayed correctly

Agent Smith said:
2. If you are concerned about retaining the zero in front of the first number that excels drops then right click on the cell and format as "Custom" and type in 13 zero's 0000000000000. 13 has been selected as I counted the number in the above number + 1 for the missing zero, this will force the 0 in front of the 885875520020, therefore it will read 0885875520020.

3. The third option would to to format the cell as point 1 then create another cell and use the following in the new cell, this would point to the cell with the numbers in.
=TEXT(A1,"0000000000000") ** A1 is your C2 on the screen shot.

Some barcodes starts with 0 or 00 which Excel drops but is there a way to format it in one go without having to do it cell by cell as there is quite a lot of barcodes to do