Excel csv file, need to protect zeros

Forums: Computers, Excel, Excel Csv File
Email this Topic • Print this Page

Excel 2007

How to put a barcode starting with zeros into excel and save as a csv (comma delimited) file.

When you enter a number into a cell that begins with zeros because you need that number to be recognised by a barcode reader on an EPoS (electronic point of sale) system, the file needs to be saved as a csv comma delimited file with zeros at the beginning of the barcode

When you save the file as csv file, it appears to save correctly and that the EPOS system will therefore recognise it.

However, when you reopen the csv file to edit, ALL the barcodes automatically are converted to numbers without the zeros in front

i.e. barcode = 0001234567
format the cell as TEXT

have tried to unlock and then protect the worksheet (doesn’t seem to make a difference)

save file as .csv (comma delimited)

http://i277.photobucket.com/albums/kk41/LzzieIzzie/whatever/book1.jpg

click yes (???????)

close excel

open excel

open .csv file

shows 1234567 in the cell

change to 0001234567

save as .csv

upon re-opening, the zeros are always removed.

What I need to happen is to save the excel file as a csv (comma delimited) and open for editing without having to re-enter 1000’s of barcodes that start with zero.

Is this possible (it must be) and how do I do this?

Please, thank you very much.

Topic Stats
Top Replies
Link to this Topic

Type: Question • Score: 2 • Views: 17,125 • Replies: 8
Topic Closed

No top replies

You can save a comma delimited file with a .txt extension. Then when you open the .txt file it will run you through an import dialogue that allows you to format each column (or any one or more columns). Select the column and format it as "text" and it will open with the leading zeros.

You can also format the file to save text fields with " " around them so that they'll reopen as text but then you have a bunch of ""s in your csv file.

1 Reply

@JPB,

here's the way I actually do it...

save the .csv file

copy it to a .txt file

open the .txt file and edit.

0 Replies

@Izzie,

1. Highlight the cell, the column, the row, or any group of cells and then Right Click.
2. Choose "Format Cells"
3. Beneath the Tab titled "Number", choose "Text".
From this point forward, Excel will not recognize anything in these formatted Cells as anything but text, so it will not manipulate your numbers. You still have the option to use Excel's number functions in all other cells not formatted this way.

1 Reply

@OCCOM BILL,

Sorry Izz... missed the part about transfering to CSV... and see now that Excel formatting will not survive the trip. Please ignore my useless advice (and everyone should vote it down.)

0 Replies

Not sure if this is usefull but I can save a column with leading zeros in CSV format by placing a text item in the cell prior to the leading zrero. A full stop, apostrophe, quote mark " or comma.
I was sure ther was a way to format cells for leading zeros but am unable to find it now and i am not sure that this would save leading zeros in CSV format anyhow.

0 Replies

Method 2: Variable-length Numbers
When you want to display leading zeros for variable-length numbers, create a custom format with the same number of zeros (0) (in quotation marks) as leading zeros that you want to display, followed by the number sign (#). For example, if you want to format a variable-length number with three leading zeros, create the following custom number format:
"000"#

To create a custom number format, follow these steps:

1. On the Format menu, click Cells.
2. Click the Number tab.
3. Click Custom.
4. In the Type box, type the custom format.

http://support.microsoft.com/kb/81518
5. Click OK.

1 Reply

@Izzie,

A very good ask, owe to this question, my similar issue about free code to recognize barcode in .net might be corrected. This is a good place to solve problems.

0 Replies

@dadpad,

Worked perfectly! Thank you!

0 Replies

Excel csv file, need to protect zeros

Related Topics

Quick Links

My Account

able2know